Thread

1. Shared Foreign Keys From Two Tables

   Thomas T. Thai <tom@minnesota.com> — 2004-07-30T17:01:44Z

   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
   
   
   

2. Re: Shared Foreign Keys From Two Tables

   Bruno Wolff III <bruno@wolff.to> — 2004-07-30T18:08:12Z

   On Fri, Jul 30, 2004 at 12:01:44 -0500,
     "Thomas T. Thai" <tom@minnesota.com> wrote:
   > 
   > 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?
   
   Not easily. (It should be possible to do with triggers.) What you could do
   is use a compound primary key in groupie_mod and mod_pref get rid of the
   (presumably) surrogate primary keys.