Brandon Metcalf wrote:
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.
Take workorder_id out of generic, and add a new table:
create table generic_link (
generic_id integer,
workorder_id integer
);
create index generic_link_pk on generic_link(generic_id);
Then to find all the workorders for a generic_id do:
select workorder.* from workorder inner join generic_link on
(workorder.workorder_id = generic_link.workorder_id)
where generic_link.generic_id = 5
This is a Many-to-Many relationship.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
