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
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? |