Foreign key to table A or table B

Question

Consider a situation where I define an object, a group of objects, then a table that links them together:

CREATE TABLE obj (
  id INTEGER PRIMARY KEY,
  name text
) ;

CREATE TABLE group (
  id INTEGER PRIMARY KEY ;
  grpname TEXT
) ;

CREATE TABLE relation (
  objid INTEGER,
  grpid INTEGER,
  PRIMARY KEY (objid, grpid)
) ;

I am looking for cascade delete when applicable so I add the foreign key

ALTER TABLE relation
ADD FOREIGN KEY (objid)
REFERENCES obj(id)
ON DELETE CASCADE ;

ALTER TABLE relation
ADD FOREIGN KEY (grpid)
REFERENCES group(id)
ON DELETE CASCADE ;

So far is all OK. Now suppose I want to add support for group of groups. I am thinking to change the relation table like this:

CREATE TABLE relation_ver1 (
  parent INTEGER,
  child INTEGER,
  PRIMARY KEY (parent, child)
) ;
ALTER TABLE relation_ver1
ADD FOREIGN KEY (parent)
REFERENCES group(id)
ON DELETE CASCADE ;

Here I get to the question: I would like to apply cascade delete to child too, but I do not know here if child refers to a group or object.

Can I add a foreign key to table obj or group?

The only solution I have found do fare is add child_obj and child_grp fields, add the relative foreign keys and then, when inserting e.g an object use a 'special' (sort of null) group, and do the reverse when inserting subgroup.

Answer 1

Consider the relation:

relation_ver1(parent, child_obj, child_group)

I claim that this relation has the following disadvantages:

• You have to deal with the NULL special case.
• Approx. 1/3 of values are NULL. NULL values are bad.

Fortunately, there is an easy way to fix this. Since there is a multi-value dependency in your data, you can decompose your table into 2 smaller tables that are 4NF compliant. For example:

relation_ver_obj(parent, child_obj) and relation_ver_grp(parent, child_group).

Answer 2

The primary reason why we have foreign keys is not so as to be able to do things like cascaded deletes. The primary reason for the existence of foreign keys is referential integrity.

This means that grpid is declared as REFERENCES group(id) in order to ensure that grpid will never be allowed to take any value which is not found in group(id). So, it is an issue of validity. A cascaded DELETE also boils down to validity: if a key is deleted, then any and all foreign keys referring to that key would be left invalid, so clearly, something must be done about them. Cascaded deletion is one possible solution. Setting the foreign key to NULL, thus voiding the relationship, is another possible solution.

Your notion of having a child id refer to either a group or an object violates any notion of referential integrity. Relational Database theory has no use and no provision for polymorphism. A key must refer to one and only one kind of entity. If not, then you start running into problems like the one you have just discovered, but even worse, you cannot have any referential integrity guarantees in your database. That's not a nice situation to be in.

The way to handle the need of relationships to different kinds of entities is with the use of a set of foreign keys, one for each possible related entity, out of which only one may be non-NULL. So, here is how it would look like:

CREATE TABLE tree_relation (
    parent_id INTEGER,
    child_object_id INTEGER,
    child_group_id INTEGER,
    PRIMARY KEY (parent_id, child_object_id, child_group_id) );
ALTER TABLE tree_relation
    ADD FOREIGN KEY (parent_id) REFERENCES group(id) ON DELETE CASCADE;
ALTER TABLE tree_relation 
    ADD FOREIGN KEY (child_object_id) REFERENCES object(id) ON DELETE CASCADE;
ALTER TABLE tree_relation
    ADD FOREIGN KEY (child_group_id) REFERENCES group(id) ON DELETE CASCADE;

All you need to do is ensure that only one of child_object_id, child_group_id is non-NULL.