Thread: Foreign Key - Best Schema

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