CODEWITHSUNDEEP
database-designforeign-keys
Freddie
Freddie

Reputation: 117

Is it possible to use foreign keys with NULL values?

I know that similar questions were asked before, but still not clear for me.

I have a table called “cities” and a table called “profiles”. The city is an optional field to be entered by the user on the table “profiles”. So fk_id_city might be NULL.

1) What is the best practice to manage this situation if foreign key wants to be used? (Some people suggested me not to use FK in these cases).

2) One idea that occur to me is to have the first row of the table “cities” as “to be defined” so if the user doesn’t select any city from the form, instead of having a NULL field I will have a “1”. Would you suggest this?

E.G.

Table cities

id_city (pk)      city_name 
---------------------------------
1                 to be defined
2                 New York
3                 London 
4                 Buenos Aires

Table profiles

id_profile (pk)      Name         fk_id_city 
----------------------------------------
1                    Paul            2       
2                    John            1 
3                    Paul            1

Additional info: If I try to leave the fk_id_city empty, I get the following error: ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (db/profile, CONSTRAINT fk_id_city FOREIGN KEY (fk_id_city) REFERENCES cities (id_city) ON DELETE CASCADE ON UPDATE CASCADE)

Upvotes: 1

Views: 211

Answers (1)

nvogel
nvogel

Reputation: 25526

In standard SQL, columns with a FOREIGN KEY constraint defined on them will permit nulls even where there is no corresponding null in the table being referenced. Nulls are permitted if you choose to allow them by not specifying NOT NULL in the column definition. There are multiple issues with this however. Not all DBMSs follow the ISO standard and so the behaviour can differ between different products. Some applications and development tools may also treat nullable constraints in ways that are inconsistent with your DBMS. Database users probably won't understand what a nullable foreign key means or know what behaviours to expect - for example many people might write an inner join between a foreign key column and the column it references and expect all the rows to be returned, but that won't be the case if some of the values are null.

So think about what a nullable foreign key means logically in your data model. Why do you want to populate nulls when no city has been defined? A simple alternative in your case would be to create a new table with non-nullable columns id_profile and fk_id_city. Only populate that table when you have a city defined.

Upvotes: 3

Related Questions