Thread: two table foreign keys
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
Just checked the docs and found the ALTER TABLE ... ADD FOREIGN KEY so my example can become much simpler. (Thanks to the documentation authors!) So disregard my previous SQL example. The other issues are still open for me though... - Miles Miles Elam wrote: > 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 <snip /> > 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 >
On Thu, 7 Nov 2002, Miles Elam wrote:
> 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
> 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")
> );
Use something like:
ALTER TABLE lusers add foreign key ("defaultGroup") references
lgroups deferrable;
rather than the create constraint triggers
> 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?
Should be fine.
> 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)?
If you do a full backup and restore, it'll be at the end (although 7.2 and
earlier don't check IIRC on restore anyway). You can't really do a
piece by piece restore with the circular references easily, however.