SQL Server - Unique index vs Unique constraint - Re. Duplicate values

Question

A unique index ensures that the values in the index key columns are unique. A unique constraint guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).

Questions:

1. Can duplicate values be inserted if we have a unique index on a column and no unique constraint?
2. What about existing duplicates in any on the column - will it allow to create unique index or unique constraint?

Bornu

Answer 1

Can duplicate values be inserted if we have a unique index on a column and no unique constraint?

Generally, duplicate values cannot be inserted and an error is raised when a unique index exists on the column. The exceptions are:

1. Index was created with the IGNORE_DUP_KEY option. No error is raised and the insert is ignored.

2. The non-clustered index is filtered such that the duplicate value does not satisfy the index WHERE clause. The row is inserted but not reflected in the non-clustered index.

What about existing duplicates in any on the column - will it allow to create unique index or unique constraint?

No, with the exception of the filtered index mentioned above.

Furmark

Just to clarify, using IGNORE_DUP_KEY does not allow duplicate values to exist in the index. A unique index never has duplicate values. Instead IGNORE_DUP_KEY simply causes attempts to insert duplicate values to be ignored (with a warning message) instead of causing an error. – Ceasar

Answer 2

One potentially unintuitive scenario that confused me at first: postgres does not treat NULL values as equal. If your table looked like this:

+-------+-------+-------+
|id     |a      |b      |
+-------+-------+-------+
|1      |0      |NULL   |
|2      |0      |NULL   |
+-------+-------+-------+

You could still add a unique index on columns a and b. According to Postgres, row with id 1 and row with id 2 have the same value for column a, but different values for column b

Delve

Same for mysql / mariadb – Spano

It's logical behaviour: NULL is very special value, that even 1+NULL = NULL, bc NULL is more like "undefined" value (not zero!). So if you didn't fill column in two records, this column must be ignored as "nothing to compare for equality"! And yes, it's confusing, because better name could be UNDEF. – Austere

Answer 3

1. Can duplicate values be inserted if we have a unique index on a column and no unique constraint?

No, the values of the columns within the index must create a unique set of data within that index.

2. What about existing duplicates in any on the column - will it allow to create unique index or unique constraint?

No, you cannot create a Unique Index on a table that has duplicate values.

This easiest way to have found this out would be to try (I suggest for things like that doing so, it's a great way of learning):

CREATE TABLE dbo.SomeTable (SomeInt int, AnotherInt int);
GO

INSERT INTO dbo.SomeTable (SomeInt,
                           AnotherInt)
VALUES (1,1),
       (1,2),
       (2,1);
GO
--Create a unique index on a column with duplicate values
CREATE UNIQUE INDEX UQ_SomeInt ON dbo.SomeTable(SomeInt); --fails
GO
--Create a unique index on the 2 columns, as they are unique
CREATE UNIQUE INDEX UQ_Some_AnotherInt ON dbo.SomeTable(SomeInt, AnotherInt); --Succeeds
GO
--Try to insert a duplicate value
INSERT INTO dbo.SomeTable (SomeInt,
                           AnotherInt)
VALUES(2,1); --fails
GO

SELECT *
FROM dbo.SomeTable
GO
DROP TABLE dbo.SomeTable;

Isobath