Foreign Key - Best Schema
От | Thomas T. Thai |
---|---|
Тема | Foreign Key - Best Schema |
Дата | |
Msg-id | Pine.NEB.4.44.0303111040560.5097-100000@ns01.minnesota.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: