4

in SQL Server Management Studio (SSMS) 2008 R2 Dev on the table (because I cannot ask without it) 

--SET ANSI_NULL_DFLT_ON ON
create table B (Id int)

I create unique constraint 

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

SSMS shows that I do not have any constraint but have a key + index instead while context options (on right clicking) hint me to create (sorry, script) still constraint.

alt text alt text

MAIN QUESTION:
What is a key here?
Why is it needed for this constraint?
Why is unique constraint called by key (and key by unique constraint)?
Sorry, again, why is key called by index? They seem to have the same name (though had I created without explicit name they would have called differently)...
Sorry, again...

COLLATERAL questions:
Which functionality is different between "unique constraint" vs. "unique index"? I searched today for a single difference (wanted to find 10) quite a time and could not find any.
In other words, what's for (why) are concepts (or constructs) "unique constraint" and "unique index" are duplicated in SQL Server?

Bonus question (for those whom this question seemed too simple):
What is the sense in unique index (or in unique constraint) permitting dupes? 

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

Update: Sorry Thanks, guys and ladies (bonus is withdrawn)
Update2: Was not there a difference between "unique index" and "unique constraint" in previous SQL Server (I vaguely recall that one of them did not permit NULL)?
Update3: Really, I was always pissed off (confused) by a "foreign key constraint" is called by "foreign key" while it is not a key and the key, foreign one is in another table, foreign one ... and just found that this is universal confusion to rattle on it. At least, now I memorize that I should remember au contraire.

Update4:
@Damien_The_Unbeliever, thanks,
these are, at least, some hrooks to memorize confusion.

Though, some bewilderments:
Why are these candidates NOT NULL by default?

Initially I really wanted to insert much shorter script: 

CREATE TABLE A(A INT UNIQUE);

which produced:
alt text

WTF this "candidate" for PRIMARY and KEY has multiple identities syndrome, then?
Is not "UQ_" stand for Unique constraint in naming practices?

Now, scripting of this index UQ__A__3214EC262AA05119 produces nameless... not index ... constraint(?!): 

ALTER TABLE [dbo].[A] ADD UNIQUE NONCLUSTERED 
(
    [ID] ASC
)
WITH 
(    PAD_INDEX  = OFF, 
     STATISTICS_NORECOMPUTE  = OFF, 
     SORT_IN_TEMPDB = OFF,
     IGNORE_DUP_KEY = OFF,
     ONLINE = OFF,
     ALLOW_ROW_LOCKS  = ON,
     ALLOW_PAGE_LOCKS  = ON) 
ON 
[PRIMARY]

WTheF - How is unique index is identified as constraint? Where, by what

Scripting of key produces the same! again, constraint... Why is it nameless? Why is it impossible to script as "modify" but only as "create"?!
This does not make sense!

Now if to execute the produced script, there are 2 dupes,
to execute once more - voila: 3 dupe "candidates"
alt text

Note: if to create the unique constraint by separate T-SQL statement with custom/manual name, as I made at the top, then scripting does not produce anonymous DML and, respectively, their execution does not permit multiplication of "candidates"

Strange candidates for primary keys, aren't they? need to visit a shrink (with multiple identities symptoms)?

Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106
  • 1
    RE: Last sentence - It does not permit dupes. `IGNORE_DUP_KEY = ON` just means dupes won't be inserted but an error won't be raised. – Martin Smith Oct 20 '10 at 14:08

4 Answers4

10

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

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
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).

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
2

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:

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • >>Personally, I think IGNORE_DUP_KEY is pointless and have never used it. it is usefull when imporing large files and you don't want it to fail at the last row if you have a dup – SQLMenace Oct 20 '10 at 14:11
  • @SQLMenace: I'd use a constraint-lite staging table to scrub and validate/de-dupe the data in that case. And report on that if I had to. *Especially* if the file was expected to be dupe-free. – gbn Oct 20 '10 at 14:13
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)
SQLMenace
  • 132,095
  • 25
  • 206
  • 225