Search Postgresql Archives

maintaining referential integrity

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux