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
>