Shared Foreign Keys From Two Tables

[Thomas T. Thai]

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(gro upie_mod_id, groupie_id, mod_id) values (1,
1, 1);
insert into groupie_mod(gro upie_mod_id, groupie_id, mod_id) values (2,
1, 2);
insert into groupie_mod(gro upie_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_pr ef_id, mod_id, pref_key, pref_value) values
(1, 1, 'key1', 'value1');
insert into mod_pref(mod_pr ef_id, mod_id, pref_key, pref_value) values
(2, 1, 'key2', 'value2');
insert into mod_pref(mod_pr ef_id, mod_id, pref_key, pref_value) values
(3, 2, 'key1', 'value1');

create table groupie_mod_pre f (
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_pre f(groupie_id, mod_id, pref_key, pref_value)
values (1, 1, 'key1', 'value1');

-- should fail because 'key3' doesn't exist!
insert into groupie_mod_pre f(groupie_id, mod_id, pref_key, pref_value)
values (1, 1, 'key3', 'value3');

The above setup works. In table groupie_mod_pre f, I needed mod_id to be
a shared common foreign key in two other tables. Consequently in used
two table constraints in groupie_mod_pre f. 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_pre f in place of
groupie_id and mod_id and still constrain mod_id to mod_pref table?

Regards,
Thomas


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruno Wolff III]

Re: Shared Foreign Keys From Two Tables

On Fri, Jul 30, 2004 at 12:01:44 -0500,
"Thomas T. Thai" <tom@minnesota. com> wrote:[color=blue]
>
> The above setup works. In table groupie_mod_pre f, I needed mod_id to be
> a shared common foreign key in two other tables. Consequently in used
> two table constraints in groupie_mod_pre f. 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_pre f in place of
> groupie_id and mod_id and still constrain mod_id to mod_pref table?[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly