maintaining referential integrity

Поиск
Список
Период
Сортировка
От Brandon Metcalf
Тема maintaining referential integrity
Дата
Msg-id Pine.LNX.4.58L.0906051115140.17533@cedar.geronimoalloys.com
обсуждение исходный текст
Ответы Re: maintaining referential integrity  (Andy Colson <andy@squeakycode.net>)
Re: maintaining referential integrity  (David <wizzardx@gmail.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Brandon Metcalf
Дата:
Сообщение: Re: limit table to one row
Следующее
От: Jean Hoderd
Дата:
Сообщение: NOT NULL with CREATE TYPE