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