{"id":8538,"date":"2016-07-12T11:36:35","date_gmt":"2016-07-12T09:36:35","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/"},"modified":"2016-07-12T11:36:35","modified_gmt":"2016-07-12T09:36:35","slug":"nulls-in-composite-keys","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/","title":{"rendered":"Nulls in composite keys"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nComparison of NULL can be misleading and it’s even worse for unique constraint validation. Having partial nulls in a composite key can be tricky because the SQL ANSI specification is not very easy to understand, and implementation can depend on the RDBMS. Here is an example with composite unique key and foreign key on Oracle.
\n<\/p>\n

Unique constraint<\/h3>\n

I create a table with a composite unique constraint:<\/p>\n

SQL> create table TABLE1 (a char, b char, unique(a,b));\nTable TABLE1 created.<\/code><\/pre>\n
I can insert a row with a=’X’ and b=’X’:<\/p>\n
SQL> insert into TABLE1 values ('X','X');\n1 row inserted.<\/code><\/pre>\n
I cannot insert the same row:<\/p>\n
SQL> insert into TABLE1 values ('X','X');\n*\nERROR at line 1:\nORA-00001: unique constraint (SYS.SYS_C0015464) violated\n<\/code><\/pre>\n
I insert another row with same value for column a but different value for column b:<\/p>\n
SQL> insert into TABLE1 values ('X','Y');\n1 row inserted.<\/code><\/pre>\n
And another row with same value for column a but a null for column b:<\/p>\n
SQL> insert into TABLE1 values ('X',null);\n1 row inserted.<\/code><\/pre>\n
However, I cannot insert the same a second time:<\/p>\n
SQL> insert into TABLE1 values ('X',null);\n*\nERROR at line 1:\nORA-00001: unique constraint (SYS.SYS_C0015464) violated\n<\/code><\/pre>\n
If you look at documentation<\/a>, this is documented as:
\n Because of the search mechanism for unique key constraints on multiple columns, you cannot have identical values in the non-null columns of a partially null composite unique key constraint.<\/em><\/p>\n
It looks like an implementation reason (the search mechanism is the index that enforces the unique constraint). What is documented in SQL-92<\/a>?
\nA unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. <\/em><\/p>\n
How to interpret this? We cannot insert two (‘X’,null) because that would be two rows with same non-null value (a=’X’) and the Oracle implementation is compilent. <\/p>\n
Or is it? We can also read the definition as the unique constraint being violated only when we find rows that have non-null values and they are the same. This is what MySQL<\/a> and PostgresSQL<\/a> do: accept duplicates when there is at least one null.
\nThis is also what I found more intuitive: I usually consider NULL as a value that is not known at insert time but that will be assigned a value later during the lifecycle of the row. Thus, I expect to be able to insert rows where there is a null and check the constraint only when all columns have a value.<\/p>\n
It is probably an implementation choice from Oracle which stores nulls as a zero-length string and then cannot have two identical entries in a unique index.<\/p>\n
Now inserting a row where a is null and b is null:<\/p>\n
SQL> insert into TABLE1 values (null,null);\n1 row inserted.<\/code><\/pre>\n
And because that do not violate the rule whatever the way we read it (non-null values are not the same as there are no non-null values at all here) I can insert a second one:<\/p>\n
SQL> insert into TABLE1 values (null,null);\n1 row inserted.<\/code><\/pre>\n
This is documented<\/a> as
\nUnless a NOT NULL constraint is also defined, a null always satisfies a unique key constraint<\/em><\/p>\n
About implementation, there is no problem because full null entries are not stored in the index. They are stored in bitmap indexes, but bitmap indexes cannot be used to enforce a unique constraint.<\/p>\n
In summary, here is what can be stored on a table where (a,b) is unique but nullable:<\/p>\n
SQL> select rownum,TABLE1.* from TABLE1;\n \n    ROWNUM A B\n---------- - -\n         1 X X\n         2 X Y\n         3 X  \n         4    \n         5   \n<\/code><\/pre>\n
Foreign key<\/h3>\n
Now that I have a unique key, I can reference it:<\/p>\n
SQL> create table TABLE2 (a char, b char, foreign key(a,b) references TABLE1(a,b));\nTable TABLE2 created.<\/code><\/pre>\n
Yes. You don’t need to reference the primary key. Any unique key, even with nullable columns, can be referenced.<\/p>\n
I can insert a row where parent exists:<\/p>\n
SQL> insert into TABLE2 values('X','X');\n1 row inserted.<\/code><\/pre>\n
As I’ve no unique key on the child, it’s many to one relationship:<\/p>\n
SQL> insert into TABLE2 values('X','X');\n1 row inserted.<\/code><\/pre>\n
I also have a parent with a=’X’ and b=’Y’:<\/p>\n
SQL> insert into TABLE2 values('X','Y');\n1 row inserted.<\/code><\/pre>\n
But I’ve no parent with a=’Y’:<\/p>\n
SQL> insert into TABLE2 values('Y','Y');\n*\nERROR at line 1:\nORA-02291: integrity constraint (SYS.SYS_C0015465) violated - parent key not found<\/code><\/pre>\n
So far so good. I said that I have a many to one relationship, but it’s a many to one or zero because my columns are nullable:<\/p>\n
SQL> insert into TABLE2 values(null,null);\n1 row inserted.<\/code><\/pre>\n
So far so good. But I have a composite key with nullable columns here, and I can insert a row where a=’X’ and b is null:<\/p>\n
SQL> insert into TABLE2 values('X',null);\n1 row inserted.<\/code><\/pre>\n
But do you think that all non null parent values must exist?<\/p>\n
SQL> insert into TABLE2 values('Y',null);\n1 row inserted.<\/code><\/pre>\n
Once again, this is documented<\/a> as:
\nIf 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.<\/em><\/p>\n
And this is what is specified in SQL-92<\/a>:
\nIf no <match type> was specified then, for each row R1 of the referencing table, either at least one of the values of the referencing columns in R1 shall be a null value, or the value of each referencing column in R1 shall be equal to the value of the corresponding referenced column in some row of the referenced table.<\/em> More detail about the other match types in Oracle Development Guide<\/a>.<\/p>\n
That may look strange, but, still thinking about NULLS as unknown values, you can consider that constraints cannot be validated until we know all values.<\/p>\n
Here is what I was able to insert into my table even with no a=’Y’ in the parent:<\/p>\n
SQL> select rownum,TABLE2.* from TABLE2;\n \n    ROWNUM A B\n---------- - -\n         1 X X\n         2 X X\n         3 X Y\n         4 X  \n         5    \n         6 Y  <\/code><\/pre>\n
So what?<\/h3>\n
Having nulls in composite unique key or foreign key can be misleading, then it’s better to ensure that what you define fits what you expect. It’s probably better to prevent partial nulls in foreign key (a check constraint can ensure that if one column is null then all columns must be null) or to have and additional referential integrity constraint which ensures that you can set only the allowed values for a subset of columns (in our case, a table with column a as primary key that we can reference).<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Comparison of NULL can be misleading and it’s even worse for unique constraint validation. Having partial nulls in a composite key can be tricky because the SQL ANSI specification is not very easy to understand, and implementation can depend on the RDBMS. Here is an example with composite unique key and […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[876,96,98,877],"type_dbi":[],"class_list":["post-8538","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-constraint","tag-oracle","tag-sql","tag-unique"],"acf":[],"yoast_head":"\nNulls in composite keys - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Nulls in composite keys\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Comparison of NULL can be misleading and it’s even worse for unique constraint validation. Having partial nulls in a composite key can be tricky because the SQL ANSI specification is not very easy to understand, and implementation can depend on the RDBMS. Here is an example with composite unique key and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-12T09:36:35+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Nulls in composite keys\",\"datePublished\":\"2016-07-12T09:36:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\"},\"wordCount\":889,\"commentCount\":0,\"keywords\":[\"Constraint\",\"Oracle\",\"SQL\",\"Unique\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\",\"name\":\"Nulls in composite keys - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-07-12T09:36:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Nulls in composite keys\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Nulls in composite keys - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/","og_locale":"en_US","og_type":"article","og_title":"Nulls in composite keys","og_description":"By Franck Pachot . Comparison of NULL can be misleading and it’s even worse for unique constraint validation. Having partial nulls in a composite key can be tricky because the SQL ANSI specification is not very easy to understand, and implementation can depend on the RDBMS. Here is an example with composite unique key and […]","og_url":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/","og_site_name":"dbi Blog","article_published_time":"2016-07-12T09:36:35+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Nulls in composite keys","datePublished":"2016-07-12T09:36:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/"},"wordCount":889,"commentCount":0,"keywords":["Constraint","Oracle","SQL","Unique"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/","url":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/","name":"Nulls in composite keys - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-07-12T09:36:35+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/nulls-in-composite-keys\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Nulls in composite keys"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8538","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8538"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8538\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8538"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}