8

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?
xandermonkey
  • 4,054
  • 2
  • 31
  • 53
variable
  • 8,262
  • 9
  • 95
  • 215

3 Answers3

5

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • 4
    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. – John Rees Sep 17 '19 at 20:24
1
  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.

  1. 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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

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

Pearce Merritt
  • 1