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.