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

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.