SQL: Enforcing foreign keys for inherited tables

Tweet 0 Shares 0 Tweets 0 Comments

This is a tricky one. Inheritance, where one database table INHERITS the properties of another while adding some extra fields of its own, is a very useful concept in SQL, but at the same time it breaks indexes and foreign key constraints.

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint.

Setting up the TABLEs

In this example we have a parent table users which is used to store unprivileged users, and a child table regulars that INHERITS the columns from users while adding extra columns:

# CREATE TABLE users (
    userid SERIAL,
    name character varying NOT NULL,
    "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    PRIMARY KEY (userid)
  );

# CREATE TABLE regulars (
    username character varying UNIQUE,
    password character varying,
    admin_user boolean DEFAULT false NOT NULL,
    PRIMARY KEY (userid)
  )
  INHERITS (users);

In this model we can INSERT new users either into the users table with just a name, or into the regulars table where they can also have login details and other information.

Why would we do it this way and not just have a single table? There are couple of good reasons:

Storage: If the child table or tables (there can be multiple inherited tables) have a lot of extra fields, and you have a large number of unprivileged users, then large parts of the storage space will be filled up with NULL values;
Simplicity We can identify various subsets of users with SQL without needing a WHERE condition:
- SELECT name FROM users; -- all users
- SELECT name FROM regulars; -- privileged users
- SELECT name FROM ONLY users; -- unprivileged users

What we can't do, however, is use userid as a foreign key constraint as shown here:

# CREATE TABLE signins (
    userid integer NOT NULL REFERENCES users,
    time_in timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    time_out timestamp with time zone
  );

The problem with foreign keys

As explained earlier, "foreign key constraints only apply to single tables" so in the TABLE defined above you will only be able to insert references to unprivileged users (ONLY users) and not any from inherited tables.

You can see this demonstrated here:

# INSERT INTO users (name) VALUES ('Tom');
INSERT 0 1

# INSERT INTO users (name) VALUES ('Mary');
INSERT 0 1

# INSERT INTO regulars (name) VALUES ('Harry');
INSERT 0 1

# SELECT * FROM users;
 userid | name  |           timestamp           
--------+-------+-------------------------------
      1 | Tom   | 2023-05-08 08:06:10.217267+00
      2 | Mary  | 2023-05-08 08:06:10.217267+00
      3 | Harry | 2023-05-08 08:06:10.217267+00
(3 rows)

# INSERT INTO signins (userid) VALUES (1);
INSERT 0 1

# INSERT INTO signins (userid) VALUES (2);
INSERT 0 1

# INSERT INTO signins (userid) VALUES (3);
ERROR:  insert or update on table "signins" violates foreign key constraint "signins_userid_fkey"
DETAIL:  Key (userid)=(3) is not present in table "users".

While the SELECT works as expected - returning rows from both the parent and child tables - trying to INSERT a reference to a record in a child table results in an error.

At this point you can either throw in the towel and revert to a single table with extra fields, or replace the foreign key constraint with a TRIGGER.

Workaround using a TRIGGER function

First we have to remove the (useless) foreign key constraint from our signins TABLE:

# CREATE TABLE signins (
    userid integer NOT NULL REFERENCES users,
    time_in timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    time_out timestamp with time zone
  );

Implementing a TRIGGER then is a two step process starting with defining a TRIGGER FUNCTION:

# CREATE FUNCTION userid_exists() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  DECLARE
    userid_exists boolean;
  BEGIN
    SELECT (userid IS NOT NULL) FROM users WHERE userid = NEW.userid INTO userid_exists;
    IF (userid_exists) THEN
      RETURN NEW;
    ELSE
      RAISE EXCEPTION 'Nonexistent ID --> %', NEW.userid;
      RETURN NULL;
    END IF;
  END;
  $$;

and then applying it as a TRIGGER on your table:

# CREATE TRIGGER userid_signin_check
    BEFORE INSERT OR UPDATE ON signins
      FOR EACH ROW EXECUTE FUNCTION userid_exists();

If the userid exists our trigger function returns NEW allowing the INSERT to continue. Otherwise NULL is returned which effectively blocks the INSERT operation, and an (optional) EXCEPTION is raised.

We can now re-run our test case:

# INSERT INTO signins (userid) VALUES (1);
INSERT 0 1

# INSERT INTO signins (userid) VALUES (2);
INSERT 0 1

# INSERT INTO signins (userid) VALUES (3);
INSERT 0 1

# INSERT INTO signins (userid) VALUES (4);
ERROR:  Nonexistent ID --> 4
CONTEXT:  PL/pgSQL function userid_exists() line 9 at RAISE

Note that without the foreign key constraint, separate triggers may be required to implement ON DELETE CASCADE and similar functionality for tables referencing users.

References

Stack Overflow: Foreign keys + table inheritance in PostgreSQL?

< SQL

Post your comment or question