Re: Referential integrity

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Hi again. Still new to databases, and I was wondering if I could get
> advice on how to set this up right. Essentially, I have a table, lets
> call it main, in which each row, describes another table in the
> database. I want to set it up so that, dropping the table will also
> drop the associated row - or the other way around (which I would
> prefer). How would I go about doing that?

Let me give you an example from my own project. Visualize several Sectors,
which are connected by Warps. So we have a table of Sectors, which might
look like this:

CREATE TABLE Sectors (
num integer PRIMARY KEY,
name char(50)
);

And we have a table of warps, which might look like this:

CREATE TABLE Warps (
origin integer NOT NULL REFERENCES Sectors (num)
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
destination integer NOT NULL REFERENCES Sectors (num)
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
distance integer NOT NULL DEFAULT 0
CHECK (distance > 0),
CHECK (origin <> destination),
UNIQUE(origin, destination)
);

The "ON DELETE CASCADE" says that if the referenced sector is deleted,
then the warp should be deleted too.
The "ON UPDATE CASCADE" says that if the referenced sector's number
changes, then the warp should change too.
The "DEFERRABLE" says that these two constraints can be deferred until the
end of the transaction; Thus they don't need to be checked if there is a
rollback.
The "CHECK" says that you can't have a warp to itself.
The "UNIQUE" says that you can't have two identical warps.

Hope this is useful as an example.

Ian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5mh9Gfn9ub9ZE1xoRAuFBAJ9KEoXGGCncTEH8rhBL7B3un68e2gCfaCid
TOFUs62z52bAJhIEq6yrdww=
=PMsQ
-----END PGP SIGNATURE-----