368

I have a table which has several ID columns to other tables.

I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, then it should also check the constraint.

(This is likely database server dependant, I'm using MySQL & InnoDB table type)

I believe this is a reasonable expectation, but correct me if I am wrong.

Improve this question
2
  • 7
    I don't know about MySQL, but MS SQL Server allows foreign keys to be nullable with the semantics that you want. I expect that is standard behavior. Commented Mar 2, 2010 at 21:22
  • 2
    the foreign key, cannot be null by default in mySQL, the reason is simple, if you reference something and you let it null, you will loose data integrity. when you create the table set allow null to NOT and then apply the foreign key constraint. You can not set null on update, it should send you an error, but you can (you must) simply not update this column and update only the fields you need to change. Commented Jun 22, 2016 at 9:46

7 Answers 7

Reset to default
355

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

The parent table can also be declared with id INT NOT NULL.
@CJDennis If you make it so that only one row can have a null ID, it could be used as fallback values for other rows. (Though it might work out better for the DB if you just use more columns.) The default constraint seems like a problem if you want to know later whether a value was originally set as "default" (by using null) or set to a value that happens to be the same as "default". By having a row with a null id, you can clearly indicate that this row is not to be used as a normal row, and can use the row as a way of providing a sort of dynamic default value for other rows.
i think the parent_id INT NULL part is (verbosely) equal to parent_id int default null
Side note for java users, if you use ibatis or other ORM and user the primitive int instead of Integer in your class members the default will never be null, but will be 0 and you will fail the constraint.
It may work for inserts but when deleting the source data then an error will happen: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails
|
47

I found that when inserting, the null column values had to be specifically declared as NULL, otherwise I would get a constraint violation error (as opposed to an empty string).

Improve this answer

6 Comments

Could you not set a default value of NULL on the column to allow this?
Yes, in most languages NULL is different than an empty string. Perhaps subtle when beginning, but critical to remember.
Hey Backslider, you say "(as opposed to an empty string)", but I don't think you meant that you would INSERT a value of empty string, but rather, that you don't specify a value at for the Value at all? i.e. you don't even mention the column in your INSERT INTO {table} {list_of_columns} ? Because that's true for me; omitting mention of the column causes error, but including and explicitly setting to NULL fixes error. If I'm correct, I think @Gary's comment doesn't apply (because you didn't mean an empty-string), but @Kevin Coulombe's could be helpful...
Yes, @KevinCoulombe's suggestion works, I described how to achieve this with Entity Framework Core's Migration scripts, here
Important to point out that the rationale for being explicit when updating a record containing NULL foreign keys only applies to string types (varchar, etc), because otherwise an empty string can be passed as default. This is the case with MySQL, and results in an integrity error on update.
|
11

Yes, that will work as you expect it to. Unfortunately, I seem to be having trouble to find an explicit statement of this in the MySQL manual.

Foreign keys mean the value must exist in the other table. NULL refers to the absence of value, so when you set a column to NULL, it wouldn't make sense to try to enforce constraints on that.

Improve this answer

2 Comments

By design Foreign Key must refer to some key(Primary) which is not NULL, but during development phase when we need to have multiple data first inserted into child table, which we don't know whom it will refer to (the parent table). That is why we have NULL value allowed. In production having NULL will be a design flow, that can be roughly said.
@vimalkrishna Not neccessarily - the primary key cannot contain null, but the foreign key can be null if the child doesn't neccessarily have a parent element. For example if the parent elements are warehouses, and the child elements are products, a NULL value can mean that the product is not currently stored in a warehouse. This makes much more sense than creating a virtual "no warehouse" warehouse to satisfy a foreign key constraint that doesn't allow nulls.
6

Yes, the value can be NULL, but you must be explicit. I have experienced this same situation before, and it's easy to forget WHY this happens, and so it takes a little bit to remember what needs to be done.

If the data submitted is cast or interpreted as an empty string, it will fail. However, by explicitly setting the value to NULL when INSERTING or UPDATING, you're good to go.

But this is the fun of programming, isn't it? Creating our own problems and then fixing them! Cheers!

Improve this answer

Comments

2

Another way around this would be to insert a DEFAULT element in the other table. For example, any reference to uuid=00000000-0000-0000-0000-000000000000 on the other table would indicate no action. You also need to set all the values for that id to be "neutral", e.g. 0, empty string, null in order to not affect your code logic.

Improve this answer

1 Comment

This is not the same thing. A default or "neutral" value is not the same as NULL, the absence of a value. Without discussing the merit of a default value over a NULL, your phrasing is a litte mixed. "Another way around this would be to insert a null element in the other table" should say something more like "Another way around this would be to insert a DEFAULT element in the other table"
1

I also stuck on this issue. But I solved simply by defining the foreign key as unsigned integer. Find the below example-

CREATE TABLE parent ( id int(10) UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id int(10) UNSIGNED NOT NULL, parent_id int(10) UNSIGNED DEFAULT NULL, FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;
Improve this answer

Comments

1

The accepted answer works but this does not. Note the ON DELETE CASCADE

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) ON DELETE CASCADE ) ENGINE=INNODB; INSERT INTO child (id, parent_id) VALUES (1, NULL); -- Query OK, 1 row affected (0.01 sec)
Improve this answer

1 Comment

What do you mean by 'the above'? Note that if you're referring to another answer the order may change.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.