CODEWITHSUNDEEP
sqlsql-serverdatabaseperformance
karthik sonu
karthik sonu

Reputation: 23

One foreign key references multiple primary keys

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

Upvotes: 1

Views: 4201

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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:

Upvotes: 2

Ari Singh
Ari Singh

Reputation: 1296

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

Upvotes: -1

Related Questions