views:

171

answers:

7

I created a constraint on my tsql table like this:

alter table disabledqualities
add constraint uc_uIdQualCode
unique (userId, qualitycode)

In MSSMStudio the constraint shows up under indexes rather then under constraints.

Why?

--EDIT--
I understand that it creates an index to enforce the constraint, but then why is there a node called "constraints"?

+4  A: 

SQL Server creates an index behind the scene to enforce the constraint

here is another way of writing that by adding nonclustered telling sql server to use a nonclustered index, you can also create a clustered on providing that you don't have a PK that is clustered (the default) or another clustered index already

alter table disabledqualities
add constraint uc_uIdQualCode
unique nonclustered (userId, qualitycode)

[edit] that node is to add check constraint, unique constraints are added under indexes

either way stay away from wizards

SQLMenace
Man, the wizards get me more and more confused every day..
borisCallens
A: 

SQL uses indexes to enforce unique constraints.

marr75
A: 

I understand that it creates an index to enforce the constraint, but then why is there a node called "constraints"?

This node is to display CHECK constraints.

Quassnoi
A: 

The true answer is "because Microsoft said so. If you want the answer, you'll have to ask them".

John Saunders
A: 

Because UNIQUE constraints can be found in sysindexes system view

select * from sysindexes where name = 'uc_uIdQualCode'

It is simply logical To add new Unique constaint from Studio you need to click Manage Index and Keys button. So if you are adding it from Indexes, you have to see it in Indexes :)

Bogdan_Ch
+1  A: 

Check constraints and default constraints are shown under the constraints node.

adrianbanks
A: 

I'm thinking perhaps you don't understand what might show up in constraints becasue you don't know what a check constraint is. A check constraint will check the data on insert or update to see if it meets some sort of business rule. It is used for ensuring data integrity. For instance if you have an integer field that should only contain the values of 1,4 or 5 then you would set up a check constraint to make sure that 9 isn't ever added to the field. A check constraint on date field might specify that it must be later than the current date and time for a field that is the PlannedCompletionDate or that CompletionDate must be later than StartDate. These are the kind of things that show up under constraints.

HLGEM
Yes, or that a combination of two keys should be unique (without it being the primary key). So I defined a unique constraint..I admit my SQL knowledge can use some improvement so please correct me if I'm wrong.
borisCallens