Shared Foreign Keys From Two Tables

Thomas T. Thai <tom@minnesota.com>

From: "Thomas T. Thai" <tom@minnesota.com>
To: pgsql-general@postgresql.org
Date: 2004-07-30T17:01:44Z
Lists: 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