Re: maintaining referential integrity

On Fri, Jun 5, 2009 at 6:27 PM, Brandon
Metcalf<brandon(at)geronimoalloys(dot)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.