Can Table Columns With a Foreign Key Be Null

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.

Can table columns with a Foreign Key be NULL?

Yes, you can enforce the constraint only when the value is not NULL. This can be easily tested with the following example:

CREATE DATABASE t;
USE t;

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT NULL, 
                    parent_id INT NULL,
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=INNODB;


INSERT INTO child (id, parent_id) VALUES (1, NULL);
-- Query OK, 1 row affected (0.01 sec)


INSERT INTO child (id, parent_id) VALUES (2, 1);

-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key 
-- constraint fails (`t/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
-- (`parent_id`) REFERENCES `parent` (`id`))

The first insert will pass because we insert a NULL in the parent_id. The second insert fails because of the foreign key constraint, since we tried to insert a value that does not exist in the parent table.

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.

Can we set foreign key on a unique column that contains null?

can we create a foreign key based on unique constraint which has null ?

The syntax allows us to create a foreign key which references a unique key. However, I think it would be if not exactly bad practice then at least peculiar practice to do so. Primary keys are the norm.

If yes, how is that possible?

It's possible because a foreign key column can itself be optional. A foreign key constrains us to entering a value in the child column which is present in the referenced primary key column. However, if we put a null in the child column then the foreign key is not enforced. This is true whether the foreign key references a primary key or a unique key.

Obviously, if the child column is mandatory (defined as NOT NULL) then we cannot put a null in it, and it really doesn't matter whether the referenced column is a unique key or a primary key.

Having not null foreign key reference nullable column

This error you are getting has nothing to do with one column being nullable and one not.
You are getting this error because the column providerID is not a key of any kind according to your table structure.

So you can't add it as a foreign key to another table.

To remedy this, you either have to make it part of a key or rethink if it actually should be part of one or if you should drop the foreign key constraint all together.

---

Related Topics