Can one attribute have two foreign keys?

Question

Just doing some Relational Database work. Quick question, can one attribute have two foreign keys?

For example, is this legal:

PERSONAL_RECORDS.Date_of_birth has a foreign key in CASUAL.Date_of_birth as well as a foreign key in MANAGER.Date_of_birth

Basically, can one attribute, have a foreign key from two other attributes?

Thank you in advance! :)

score 2 · Answer 1 · answered Sep 05 '12 at 08:13

A single column can reference more than one table.

create table t1 (
  t1_id integer primary key
);

create table t2 (
  t2_id integer primary key
);

create table t3 (
  t3_id integer primary key,
  foreign key (t3_id) references t1 (t1_id),
  foreign key (t3_id) references t2 (t2_id)
);

score 0 · Answer 2 · edited May 23 '17 at 10:24

Tricky question actually, i would read: http://office.microsoft.com/en-us/access-help/design-the-tables-for-a-new-database-RZ101772996.aspx?section=9 for a bit more information on the subject. From what i recall from the schoolbench, it's not possible. But there might be a way to do it?

Multiple Foreign keys to a single table and single key pointing to more than one table also goes into a little detail regarding this.

Good luck though :)

Can one attribute have two foreign keys?

2 Answers2