maintaining referential integrity

From: Brandon Metcalf <brandon(at)geronimoalloys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: maintaining referential integrity
Date: 2009-06-05 16:27:44
Message-ID: Pine.LNX.4.58L.0906051115140.17533@cedar.geronimoalloys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jean Hoderd 2009-06-05 17:00:31 NOT NULL with CREATE TYPE
Previous Message Brandon Metcalf 2009-06-05 16:13:12 Re: limit table to one row