Thread: maintaining referential integrity

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.

--
Brandon

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.