473,246 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Shared Foreign Keys From Two Tables

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
1 2051
On Fri, Jul 30, 2004 at 12:01:44 -0500,
"Thomas T. Thai" <to*@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.

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

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Bodza Bodza | last post by:
I'm having an argument with an incumbent self-taught programmer that it is OK to use null foreign keys in database design. My take is the whole point of a foreign key is that it's not supposed...
31
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific...
1
by: Vinodh Kumar P | last post by:
I understand the number of foreign keys allowed is restricted by the DBMS I use. In a general relational schema design perspective how many foreign keys a table shall have? If I have large number...
6
by: Brendan Jurd | last post by:
Hi all, I read on the manual page for Inheritance that: "A limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
9
by: Jax | last post by:
I'm making my first ever database for my program. I understand the concept of one to many relationships but fail to see the advantage of using a secondary key over a primary one. I have a lot of...
2
by: Ian Davies | last post by:
I have created a database with about 17 tables. I have been creating foreign keys some of which have worked but when creating others I get the message below ************************* 1005...
1
by: rbarber | last post by:
I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables...
1
by: apax999 | last post by:
Kinda new to SQL, using SQL Server 2005. I have some foreign keys in a couple of tables. I need to drop these tables, but can't since I'll get the error: Msg 3726, Level 16, State 1, Line...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.