Foreign key SQL SET NULL

Question

Well folks, let’s have one more question from me, I hope you can help me.

I have the following problem:

When I create a foreign key linking to another field of another table I always define it as CASCADE. However when I will register a field (let’s say in this table I have more foreign keys and one of them may be NULL) I cannot successfully register the table because it does not find in the other table referenced the value.

I think it’s because I’m using the type CASCADE and that SET NULL would care to let this field exist with the value NULL.

Defining my foreign key as SET NULL solve my problem ?

Answer 1

Names to the oxen

The table To has a foreign table key B.

The solution

Set the field of To as NULL.

The explanation

The CASCATE foreign keys are triggered when changes occur occur in the table referenced, no reference table. In this case, a CASCATE SET NULL will make the field of To null when the record is deleted from B.

This is a different case. You want records on To are born without a record referring to B. This has nothing to do with CASCATE, but rather as the very definition in To.