two table foreign keys

From: Miles Elam <nospamelam(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: two table foreign keys
Date: 2002-11-07 21:23:46
Message-ID: aqelj7$412$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a database schema that has two tables with mutual dependency.

Basically:

local_users
id
default_group
(...bunch of other columns...)

local_groups
id
ownerid
(..other columns...)

local_users.default_group references local_groups.id
local_groups.ownerid references local_users.id

The SQL was a bit...disconcerting. I have trimmed the example for brevity.

---------------------------------------

CREATE TABLE "lusers" (
"id" serial NOT NULL,
"defaultGroup" integer NOT NULL,
Constraint "luser_pkey" Primary Key ("id")
);

CREATE TABLE "lgroups" (
"id" serial NOT NULL,
"ownerID" integer NOT NULL REFERENCES "lusers" DEFERRABLE ON UPDATE
CASCADE,
Constraint "lgroups_pkey" Primary Key ("id")
);

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "lusers"
FROM "lgroups" DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'lusers', 'lgroups',
'UNSPECIFIED', 'defaultGroup', 'id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "lgroups" FROM
"lusers" DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_del" ('<unnamed>', 'lusers', 'lgroups', 'UNSPECIFIED',
'defaultGroup', 'id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "lgroups" FROM
"lusers" DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd" ('<unnamed>', 'lusers', 'lgroups', 'UNSPECIFIED',
'defaultGroup', 'id');

-------------------------------------

There are other tables as well, but for now, I'll stick with this one.
My intent is to be somewhat like UNIX permissions and current configs
where users have a default, private group. I don't want to give up
foreign key constraints though as every user is expected to have a
default group and every group must have an owner (someone who controls
private group creation/modification/deletion). I would have preferred
simple references syntax on the lusers table, but since the lgroups
table did not yet exist, this is what I came up with.

Two issues: Am I doing something for which PostgreSQL was not designed
(mutual table references)? Will it affect pg_dump/pg_restore?

Is it acceptable that INSERTs be done by simply setting constraints to
deferred and performing the INSERTS/stored procedure in a transaction?
Would I be better served by making a ¨nobody¨ group and user to be used
as a temporary?

When I am backing up and restoring the database, are constraints checked
after every COPY statement into a database (which would fail) or after
all data has been imported (which would succeed)?

- Miles

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2002-11-07 21:23:48 Re: request new feature: auto recompile of function when oid not found
Previous Message Tom Lane 2002-11-07 21:23:13 Re: Vacuum full?