MySQL : How to give a default value to a foreign key when I don't use it?

Question

Right now I have a table that has two foreign key, both that reference to different tables. When I insert a value in one foreign key field, but not in the other one, my insert fails because the foreign key constraint. How can I give a default value, like NULL or 0, to a column that references a foreign key, when I am not using it, or make the foreign key optional? I am using MySQL and XAMPP for my tables.

score 3 · Accepted Answer · answered Jun 05 '17 at 17:59

3

Declare the column as nullable.

Then use NULL in the column when you aren't using the foreign key.

answered Jun 05 '17 at 17:59

Bill Karwin

538,548
86
673
828

I answered this similar question over eight years ago! https://stackoverflow.com/a/442000/20860 – Bill Karwin Jun 05 '17 at 18:02
Well, that was more simple than I expected. I thought that since the column is declared as a foreign key, it cannot be set as NULL. Thank you. – Artyomska Jun 05 '17 at 19:14

score 0 · Answer 2 · answered Jun 05 '17 at 18:03

First, if its a key it can not be optional also can not be null/default, its simple logic. I don't remember how to but you could create a group of keys (composed key) then the pair you can set a default value always that at least one of them is uniq:

<key1.a ,   0   >,
<key1.b ,   0   >,
<   0   , key2.a >,
<   0   , key2.b >,
<key1.a , key2.b >,
<key1.a , key2.c >,etc...

MySQL : How to give a default value to a foreign key when I don't use it?

2 Answers2