CODEWITHSUNDEEP
databasedatabase-designforeign-keys
sachin
sachin

Reputation: 1386

Reference data from another table

I am creating a database table and want to make sure that data in one of the column is always bounded by data in a column of another table. for example:

Table_1 has Column_1
Column_1 can have values:
v1
v2
v3
v4
v2
v3

Now I am trying to create Table_2 with Column_3
and want to make sure that values in this column is always a subset of Table_1:Column_1

Is there a constraint I can apply to achieve this?

Upvotes: 1

Views: 81

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Is Table_1.Column_1 unique?

  • If yes, just make it a PRIMARY KEY or UNIQUE, then reference it from FOREIGN KEY on the Table_2.Column_3.
  • If no (as apparently implied by your example), make a FK from Table_2 to Table_1's primary key. The Table_2.Column_3 won't even exist, instead you'll get the values of Table_1.Column_1 by JOIN-ing the two tables. You can put that JOIN in a VIEW to make it appear (to the client applications) as if the Table_2.Column_3 actually exists.

Upvotes: 0

Andrew Alcock
Andrew Alcock

Reputation: 19661

In Oracle and PostgreSQL, use a check constraint

eg, in Oracle:

ALTER TABLE Table_3
ADD CONSTRAINT my_name
CHECK
    (column_3 in 
        (SELECT Column_1 FROM Table_1))

This also works with PostgreSQL

In SQL Server and DB2, I believe, you have to create a function that does the actual test, but otherwise it's the same. The function would have a single argument (column_3's value) and return EXISTS (SELECT 1 FROM Table_1 WHERE Column_1 = argument).

Unfortunately, in MySQL you will need to use on insert and update triggers

Upvotes: 1

Related Questions