MySQL: How think about Null insert to foreign key column

Posted on

Question :

I’m designing table relationship. I have an one question.

Parent: A table

Child: B table

Table: A
PK: id


Table: B
PK: id
Column: A_id(NOT NULL), desk, chair...
Option: CONSTRAINT `fk_B_A_id` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) 

My case is, B table sometimes has A_id, sometimes doesn’t have A_id.

So I’m trying to insert 0 for A_id when B table doesn’t need the A_id value. But A table doesn’t have id 0. As a result error occurs.

I’m thinking two solutions:

  1. A_id can have null.
  2. Dummy data ( insert to A table when A table create.

If you have other idea Please tell me.

Thank you.

Answer :

If the relationship for child B against A isn’t mandatory then just allow NULL on the foreign key column. It’s very common to do this.

Don’t use a default record (like ID = 0) to match against when there shouldn’t really be one, it will bring you more trouble that solutions (what data will this record have? what happens with NOT NULL columns?)

Leave a Reply

Your email address will not be published. Required fields are marked *