11

I inherited a SQL Server 2005 3rd party server and database to look into to be able to generate reports and dashboards on.

On one table, I found a new icon: a blue-silver upside-down key, alongside my other primary and foreign keys.

When I right-click it, the context menu gives me the option to "Script Constraint as", but when I try to "Modify" it, it opens the column design form (like primary keys), and yet, it appears in the "Indexes" section, not constraints.

Someone knows what it is called and what it is used for?

Upside-down key

1 Answer 1

12

It's signifying a unique constraint. In SQL Server, a unique constraint is actually implemented as an index under the covers; this is why it appears under the Indexes node as well (I can't give you a good explanation of why it is not at least listed under the Constraints node, nor why they chose an upside down key instead of just a different color).

You should see a result here to validate that this is actually implemented as an index:

SELECT index_id, type_desc, is_unique_constraint
  FROM sys.indexes
  WHERE name = N'IX_tblCustIndex';

You said that you saw "Script Constraint As..." - if you followed through with that it would have provided you a script something like:

ALTER TABLE [dbo].[tblCustIndex] ADD UNIQUE NONCLUSTERED 
(
  [some_column] ASC
) WITH (...a bunch of default options...)

But don't run it because this syntax does not use a name and it will just create a second, redundant unique constraint...

Here is a very simple repro, and proof that I get the upside-down key even with very basic syntax, on 2014:

CREATE TABLE dbo.foo(id INT UNIQUE);

enter image description here click to enlarge

Now, you can get a different icon if you create a unique index explicitly (and the only differences you'll see in sys.indexes are the name and the value of is_unique_constraint):

enter image description here click to enlarge

For other differences see this Stack Overflow answer.

3
  • 1
    Is it specific to SQL2005? Because when I try to add a UNIQUE index, I get a different icon (some kind of reversed tree). And when I add a Constraint, I get some kind of square-bracketed-table icon. I can't seem to be able to reproduce this icon.
    – Philippe
    Feb 25, 2015 at 20:55
  • @Philippe Updated answer. Feb 25, 2015 at 21:05
  • Thanks. That was a detailled explanation, and a quick one! :-)
    – Philippe
    Feb 25, 2015 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.