Shared Foreign Keys From Two Tables
От | Thomas T. Thai |
---|---|
Тема | Shared Foreign Keys From Two Tables |
Дата | |
Msg-id | 410A7EF8.8050208@minnesota.com обсуждение исходный текст |
Ответы |
Re: Shared Foreign Keys From Two Tables
|
Список | pgsql-general |
I'm looking for a better way to make use of foreign keys. Here is a sample setup: -- TESTING Foreign Keys create table mod ( mod_id int not null primary key, name varchar(32) not null default '' ); insert into mod(mod_id, name) values (1, 'module one'); insert into mod(mod_id, name) values (2, 'module two'); create table groupie ( groupie_id int not null primary key, name varchar(32) not null default '' ); insert into groupie(groupie_id, name) values (1, 'groupie one'); insert into groupie(groupie_id, name) values (2, 'groupie two'); create table groupie_mod ( groupie_mod_id int not null primary key, groupie_id int not null references groupie on delete restrict, mod_id int not null references mod on delete restrict, UNIQUE (groupie_id, mod_id) ); insert into groupie_mod(groupie_mod_id, groupie_id, mod_id) values (1, 1, 1); insert into groupie_mod(groupie_mod_id, groupie_id, mod_id) values (2, 1, 2); insert into groupie_mod(groupie_mod_id, groupie_id, mod_id) values (3, 2, 2); create table mod_pref ( mod_pref_id int not null primary key, mod_id int not null REFERENCES mod(mod_id), pref_key varchar(32) NOT NULL, pref_value varchar(255) NOT NULL DEFAULT '', UNIQUE (mod_id, pref_key) ); insert into mod_pref(mod_pref_id, mod_id, pref_key, pref_value) values (1, 1, 'key1', 'value1'); insert into mod_pref(mod_pref_id, mod_id, pref_key, pref_value) values (2, 1, 'key2', 'value2'); insert into mod_pref(mod_pref_id, mod_id, pref_key, pref_value) values (3, 2, 'key1', 'value1'); create table groupie_mod_pref ( groupie_id int not null, mod_id int not null, pref_key varchar(32) NOT NULL, pref_value varchar(255) NOT NULL DEFAULT '', FOREIGN KEY (groupie_id, mod_id) REFERENCES groupie_mod (groupie_id, mod_id), FOREIGN KEY (mod_id, pref_key) REFERENCES mod_pref (mod_id, pref_key) ); insert into groupie_mod_pref(groupie_id, mod_id, pref_key, pref_value) values (1, 1, 'key1', 'value1'); -- should fail because 'key3' doesn't exist! insert into groupie_mod_pref(groupie_id, mod_id, pref_key, pref_value) values (1, 1, 'key3', 'value3'); The above setup works. In table groupie_mod_pref, I needed mod_id to be a shared common foreign key in two other tables. Consequently in used two table constraints in groupie_mod_pref. However, I thought that was a waste of storage space having to repeat groupie_id and mod_id from groupie_mod. Is there anyway to keep the functionality like above, while using groupie_mod_id from groupie_mod in groupie_mod_pref in place of groupie_id and mod_id and still constrain mod_id to mod_pref table? Regards, Thomas
В списке pgsql-general по дате отправления: