PostgreSQL - Table Inheritance

This tutorial is still in progress.

SQL 1999 (also called SQL 3) was the fourth revision of the SQL database query language which first introduced table inheritance, but only allows for only single inheritance. PostgreSQL supports table inheritance, but I have not had the opportunity to use it much because MySQL still does not support it (at the time of writing this post) which is really unfortunate, because it is a really useful feature, and this blog post hopes to clearly explain why. https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa For this tutorial, I am using plural names for tables. I am on the fence on whether table names should be plural or not. E.g. the "user" or "users" table. I will just go with whatever will cause less friction with the people I work with at the time.

Steps

For this scenario, I am setting up a database of users who own assets. These assets are either files held on the server that other users can download, or they are links to other other places on the internet where the content can be read or downloaded.

If you haven't already, I recommend you install PostgreSQL to follow along with this tutorial.

Connect to your database:

sudo -u postgres psql

Create and connect to a fresh new database to test with:

create database test;
\connect test;

Lets create a table of users. These users will "own" assets, and be the only person that will have permission to edit/delete the assets once in the system.

CREATE TABLE "users" (
    uuid uuid NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (uuid)
);

Now create the table containing the "core" information about our assets. This is the information that both our files and our links share. In our case, each asset as an identifier, a name, and an owner.

CREATE TABLE "assets" (
    uuid uuid NOT NULL,
    name varchar(255) NOT NULL,
    owner uuid references "user"("uuid") ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (uuid)
);

Now lets create our files table. As stated earlier, files are assets, but they are also special because they have a local filepath, which is where the file is stored on the server for serving up:

CREATE TABLE "files" (
    filepath varchar(255)
) INHERITS (assets);

Now lets creat

CREATE TABLE "link" (
    url varchar(255)
) INHERITS (assets);

Caveats  

Foreign keys  

Foreign keys don't work as expected. E.g. if you Have tables Parent, Child, and Foo, with Child inheriting from Parent, and Foo having a foreign key on Parent.id, then adding a row to Child, and trying to insert a row into Foo, referencing Child's ID will fail, even though that ID is in the Parent table. The foreign keys only work on the direct table it references.

To demonstrate this with code, try the following:

CREATE TABLE "assets" (
    uuid uuid NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (uuid)
);

CREATE TABLE "files" (
    filepath varchar(1000)
) INHERITS (assets);


CREATE TABLE "weblinks" (
    url varchar(2048)
) INHERITS (assets);


CREATE TABLE "asset_tags" (
    uuid uuid,
    asset_id uuid references assets(uuid),
    tag varchar(255)
);


INSERT INTO weblinks (uuid, name, url)
VALUES 
    ('a157a538-63b6-4a7c-baf7-b52a7041fc02', 'Google', 'https://www.google.com')
;

INSERT INTO asset_tags (uuid, asset_id, tag)
VALUES 
    ('0bec257b-b37c-40d3-be37-a185ba14ce3f', 'a157a538-63b6-4a7c-baf7-b52a7041fc02', 'google'),
    ('8bb6653a-dde4-45d6-9454-3cc9e6012de5', 'a157a538-63b6-4a7c-baf7-b52a7041fc02', 'weblink'),
    ('c5afd36e-435f-4f33-afbe-ad89c9115b98', 'a157a538-63b6-4a7c-baf7-b52a7041fc02', 'search')
;

You will get the following error when you try to run the final insert:

ERROR:  insert or update on table "asset_tags" violates foreign key constraint "asset_tags_asset_id_fkey"
DETAIL:  Key (asset_id)=(a157a538-63b6-4a7c-baf7-b52a7041fc02) is not present in table "assets".

Unique Indexes  

This is the same as above, just applying to the unique constraint. E.g. even if you have a unique index on a field in a parent table, you could add rows to two different child tables that have that same field value.

Constraints are not inherited  

Promote and Demote

• https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa

References

• Wikipedia - SQL:1999
• PostgreSQL - Docs - Chapter 3 - 3.6 Inheritance
• Stack Overflow - Postgres table inheritance: move from parent to child and vice versa
• Stack Overflow - Foreign keys + table inheritance in PostgreSQL?