Re: two table foreign keys

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.