{"id":69423,"date":"2016-12-27T10:41:41","date_gmt":"2016-12-27T10:41:41","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=69423"},"modified":"2016-12-28T17:55:27","modified_gmt":"2016-12-28T17:55:27","slug":"allow-nulls-unique-fields","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/allow-nulls-unique-fields\/","title":{"rendered":"How to allow nulls in unique fields"},"content":{"rendered":"

An interesting workaround using unique fields<\/h2>\n

Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index.<\/p>\n

Regardless of\u00a0using unique constraint or unique index, the field can accept null values, however\u00a0the uniqueness will result in only accepting a single row with null value.<\/p>\n

The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.<\/p>\n

First, let’s see the regular behavior of unique fields:<\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0testunique<\/span>
\u00a0\u00a0(<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>,<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0value<\/span>\u00a0VARCHAR<\/span>(<\/span>30<\/span>)<\/span>\u00a0NULL<\/span>\u00a0UNIQUE<\/span>
\u00a0\u00a0)<\/span> <\/p>\n

go<\/span> <\/p>\n

—\u00a0The\u00a0first\u00a0two\u00a0inserts\u00a0will\u00a0work<\/span>
INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>‘value1’<\/span>)<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>NULL<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

–Next\u00a0two\u00a0inserts\u00a0will\u00a0fail,\u00a0they\u00a0aren’t\u00a0unique\u00a0\u00a0<\/span>
INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>‘value1’<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>NULL<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

DROP<\/span>\u00a0TABLE<\/span>\u00a0testunique<\/span>\u00a0 <\/span><\/div>\n

Now let reapeat the same example again, using a filtered index:<\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0testunique<\/span>
\u00a0\u00a0(<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>,<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0value<\/span>\u00a0VARCHAR<\/span>(<\/span>30<\/span>)<\/span>\u00a0NULL<\/span>\u00a0
\u00a0\u00a0)<\/span> <\/p>\n

go<\/span> <\/p>\n

—\u00a0Creating\u00a0the\u00a0filtered\u00a0index<\/span>
CREATE<\/span>\u00a0UNIQUE<\/span>\u00a0INDEX<\/span>\u00a0indunique<\/span>
\u00a0\u00a0ON<\/span>\u00a0testunique<\/span>(<\/span>value<\/span>)<\/span>
\u00a0\u00a0WHERE<\/span>\u00a0value<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span> <\/p>\n

go<\/span> <\/p>\n

—\u00a0The\u00a0first\u00a0two\u00a0inserts\u00a0will\u00a0work<\/span>
INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>‘value1’<\/span>)<\/span> <\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>NULL<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

–This\u00a0insert\u00a0will\u00a0fail,\u00a0the\u00a0first\u00a0value\u00a0is\u00a0already\u00a0present\u00a0\u00a0<\/span>
INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>‘value1’<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

—\u00a0this\u00a0second\u00a0insert\u00a0will\u00a0work,\u00a0null\u00a0values\u00a0are\u00a0not\u00a0part<\/span>
—\u00a0of\u00a0the\u00a0index,\u00a0therefore\u00a0duplicate\u00a0values\u00a0are\u00a0accepted<\/span>
INSERT<\/span>\u00a0INTO<\/span>\u00a0testunique<\/span>
VALUES<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>NULL<\/span>)<\/span> <\/p>\n

go<\/span> <\/p>\n

DROP<\/span>\u00a0TABLE<\/span>\u00a0testunique<\/span>\u00a0 <\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

An interesting workaround using unique fields Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index. Regardless of\u00a0using unique constraint or unique index, the field can accept null values, however\u00a0the uniqueness will result in only accepting a single row with null value. The solution……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-69423","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69423","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=69423"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69423\/revisions"}],"predecessor-version":[{"id":69427,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/69423\/revisions\/69427"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=69423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=69423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=69423"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=69423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}