Re: maintaining referential integrity

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