On Fri, Jun 5, 2009 at 6:27 PM, Brandon
Metcalf<brandon@geronimoalloys.com> 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.
>
You probably want a third table, generic_workorder, that links tables
generic and work_order together in a many-to-many relationship.
Something like:
CREATE TABLE generic_workorder (
generic_workorder_id SERIAL PRIMARY KEY,
generic_id NOT NULL REFERENCES generic(generic_id),
workorder_id NOT NULL REFERENCES generic(generic_id)
);
(I'm not sure if the above syntax is 100% correct), and then possibly
drop the generic.workorder_id column.
The new table, generic_workorder, will link generic and workorder
records together in a many-to-many relationship, and also enforce
referential integrity.