Can table columns with a Foreign Key be NULL?

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.

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.

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

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.

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!

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)

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.

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;