D4rt D4rt - 7 months ago 9
SQL Question

Constraining between two recursive many-to-many relationships

I have the following tables for creating any number of items with different types.

CREATE TABLE item_types (
id SERIAL,
PRIMARY KEY (id)
-- Other columns omitted
);

CREATE TABLE items (
id SERIAL,
itemtype integer NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (itemtype) REFERENCES item_types (id)
-- Other columns omitted
);


The
items
table has a recursive many-to-many relationship called
item_relationship
.

CREATE TABLE item_relationships (
itemid1 integer,
itemid2 integer,
PRIMARY KEY (itemid1, itemid2),
FOREIGN KEY (itemid1) REFERENCES items (id),
FOREIGN KEY (itemid2) REFERENCES items (id)
);


The
item_types
table has a recursive many-to-many relationship called
item_relationship_types
.

CREATE TABLE item_relationship_types (
type1 integer,
type2 integer,
PRIMARY KEY (type1, type2),
FOREIGN KEY (type1) REFERENCES item_types (id),
FOREIGN KEY (type2) REFERENCES item_types (id)
);


Now, what I want to do is to somehow have a constraint that you can't accidentally create an
item_relationship
that is invalid, i.e where the
item_types
of the items are not found in any
item_relationship_type
. I have two questions.


  1. Does such a constraint make sense? I think inserting a wrong relationship is a mistake that could easily happen in business logic, so constraining in the DB feels important.

  2. What's the sensible way to actually implement the constraint?


Answer

One of possible approach could be extending item_relationship_types table with a surrogate primary key:

CREATE TABLE item_relationship_types (
    id integer SERIAL,
    type1 integer,
    type2 integer,
    PRIMARY KEY (id),
    UNIQUE (type1, type2),
    FOREIGN KEY (type1) REFERENCES item_types (id),
    FOREIGN KEY (type2) REFERENCES item_types (id)  
);

and then add a foreign key pointing to that surrogate key into item_relationships table:

CREATE TABLE item_relationships (
    itemid1 integer,
    itemid2 integer,
    type_rel_id integer not null,
    PRIMARY KEY (itemid1, itemid2),
    FOREIGN KEY (itemid1) REFERENCES items (id),
    FOREIGN KEY (itemid2) REFERENCES items (id),
    FOREIGN KEY (type_rel_id) REFERENCES item_relationship_types (id)  
);

You need also create a trigger that prevents from entering type_rel_id value in item_relationships table that points to the entry with types not related to these two items in item_relationship_types table.