Questions tagged [unique-constraint]

DDL UNIQUE constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The data contained in the column or columns involved is, therefore, useful to uniquely identify a row in the relevant table.

Declaration

DDL UNIQUE constraints can be created by adding the UNIQUE keyword to the column declaration on the table:

CREATE TABLE foo (
  bar INT UNIQUE
);

CREATE TABLE foo (
  bar INT PRIMARY KEY -- PRIMARY KEY = "UNIQUE NOT NULL"
); 

…as a table-level constraint:

CREATE TABLE foo (
  bar INT NOT NULL,
  CONSTRAINT foo_ck UNIQUE (Name)
);

…or, with a command after table creation:

-- generate a unique name
ALTER TABLE foo
  UNIQUE (bar);

-- explicitly declare constraint with name `foo_bar`
ALTER TABLE foo
  ADD CONSTRAINT foo_bar
  UNIQUE (bar);

Design considerations

If (a) there is a primary key set up for the table in question, and (b) the whole set of columns (i.e., one or more) configured with this type of constraint is also fixed with NOT NULL constraints, the designer is representing an alternate key:

CREATE TABLE foo (
  foo_id INT      NOT NULL,
  bar    DATETIME NOT NULL,
  CONSTRAINT foo_pk PRIMARY KEY (foo_id),
  CONSTRAINT foo_ak UNIQUE      (bar) -- ALTERNATE KEY
);

If, on the contrary, (1) there is no primary declared for the table under consideration, and (2) all the column (or columns) configured with this type of constraint are also fixed with one (or more) NOT NULL constraint(s), the designer is depicting a candidate key:

CREATE TABLE foo (
  foo_id INT      NOT NULL,
  bar    DATETIME NOT NULL,
  CONSTRAINT foo_ck1 UNIQUE (foo_id), -- CANDIDATE KEY
  CONSTRAINT foo_ck2 UNIQUE (bar)     -- CANDIDATE KEY
);

Manipulation

As for data manipulation operations, the columns constrained as UNIQUE can always be used to uniquely identify a row in the table:

-- returns one row
SELECT * FROM foo WHERE bar = $id;

Supporting indexes

The major SQL database management systems (DBMSs) commonly support UNIQUE constraints with the aid of a well-known structure called unique index, but these instruments should not be confused nor conflated, because a UNIQUE constraint is a construct that is part of the logical level of abstraction of a database, while a unique index is part of the physical (or internal) level.

In this respect, the vendors and/or developers of a DBMS may find it convenient to supply mechanisms that are different from a unique index to support a UNIQUE constraint, thus, as long as the uniqueness declared logically is guaranteed, the usage of unique index is not required. In addition, a unique index can be used in software products that are quite different from a SQL DBMS, where the notion of (i) a logical-level UNIQUE constraint, and (ii) the associated ideas of ALTERNATE and CANDIDATE KEYs, are (iii) completely irrelevant.

If your UNIQUE constraint declaration is, precisely, physically implemented with an index, this means you can seek to it, and the DBMS query planner will know that the clause returns 0, or 1 row. Some DBMSs allow you to specify that an index is unique on creation,

-- Valid with PostgreSQL,
CREATE UNIQUE INDEX ON foo (bar);

Terminology and keywords

Sometimes, the expression “unique key” is used when referring to a UNIQUE constraint —e.g., in the official documentation of several SQL platforms—.

Also, a database designer can even set up a UNIQUE constraint by virtue of the UNIQUE KEY combination of keywords in MySQL.

In this regard, both (a) the “unique key” term and (b) the UNIQUE + KEY combination are redundant, since:

  • a KEY constraint —be it PRIMARY, ALTERNATE or CANDIDATE— is, by its very nature, meant to enforce uniqueness of the value (or values) contained in the column (or columns) of the table under consideration; and

  • one can set up a UNIQUE constraint without adding the “KEY” part.

See also

RDBMS-specific documentation:

305 questions
266
votes
7 answers

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id int primary key, code varchar(10) NULL ); go create…
bernd_k
  • 12,001
  • 23
  • 74
  • 109
145
votes
3 answers

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), constraint u_constrainte unique (id_A, id_B,…
Manuel Leduc
  • 1,561
  • 2
  • 10
  • 5
40
votes
5 answers

Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?
35
votes
1 answer

Optimal way to ignore duplicate inserts?

Background This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code: -- Ignores duplicates. INSERT INTO db_table (tbl_column_1, tbl_column_2) VALUES ( SELECT …
Dave Jarvis
  • 815
  • 1
  • 10
  • 24
32
votes
2 answers

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the combination of type and subset to be unique.…
27
votes
2 answers

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable. Below I have created a smaller…
26
votes
3 answers

What is the difference between a primary key and a superkey in a DBMS

What is the difference between a primary key and a superkey in a DBMS? Can a primary key and a superkey both have multiple columns? Is a primary key a subset of a superkey or vice versa?
MikeHil
  • 369
  • 1
  • 3
  • 3
23
votes
3 answers

Multiple primary keys in PostgreSQL

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id); When I…
mostafa
  • 249
  • 1
  • 2
  • 4
20
votes
2 answers

Deferrable unique index in postgres

Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in). Indexes can also be associated with a constraint, as long as: The…
jcristovao
  • 323
  • 1
  • 2
  • 5
19
votes
4 answers

NVARCHAR column as PRIMARY KEY or as UNIQUE column

I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys. I have this table: CREATE TABLE [dbo].[CODES] ( [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL, [CODE_LEVEL] [tinyint] NOT NULL, [CODE]…
VansFannel
  • 1,803
  • 5
  • 22
  • 35
19
votes
4 answers

Differences between "Unique Key" and "Primary Key"

What are the major differences between Unique Key and Primary Key in MySQL?
newuser
  • 661
  • 1
  • 9
  • 20
19
votes
2 answers

Avoid unique violation in atomic transaction

Is possible to create atomic transaction in PostgreSQL? Consider I have table category with these rows: id|name --|--------- 1 |'tablets' 2 |'phones' And column name has unique constraint. If I try: BEGIN; update "category" set name = 'phones'…
Petr Přikryl
  • 333
  • 2
  • 7
17
votes
1 answer

Creating a UNIQUE constraint from a JSON object

Lets take some example table peoples , that got only 2 fields: id and data(json). SELECT data FROM peoples ; {"name": "Adam","pos":"DBA","age":22 } {"name": "Alice","pos":"Security","age":33 } {"name": "Bob","pos":"Manager","age":42 } I want to…
Chenko47
  • 301
  • 1
  • 4
  • 8
15
votes
4 answers

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value is (hopefully) guaranteed to be unique within…
dsaxton
  • 267
  • 2
  • 7
14
votes
1 answer

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now the size of the serial field has become to low, so I…
Staeff
  • 397
  • 1
  • 2
  • 12
1
2 3
20 21