PostgreSQL - Table Inheritance
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.
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);
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".
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
- 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?
First published: 5th November 2020