Can 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.

MySQL Can Foreign Key Be Null

Yes it is possible, and no it is not bad practice. You will want to make sure you take this into account when running queries on your database. If you attempt to run an inner join with your selects, then you will automatically exclude all rows with a null value. In cases where you want to join on that relationship even if the foreign key is null, you will want to use an outer join.

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

Nullable Foreign Key bad practice?

Having the link table is probably a better option. At least it does not violate normalization BCNF (Boyce-Codd normal form). however I would favor being pragmatic. If you have very few of these null values and they are only temporary I think you should skip the link table since it only adds complexity to the scheme.

On a side note; using a link table doesn't necessarily make it n to n, if you in the link table use the foreign key that's pointing to your orders table as the primary key in that link table the relationship is still 1..n. There can only be one entry in that link table per order.

MySql: Why foreign key is null?

Q: Why foreign key is null?

A: Whether or not a column can contain a NULL value is determined by the presence or absence of a NOT NULL constraint. This is entirely independent of whether the column is referenced in a foreign key constraint.

The value in the column is NULL because that's the value that was assigned when the row was inserted. The value was assigned, whether it was explicitly set, or whether it was derived from the default value for the column. (If the column was added to an existing table, then the values in the new column was the default value for the column.)

--

Q: Do I miss something or what?

A: The behavior and results you observe are exactly as we expect.

Q: Shouldn't it copy the id values from users table?

A: If you're asking if MySQL should automatically populate the user_id column in the post table, the answer to that question is no, it shouldn't.

I think maybe you've clued in on a key idea:

The "relationship" between a row in one table to a row in another table is represented in the relational database by storing a common value.

But the database doesn't know which row is related to which row. You have to tell it. You have to provide that information.

When you insert a row into the post table, you can provide a value for the user_id column. You would provide a value that's equal to the id value of some row in user.

The idea with a FOREIGN KEY constraint is that it's restriction. It only allows valid values. It prevents an invalid value from being stored. (That's true with InnoDB if FOREIGN_KEY_CHECKS=1, that's not true for MyISAM, because MyISAM doesn't enforce foreign key constraints.)

The foreign key is saying that you want to "constrain" the values that can be stored. It's saying that it's not going to allow rows in post to have have user_id values that point to a "missing" row in the users table.

It's perfectly acceptable to store a NULL in a foreign key column. When a NULL value is stored, that's saying that the row is not related to a row in the users table.

Disallowing NULL values in a column is done with a different kind of constraint, a NOT NULL constraint.

It's possible to define both a foreign key constraint and a NOT NULL constraint on the same column. That's a design decision, whether you want to allow NULL values or not. In some cases, we may want to disallow NULL values in a foreign key. For example, if we were to add a NOT NULL constraint on the user_id column of post, that would effectively be saying that a row cannot exist in post if it's not related to a row in users. And that's a very common pattern.

---

Related Topics