Is it necessary for a foreign key to be a primary key in another table? Why?

Question

I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table.

Foreign key is not unique - it may have duplicate entries. If it must be primary key of another table then why it is necessary?

I think any column can be a foreign key because you can put condition at any column.

Answer 1

The referencing column may not be unique, as you point out. If the referenced column is also not unique, you have created a many-to-many relationship, which is a right pain to implement and can cause problems maintaining data integrity.

For example, I have two rows in table 1 with the same value in the referenced column. I also have two rows in table 2 with the same value in the referencing column. I then change one of the rows in table 1; which rows in table 2 should also be updated?

If the referenced column is unique, but is not the primary key, then your table may be in an "abnormal form" and the additional unique column may be redundant. The test for this is to ask yourself two questions:

1. If I make the unique column(s) the new primary key, and drop the old primary key column(s), do I lose any data about the entity? If not, your table is in an abnormal form.
2. Can the unique column(s) change during the lifespan of the entity? If not, then the column(s) constitute a key candidate key.

In practice, there are sometimes very good reasons for denormalizing tables, but these must be considered carefully on a case-by-case basis.

Note that it is possible to reference a unique non-primary-key column or column group. However, since conceptually the primary key is "The" canonical identifier for the row, it makes more sense to reference the primary key.

Answer 2

is it necessary for a foreign key to be primary key of another table

Yes, foreign key has to be primary key of parent table.

foreign key is not unique it may have duplicate entries

Yes, it may not be unique and may have duplicate entries in child table, but it must be unique and does not have any duplicate entries at the parent table (as it is a primary key).

For example consider below tables:

create table city (city_id int(PK), name varchar(25));
create table author (author_id int(PK), city int (FK), name varchar(25));

Here, in the author table, the city (foreign key) column can be non-unique as multiple authors belongs to a city. But in city table, city_id (primary key) must be unique as each city must have only one city_id.

However, if you really need to reference non-primary key as foreign key to other table, make sure its a unique column in parent table - sometimes this is helpful as well.

Have a look at my blog post for background.

Answer 3

No! A foreign key may not need to be the primary key for the child Table, but it is always a primary key for the parent's table

Answer 4

Actually, a foreign key may reference a unique key which allows NULLs that's why it is best for the child table to have the PK/FK on the same column.