Create a Foreign Key from an attribute that is not Primary Key?

Question

Is it possible to create a foreign key - FK in one table which will refer to a normal atribute (not Primary Key - PK) in another table?

MarcM · Accepted Answer · 2017-05-03T09:35:08.403

If your are working in a RDBMS:

Yes, you can. But ONLY if values of the FK destination field are unique.

The column pointed from the FK MUST have unique values to keep database referential integrity safe.

A way to assure so is defining a Unique Constraint on it.

Edited to clarify:

Table A has a FK column pointing to table B, column named B_anycolumn.

There's no actual need to B_anycolumn from being PK from table B, but it must have a Unique Constraint to ensure that all values in it are different.

Even though this is possible, normal relationships between tables are to PK columns, since are always unique by definition.

Ok, so if i understood, the normal atribute that i want to refer to cannot have duplicated values. Is that it? — Rafael Valente, May 03 '17 at 09:12

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

Yes. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

refer this:

Foreign Key to non-primary key

edited May 23 '17 at 12:09

Community

1
1

answered May 03 '17 at 09:20

Sunil tc

2,482
4
19
46

Thank you for you response! – Rafael Valente May 03 '17 at 09:26

Create a Foreign Key from an attribute that is not Primary Key?

2 Answers2