What would be the best way to maintain referential integrity in the
following situation? Let's say I have the following table
CREATE TABLE workorder (
workorder_id INTEGER NOT NULL,
part_id INTEGER DEFAULT NULL,
generic BOOLEAN DEFAULT FALSE,
PRIMARY KEY (workorder_id)
);
and another
CREATE TABLE generic (
generic_id INTEGER NOT NULL,
workorder_id INTEGER,
PRIMARY KEY (generic_id),
FOREIGN KEY (workorder_id)
REFERENCES workorder
ON DELETE RESTRICT
ON UPDATE CASCADE
);
This is straight forward.
What if a generic_id can reference more than one workorder_id? If I
knew the upper limit on the number a generic_id could reference and
that number was small, I suppose I could define workorder_id1,
workorder_id2, etc and defined foreign keys for each. However, I
don't know this.
Another idea I have is to allow generic.workorder_id be a comma
separated list of integers and have a stored procedure verify each
one, but this gets a little messy trying to duplicate the "ON DELETE"
functionality that a foreign key provides.
Thanks.
--
Brandon
