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);
Promote and Demote
Constratints are not inherited
- Wikipedia - SQL:1999
- PostgreSQL - Docs - Chapter 3 - 3.6 Inheritance
First published: 5th November 2020