Constraint is key is index is constraint?

Answer 1

A unique constraint is implemented internally as an index.

Pretty much the only difference between explicit CREATE INDEX and adding a constraint via ALTER TABLE is the ability to have INCLUDE columns as an explicit index.

SSMS is somewhat confusing in how it presents this. No idea why

Personally, I think IGNORE_DUP_KEY is pointless and have never used it.

Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. The default is OFF.

ON A warning message is issued and only the rows violating the unique index fail.

OFF An error message is issued and the entire INSERT transaction is rolled back.

The IGNORE_DUP_KEY setting applies only to insert operations that occur after the index is created or rebuilt. The setting has no affect during the index operation.

Edit:

• Found this from me before: http://stackoverflow.com/questions/2594193/can-i-set-ignore-dup-key-on-for-a-primary-key

• Generally, IGNORE_DUP_KEY has several answers too

Answer 2

first off all your table has only 1 value not 3, take a look

create table B (Id int)


ALTER TABLE B
ADD CONSTRAINT IX_B
UNIQUE (ID)
WITH (IGNORE_DUP_KEY = ON)

insert into B VALUES (1)
insert into B VALUES (1)
insert into B VALUES (1)

--Duplicate key was ignored.
--Duplicate key was ignored.

select * from B

One row, right? This is because you have this WITH (IGNORE_DUP_KEY = ON)

now do this

create table C (Id int)


ALTER TABLE C
ADD CONSTRAINT IX_C
UNIQUE (ID)


insert into C VALUES (1)
insert into C VALUES (1)

The second row won't go into the table now

The way that SQL Server implements constraint is that it creates an index behind it to facilitate fast lookups among other things. Perhaps you really want a primary key on this table?

create table D (Id int not null primary key)

Answer 3

This only answers part of your question, as I'm not clear on why management studio displays these objects the way it does.

A unique constraint is part of the (logical) relational model. Essentially if you were drawing out the logical model, a unique constraint would appear on the drawing.

A unique index (like all indexes) is an implementation detail, so is part of the physical model.

SQL Server uses a unique index to implement a unique constraint. There is a logical difference, and I think this shows up in the SQL Server diagramming tool--if you use a unique constraint on a foreign key in what otherwise would be a 1:n relationship, it shows up as a 1:1. This is not true when using a unique index (again, not 100% sure on this).

Answer 4

A UNIQUE constraint is also known as a UNIQUE KEY constraint. Basically, there can be multiple KEYs for a table. One is selected (somewhat arbitrarily) to be the PRIMARY KEY for the table. Other keys are created as UNIQUE KEY constraints.

As an implementation detail, a UNIQUE KEY constraint is implemented by placing a UNIQUE index on the same columns in the table. However, it is possible to create UNIQUE indexes on a table (via CREATE INDEX), without creating a UNIQUE constraint.

UNIQUE constraints are similar to PRIMARY KEYs - they can be the target reference for a FOREIGN KEY constraint. A UNIQUE index, by itself, cannot be so referenced.

In SSMS, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints will always show up under the "Keys" folder for a table. CHECK and DEFAULT constraints will show up under the "Constraints" folder