Why is this kind of foreign keys possible?

Question

Why does the SQL Standard accept this? Which are the benefits?

If have those tables:

create table prova_a (a number, b number);
alter table prova_a add primary key (a,b);
create table prova_b (a number, b number);
alter table prova_b add foreign key (a,b) references prova_a(a,b) ;
insert into prova_a  values (1,2);

You can insert this without error:

insert into prova_b  values (123,null);
insert into prova_b  values (null,123);

Note1: This comes from this answer.

Note2: This can be avoid, setting not null on both columns.

Remarks: I'm not asking about avoid, I'm interested on which are the beneficts.

References:

• Oracle documentation: The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key.

• SQL Server documentation: A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped.

Answer 1

I know some DBMSs simply don't enforce referential integrity when it comes to foreign keys with foreign key constraints. SQLite comes to mind. It's talked about here.

Other DBMSs are different, I know that MS SQL Server will complain if you attempt something like that.

SQLite has its uses but it is not meant to be used in high-concurrency situations. If you are seeing this behavior in a different DBMS, check their documentation to see if they did something similar. Most should be enforcing integrity however.

Answer 2

The SQL standard doesn't accept this; you've found a DBMS that doesn't enforce referential integrity. Uninstall it now if you're smart. At a bare minimum, don't use it for production purposes.

Earlier SQL standards (SQL86) had no referential integrity and SQL89 level 2 fixed that.

Answer 3

at least do your DEV work with a reasonably standard RDBMS, even if you are doing your production system with something like SQLite (which is an excellent database- it runs in your Ipod touch!) It will flush out all these mistakes- like Lint really. If you run your code with SQL Server Express, which you can download for free, you'll get plenty of errors such as...

Msg 8111, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on nullable column in table 'prova_a'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Answer 4

Try adding this declaration:

alter table prova_b add primary key (a,b);

This will forbid NULLS in prova_b. It will also forbid duplicate entries. In Oracle and SQL server, it will also create an index. This index will speed up lookups and joins, at the cost of slowing down inserts a tiny bit.

Is this what you want to do?

As to why standard SQL lets you do something you consider stupid, that's a philosophical question. Most tools allow some stupid choices. Tools that try to forbid all stupid choices generally end up forbidding some really smart choices unintentionally.

Answer 5

Oracle and SQL Server both allow NULL foreign keys, and it is easily understandable why this is necessary. Think of a tree, for instance, where every row has a parent key that references the primary key of the same table. There has to be a root node in the tree that does not have a parent, and the parent key will be null. A more tangible example: think of employees and managers. Some people in the company, and if it is only the CEO, will not have a manager. Were it not possible to set the manager id on the employee table to NULL, you would have to create a "No Manager" employee - something that is just wrong, because it has no real-life correspondence.

Now that we know this, it is obvious why your composite keys behave like they do. Logically, if part of the composite is NULL, the entire key is null. A string concatenation returns NULL if one of the pieces is NULL. There cannot be a match, and the constraint is not enforced in these cases.