Can table columns with a Foreign Key be NULL

Question

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  amp  InnoDB table type   I believe this is a reasonable expectation  but correct me if I am wrong

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The above 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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Can table columns with a Foreign Key be NULL?

Examples related to sql

Examples related to mysql

Examples related to database

Examples related to foreign-keys