Foreign Key - Best Schema

From: "Thomas T(dot) Thai" <tom(at)minnesota(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Foreign Key - Best Schema
Date: 2003-03-11 17:08:44
Message-ID: Pine.NEB.4.44.0303111040560.5097-100000@ns01.minnesota.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I'm trying to make the best use of foreign key in the following schema:

CREATE SEQUENCE auth_user_seq;
CREATE TABLE auth_user (
user_id integer NOT NULL DEFAULT nextval('auth_user_seq')
PRIMARY KEY,
username varchar(32)
);

CREATE SEQUENCE auth_group_seq;
CREATE TABLE auth_group (
group_id integer NOT NULL DEFAULT nextval('auth_group_seq')
PRIMARY KEY,
groupname varchar(32)
);

CREATE SEQUENCE auth_perm_seq;
CREATE TABLE auth_perm (
perm_id integer NOT NULL DEFAULT nextval('auth_perm_seq')
PRIMARY KEY,
perm_name varchar(32)
);

CREATE TABLE auth_group_perm (
group_id integer NOT NULL REFERENCES auth_group (group_id)
ON DELETE RESTRICT,
perm_id integer NOT NULL REFERENCES auth_perm (perm_id)
ON DELETE RESTRICT,
PRIMARY KEY (group_id, perm_id)
);

CREATE TABLE auth_group_user (
group_id integer NOT NULL REFERENCES auth_group (group_id)
ON DELETE RESTRICT,
user_id integer NOT NULL REFERENCES auth_user (user_id)
ON DELETE RESTRICT,
PRIMARY KEY (group_id, user_id)
);

I would like auth_group_user_perm table to only contain users from certain
groups (auth_group_user) with certain perm avail to that group
(auth_group_perm) only. Is there a more efficient way to construct table
auth_group_user_perm below? Note that group_id is being used twice in both
foreign key.

CREATE TABLE auth_group_user_perm (
group_id integer NOT NULL,
user_id integer NOT NULL,
perm_id integer NOT NULL,
FOREIGN KEY (group_id, user_id) REFERENCES auth_group_user,
FOREIGN KEY (group_id, perm_id) REFERENCES auth_group_perm
);

--
Thomas

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2003-03-11 17:12:03 Re: General Performance questions
Previous Message Rich Shepard 2003-03-11 16:27:29 Re: Installation question