Is it possible to use foreign keys with NULL values?

Question

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)

What problem are you trying to solve, exactly? What is the problem with leaving the FK NULL? — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Jun 3, 2014 at 9:27
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 (gpj/profile, CONSTRAINT fk_id_city FOREIGN KEY (fk_id_city) REFERENCES cities (id_city) ON DELETE CASCADE ON UPDATE CASCADE) — Freddie
– Freddie, Commented Jun 3, 2014 at 21:41

Walter Mitty · Accepted Answer · 2014-06-02 20:18:37Z

3

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.

edited Jun 2, 2014 at 20:18

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

answered Jun 2, 2014 at 19:27

nvogel

25.6k3 gold badges47 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Freddie Over a year ago

So you think is better to create a 3° table with non-nullable columns id_profile and fk_id_city rather than having only 2 tables with nulls? What is the criteria to decide to create an extra table?

Walter Mitty Over a year ago

Logically, a NULL in a foreign key corresponds to an optional relationship, a relationship in which the entity may participate zero or one times. That being the case, one would expect a row containing a NULL to drop out of the inner join.

Erwin Smout Over a year ago

The sad thing is that all the DBMS vendors actively punish you in the performance arena for doing things the logically correct way.

Erwin Smout Over a year ago

Surprised to see you ask the question, I must say. They are often a valid design choice because you must ultimately avoid the performance penalty your SQL DBMS vendor imposes on you for doing things the logically correct way. There's a section on NULL and 3VL in Darwen's "Introduction to relational theory" and also in several of Date's books, e.g. "Logic and Databases" ('why database logic must be 2-valued'). Outside of the specific database context, C.A.R. Hoare is on record calling his invention of the "null reference" his billion-dollar mistake.

Erwin Smout Over a year ago

Darwen also has a presentation on the matter at dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf . NULL has been the subject of the most heated of debates for decades in a row, so asking to "compress" it all in a comment here really is a tall order ! The "proposed solution" in the pdf is logically sound and 100% in agreement with the RM, "pure version", however it will take a couple of things to get there : e.g. fix the current integrity support currently offered by SQL engines (way too limited these days)

|

Collectives™ on Stack Overflow

Is it possible to use foreign keys with NULL values?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related