1

I have two tables with single primary key columns in each of those two tables and another table (table 3) which has a foreign key that refers both of those above primary key's columns.

Now I want to insert records into table 3 if it present in either of those two primary key tables.

Note: I don't want to create a new table that is combination of primary key tables and add reference to that newly created table

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
karthik sonu
  • 23
  • 1
  • 4
  • what error you are facing? – Zaid Mirza Feb 15 '18 at 04:46
  • @ZaidMirza - /*create table table1(id int primary key) * / /* create table table2(id int primary key) */ /*create table table3(id int foreign key references table1(id)) */ /*alter table table3 add constraint fk_id foreign key(id) references table2(id)*/......In This table 3 is refereing to both table1 and table2 .now i want to enter records in table3 if it has the same record in either table1 or table2. Is there any way to do it sql server – karthik sonu Feb 22 '18 at 08:46
  • @ZaidMirza- in the above example mentioned it is allowing me to enter data into table3 if it is present in both the tables(table1 and table2) – karthik sonu Feb 22 '18 at 08:47

2 Answers2

2

As Erwin Brandstetter stated here

Rules for FK constraints

To answer the question in the title and at the end of your text:

"I would still like to know how to have one foreign key referencing two primary keys."

That's impossible.

  • A FOREIGN KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

  • Or you can have multiple FOREIGN KEY constraints on the same column(s) referencing one PRIMARY KEY of a (different) table each. (Rarely useful.)

However, a single PK or FK can span multiple columns.
And a FK can reference any explicitly defined unique (set of) column(s) in the target, not just the PK. The manual:

A foreign key must reference columns that either are a primary key or form a unique constraint.

A multicolumn PK or UNIQUE constraint can only be referenced by a multicolumn FK constraint with matching column types.

Basic advice:

Community
  • 1
  • 1
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
-1
insert into table3 (col1, col2 ...)
(select col1, col2 ... from table1
union
select col1, col2 ... from table2);

You can optionally put where clauses or split the SQL into 2, instead of a union.

This is standard ANSI SQL and should work on any DBMS

Ari Singh
  • 1,228
  • 7
  • 12