From: | Brandon Metcalf <brandon(at)geronimoalloys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | maintaining referential integrity |
Date: | 2009-06-05 16:27:44 |
Message-ID: | Pine.LNX.4.58L.0906051115140.17533@cedar.geronimoalloys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean Hoderd | 2009-06-05 17:00:31 | NOT NULL with CREATE TYPE |
Previous Message | Brandon Metcalf | 2009-06-05 16:13:12 | Re: limit table to one row |