{"id":553,"date":"2009-02-28T17:37:00","date_gmt":"2009-02-28T17:37:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Foreign-Keys-can-reference-UNIQUE-indexes-(without-constraints).aspx"},"modified":"2009-02-28T17:37:00","modified_gmt":"2009-02-28T17:37:00","slug":"foreign-keys-can-reference-unique-indexes-without-constraints","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/foreign-keys-can-reference-unique-indexes-without-constraints\/","title":{"rendered":"Foreign Keys can reference UNIQUE indexes (without constraints)"},"content":{"rendered":"

\nSomething I learned while the SQL Server 2008 Internals<\/a> book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez – who, unfortunately, does not have a blog or anything…yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index – one without a PRIMARY KEY or UNIQUE key constraint. At first glance this might seem like something relatively insignificant but in terms of reducing indexes and\/or consolidating indexes it offers something that constraints do not. When you create a UNIQUE index you can use INCLUDE to reference (and include) non-key columns in the leaf level of an index. This offers more choices for covering and if you want to cover a query using INCLUDE but also have a UNIQUE column(s) as the key – you can do that with a regular index but not with a constraint based index. So, that got me thinking even more – can I use a UNIQUE index with INCLUDE and even a filters – from a FOREIGN KEY. My guess was that it probably wouldn't work because it would be too costly to have to verify it on every referencing row BUT, I did have hopes that a filter of IS NOT NULL would work. However, it does not. ;-( \n<\/p>\n

\nSo, you CAN reference a UNIQUE index with INCLUDEd columns but not filters. Even that's really cool!\n<\/p>\n

\nAnd, when you start your spring cleaning – try and cleanup and\/or consolidate some of those redundant indexes!!\n<\/p>\n

\nCheers,
\nkt <\/p>\n","protected":false},"excerpt":{"rendered":"

Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez – who, unfortunately, does not have a blog or anything…yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index – one without a PRIMARY KEY or […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,36,65,66],"tags":[],"class_list":["post-553","post","type-post","status-publish","format-standard","hentry","category-foreign-keys","category-indexes","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=553"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/553\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}