Can a Foreign Key Be Null And/Or Duplicate

Can a foreign key be NULL and/or duplicate?

Short answer: Yes, it can be NULL or duplicate.

I want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an FK is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.

Let me give you a real life example. Suppose you have a database that stores sales proposals. Suppose further that each proposal only has one sales person assigned and one client. So your proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, at the time the record is created, a sales rep is not always assigned (because no one is free to work on it yet), so the client ID is filled in but the sales rep ID might be null. In other words, usually you need the ability to have a null FK when you may not know its value at the time the data is entered, but you do know other values in the table that need to be entered. To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK. The null value is separate from the idea of it being an FK.

Whether it is unique or not unique relates to whether the table has a one-one or a one-many relationship to the parent table. Now if you have a one-one relationship, it is possible that you could have the data all in one table, but if the table is getting too wide or if the data is on a different topic (the employee - insurance example @tbone gave for instance), then you want separate tables with a FK. You would then want to make this FK either also the PK (which guarantees uniqueness) or put a unique constraint on it.

Most FKs are for a one to many relationship and that is what you get from a FK without adding a further constraint on the field. So you have an order table and the order details table for instance. If the customer orders ten items at one time, he has one order and ten order detail records that contain the same orderID as the FK.

Should Foreign Key Columns be Unique and Not Null?

All databases allow foreign keys to be NULLable and non-UNIQUE. How you choose to declare a particular foreign key depends on the business case.

Consider the following tables used by a company that sells supplies to secret agents.

CountryList (
  CountryCode NOT NULL PRIMARY KEY, 
  CountryName NOT NULL
)
SecretAgents (
  CodeName NOT NULL PRIMARY KEY, 
  HomeCountryCode FOREIGN KEY REFERENCES CountryList(CountryCode)
)

Clearly, HomeCountryCode will not be unique because you may sell to more than one spy in each country. Is it NULLable? That depends on whether your business model requires each customer to declare their home country or not. If the model allows you to do business with someone who does not have a home country, or does not wish to reveal the home country to you, then the field should be NULLable. But if a state-less actor is not contemplated in your business model you should declare the column NOT NULL so that an invalid customer record cannot be created.

Now, consider the additional table

SpyMasters (
  CountryCode NOT NULL PRIMARY KEY References CountryList(CountryCode),
  Name NOT NULL PRIMARY KEY
)

This table lists the (singleton) head of spying for those countries that have a spy master. Not all countries will appear in this list, but each country can appear only once. In this case the CountryCode field is UNIQUE -- but you don't have to declare that explicitly because PRIMARY KEY always includes uniqueness.

Under what conditions must a foreign key not be null?

A foreign key may not be null when it is part of a composite primary key in the child table.

tableA
id (int) PK

tableB
id (int) PK

tableAB
aId (int)
bId (int)
(aId, bId) PK

does foreign key always reference to a unique key in another table?

By the SQL standard, a foreign key must reference either the primary key or a unique key of the parent table. If the primary key has multiple columns, the foreign key must have the same number and order of columns. Therefore the foreign key references a unique row in the parent table; there can be no duplicates.

---

Re your comment:

If T.A is a primary key, then no you can't have any duplicates. Any primary key must be unique and non-null. Therefore if the child table has a foreign key referencing the parent's primary key, it must match a non-null, unique value, and therefore references exactly one row in the parent table. In this case you can't make a child row that references multiple parent rows.

You can create a child row whose foreign key column is NULL, in which case it references no row in the parent table.

Foreign key that can hold duplicate values

can you have a column which is referenced as a foreign key by another table contain duplicate values?

No - a foreign key constraint must reference a unique key.

You can still keep a (non-foreign-key) reference to the product_rate_id value in related tables, but you'll have to decide which related record you want when querying (I'm assuming by using an effective date between start_date and end_date.

Can you add a relationship to a primary key if the second table's column can be null?

You can declare a foreign key column as nullable. It has no effect on the referenced table.

NULLs don't match anything. So a NULL in a foreign key does not require the referenced primary key of the users table contain a row with NULL.

I feel like I have answered this before... ah yes, here it is, from January 2009 (nearly 10 years ago):

MySQL foreign key to allow NULL?

---

Related Topics