SQL UNIQUE Constraint

The SQL UNIQUE constraint is used to add unique or distinct value in a field and uniquely identifies each row in a database table.

The SQL UNIQUE constraint provide a guarantee for uniqueness for a field or multiple fields in a table.

Difference Between Unique Key and Primary Key

There are many differences between them, which is mentioned below:

1. unique constraint combination of columns

With column_constraint, you can specify a unique key for a single column.

To ensure that values in a column or a group of columns in a table are unique, use the UNIQUE constraint.

A UNIQUE constraint is an integrity constraint that ensures the data in a column, or a group of columns, is unique. It can be a table constraint or a column constraint. In a table, each row is distinct.

No two rows in the table can have the same value for the unique key in order to meet the UNIQUE constraint. A single-column unique key, on the other hand, can have nulls.

The datatype LONG or LONG RAW cannot be used for a unique key column. You can't use the same column or set of columns as a unique key and a primary key, or as a unique key and a cluster key at the same time. You can, however, use the same column or group of columns as a unique key and a foreign key.

Defining Composite Unique Keys

A composite unique key is one that is made up of several columns. Because Oracle constructs an index on a unique key's columns, a composite unique key can have up to 16 columns.

You must use table constraint syntax instead of column_constraint syntax to define a composite unique key.

No two rows in the table can have the same mix of values in the key fields to meet a constraint that specifies a composite unique key. The constraint is immediately satisfied by any record with nulls in all key columns. Two rows with nulls in one or more key columns and the identical mix of values in the other key columns, on the other hand, violate the requirement.

Syntax:

To define a UNIQUE constraint for a column when you create a table, you use this syntax:

CREATE TABLE table_name(
    ...,
    column_name data_type UNIQUE,
    ...
);

In this approach, the UNIQUE keyword is used in the description of the column where the uniqueness rule is to be enforced. MySQL refuses and issues an error if you insert or update a value that causes a duplicate in the column name.

This is a column constraint with UNIQUE. It can also be used to enforce the unique rule for a single column.

To define a UNIQUE constraint for two or more columns, you use the following syntax:

CREATE TABLE table_name(
   ...
   column_name1 column_definition,
   column_name2 column_definition,
   ...,
   UNIQUE(column_name1,column_name2)
);

After the UNIQUE keyword, you place a comma-separated set of columns in parenthesis. MySQL determines uniqueness by combining values from both column column_name1 and column_name2.

If you define a UNIQUE constraint without giving it a name, MySQL will come up with one for you. This syntax is used to define a UNIQUE constraint with a name:

[CONSTRAINT constraint_name]
UNIQUE(column_list)

In this syntax, you specify the name of the UNIQUE constraint after the CONSTRAINT keyword.

Example 1: The following statement creates a table whose data in the email column is unique among the rows in the hr.persons table:

CREATE SCHEMA hr;
GO

CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

In this syntax, you define the UNIQUE constraint as a column constraint. You can also define the UNIQUE constraint as a table constraint, like this:

CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    UNIQUE(email)
);

SQL Server constructs a UNIQUE index behind the scenes to guarantee the uniqueness of data stored in the columns that partake in the UNIQUE constraint. If you try to insert a duplicate record, SQL Server rejects the modification and displays an error message stating that the UNIQUE constraint was breached.

The following statement inserts a new row into the hr.persons table:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('John','Doe','j.doe@bike.stores');

The statement works as expected. However, the following statement fails due to the duplicate email:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('Jane','Doe','j.doe@bike.stores');

SQL Server issued the following error message:

If you don’t specify a separate name for the UNIQUE constraint, SQL Server will automatically generate a name for it. In this example, the constraint name is UQ__persons__AB6E616417240E4E, which is not quite readable.

To assign a particular name to a UNIQUE constraint, you use the CONSTRAINT keyword as follows:

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE(email)
);

The following are some of the advantages of naming a UNIQUE constraint:

The error message is easier to classify.

When you want to change a constraint, you can relate to its name.

Example 2: When you want to ensure the uniqueness of a column or a group of columns that are not the primary key columns, you must use the UNIQUE constraint rather than the PRIMARY KEY constraint.

UNIQUE constraints, unlike PRIMARY KEY constraints, allow NULL. Furthermore, because UNIQUE constraints consider NULL as a regular value, only one NULL per column is allowed.

The following statement inserts a row whose value in the email column is NULL:

INSERT INTO hr.persons(first_name, last_name)
VALUES('John','Smith');

Now, if you try to insert one more NULL into the email column, you will get an error:

INSERT INTO hr.persons(first_name, last_name)
VALUES('Lily','Bush');

Output:

2. UNIQUE constraints for a group of columns

Syntax:

You construct a table constraint with column names separated by commas to define a UNIQUE constraint for a group of columns, as shown below:

CREATE TABLE table_name (
    key_column data_type PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...,
    UNIQUE (column1,column2)
);

Example 1: The following example creates a UNIQUE constraint that consists of two columns person_id and skill_id:

CREATE TABLE hr.person_skills (
    id INT IDENTITY PRIMARY KEY,
    person_id int,
    skill_id int,
    updated_at DATETIME,
    UNIQUE (person_id, skill_id)
);

3. Add UNIQUE constraints to existing columns

When you add a UNIQUE constraint to an existing column or a group of columns in a database, SQL Server checks the existing data in these columns to make sure that all values are unique. If SQL Server discovers duplicate data, it generates an error and does not apply the UNIQUE constraint.

Syntax:

The following shows the syntax of adding a UNIQUE constraint to a table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2,...);

Example: Suppose you have the following hr.persons table:

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
);

The following statement adds a UNIQUE constraint to the email column:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_email UNIQUE(email);

Similarly, the following statement adds a UNIQUE constraint to the phone column:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_phone UNIQUE(phone); 

Delete UNIQUE constraints

To define a UNIQUE constraint, you use the ALTER TABLE DROP CONSTRAINT statement as follows:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

The following statement removes the unique_phone constraint from the hr.person table:

ALTER TABLE hr.persons
DROP CONSTRAINT unique_phone;

1. Create unique constraint

The Unique Constraint assures that all values are unique, and that no duplicate values are put in a table column.

CREATE or ALTER TABLE SQL can be used to generate unique constraints. In the CREATE TABLE statement, place unique constraints after column declarations.

Syntax:

The syntax for creating a unique constraint using a CREATE TABLE statement in SQL Server is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

• table_name - The name of the table that you wish to create.
• column1, column2 - The columns that you wish to create in the table.
• constraint_name - The name of the unique constraint.
• uc_col1, uc_col2, ... uc_col_n - The columns that make up the unique constraint.

Example 1: Let's look at an example of how to create a unique constraint in SQL Server using the CREATE TABLE statement.

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  employee_number INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT employees_unique UNIQUE (employee_number)
);

We've developed a unique constraint on the employees database named employees unique in this instance. There is only one field in it, which is the employee number.

As shown in the instance below, we can also define a unique constraint with several fields:

CREATE TABLE employees
( employee_id INT PRIMARY KEY,
  employee_number INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY,
  CONSTRAINT employees_unique UNIQUE (last_name, first_name)
);

2. Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in SQL Server is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

• table_name - The name of the table that you wish to create.
• constraint_name - The name of the unique constraint.
• column1, column2, ... column_n - The columns that make up the unique constraint.

Example: Let's have a look at how to use the ALTER TABLE statement in SQL Server to add a unique constraint to an existing table.

ALTER TABLE employees
ADD CONSTRAINT employees_unique UNIQUE (employee_number);

In this example, we've created a unique constraint on the existing employees table called employees_unique. It consists of the field called employee_number.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employee_name_unique UNIQUE (last_name, first_name);

The Unique Constraint feature allows you to ensure the uniqueness of records in your database table while also maintaining database integrity. The MySQL DROP INDEX command is used to remove a unique constraint from a MySQL table. In MySQL, it can also be used to delete the unique key.

A table's unique constraint is a set of one or more fields that describe each entry in the table in a unique way. These fields can have null values as long as the field combinations are distinct.

Syntax:

The syntax for dropping a unique constraint in SQL Server is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

• table_name - The name of the table that has to be changed. This is the table from which you want to eliminate the unique constraint.
• constraint_name - The name of the constraint that has to be removed.

Here’s the syntax of MySQL DROP INDEX command

DROP INDEX constraint_name ON table_name
[algorithm_option | lock_option];

In the above query, constraint_name is the name of constraint, and table_name is your database table name.

Example 1: Let's look at an example of how to remove a unique constraint from a table in SQL Server.

ALTER TABLE employees
DROP CONSTRAINT employees_unique;

In this example, we're dropping a unique constraint on the employees table called employees_unique.

Example 2: You want to remove a unique constraint from a column or columns in a database table.

The unique constraint in the table product should be removed from the column name. The example below demonstrates how to do that.

ALTER TABLE product
DROP CONSTRAINT UQ_product_name

The ALTER TABLE clause is used to change the table product in this example. After this clause, you must put the table's name (in this case, product) and the phrase DROP CONSTRAINT, which must contain the name of the unique constraint you want to remove.

The name of the constraint can be found in your database's meta data. Naming constraints are handled differently by each database engine. Select data from the sys.key_constraints table in a database to determine the name of the constraint in SQL Server. Select the conname column from the pg_constraint table in PostgreSQL. The Oracle server stores this information in the user_constraints table, while MySQL uses the CONSTRAINT NAME column in information schema.TABLE_CONSTRAINTS to get names.

Example 3: MySQL DROP UNIQUE CONSTRAINT example

Let’s say you have the following table with unique constraint.

create table orders(order_id int primary key,
     order_date date,
     product varchar(255),
     sale int,
     constraint name unique (product)
     );

Here’s the SQL query to drop unique constraint name from table orders.

DROP INDEX name ON orders;

Hence, null values are not regarded as duplicates of other null values, so this restriction does not permit duplicate values. We can establish a unique constraint that allows multiple nulls using the filtered unique index.

Syntax:

Here is the syntax of the Unique Index with filter to allow multiple nulls

CREATE UNIQUE INDEX [Index Name]
ON [Table Name]([Column Name])
WHERE [Column Name] IS NOT NULL;

Example 1: In this scenario, I've built a table and a NOT NULL constraint. This filter condition ensures that the unique constraint ignores all NULLs and only applies uniqueness to non-NULL items. Then I insert numerous records in the unique column with null values. You'll see that the unique constraint field allows you to enter several null values. You will get the "Cannot insert duplicate key..." error if you try to insert another duplicate value.

/* Create a table */
CREATE TABLE dbo.MTB_Table_C (EmpID INT NOT NULL, EmpCode VARCHAR(10) NULL);
 
/* Create a filtered index on the colum EmpCode */
CREATE UNIQUE INDEX IX_EmpCode_NotNull
    ON dbo.MTB_Table_C(EmpCode) WHERE EmpCode IS NOT NULL;
 
/* Insert values with multiple nulls */
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (1, 'A00A01')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (2, NULL)
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (3, 'A00A03')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (4, NULL)
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (5, 'A00A05')
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (6, NULL)

You can put any amount of NULLs in the unique column, as seen in the example above. Try inserting a duplicate value now. You will receive the following error.

Example 2: A practical example

This table/design goal is a good example of a situation where you might need a null agnostic unique constraint.

Customers Table
CustID	integer, Primary Key, 
Identity
SSN	varChar(11)
CustName	varChar(100)

Validation Requirement: Although not every client is required to supply their SSN for privacy reasons, the table should reject any duplicate entries between customers who have this field filled in.

The Problem

The main problem with using nulls in a unique index is that it's unclear what the phrase NULL=NULL should evaluate to. NULL is used in database systems to represent an unknown or undefined value. Is there a difference between one unknown and another? That is a point of contention.

By providing the SET ANSI NULLS option and setting it to OFF, Microsoft looks to take the more rational position that you can't declare whether two unknown values are similar, and hence evaluates NULL=NULL to NULL by default.

The SQL-92 standard, on the other hand, takes the opposite stance, stating that both NULL=NULL and NULL<>NULL should evaluate to false. This is further formalised in their recommended way to handling NULL values when implementing unique constraints.

If UNIQUE constraints aren't combined with NOT NULL constraints, the columns can have any amount of NULL values.

Unfortunately, Microsoft's implementation of unique constraints/indexes in SQL Server does not fully conform to this standard. When checking for duplicates, SQL Server treats Null as a discrete number, regardless of the ANSI_NULLS parameter. That is, each unique column can only have one Null value.

Surprisingly, this implementation appears to infer that NULL=NULL evaluates to True. Furthermore, it's a little perplexing that Microsoft chose to include the ability to accept NULL values in unique columns, which is an optional feature in the standard, but overlooked the strict need to not recognise multiple NULL values as duplicates.

In the end, creating a null agnostic unique constraint on our Customers table will be more difficult than it would be with PostgreSQL or MySQL, which handle duplication checking on NULLS in compliance with SQL-92.

Solution 1: Filtered Indexes

The first option is undoubtedly the cleanest and fastest, but it necessitates the use of filtered indexes, which were not offered until SQL2008.

Using the new If syntax of the CREATE INDEX command, you simply create a unique index on the field that has to be unique, and indicate that you only want to include rows in the index where that field is not null.

Here’s how it would work using the Customers table example.

CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptNulls]
ON [Customers] (SSN)
WHERE [SSN] IS NOT NULL

Any values inserted or updated into the SSN column will be checked for duplicates unless they are NULL, because NULL values are excluded from the index.

Solution 2: Constraint on computed column

It isn't nearly as simple if you're running a pre-2008 version of SQL Server, but there is an other technique that works. It should work quite well, however it comes with the drawback of adding an extra computed column to the database that no one else will understand.

We use a unique constraint on a calculated field to make the null values look different. It accomplishes this by evaluating to the value you want to check for uniqueness, unless that value is NULL, in which case it evaluates to a unique value for that row that will not trigger the duplication check. The most straightforward approach is to base the computed field on an identity field.

Here is the implementation of this approach, again using the Customers table.

CREATE TABLE [Customers] (
  [CustId] INT IDENTITY(1,1) NOT NULL,
  [SSN] varchar(11) NULL,
  [CustName] varchar(100) NOT NULL,
  [SSN_UNQ]  AS (CASE WHEN [SSN] IS NULL
     THEN cast([CustID] as varchar(12))
     ELSE '~' + [SSN] END
),
CONSTRAINT [UQSSN_Unique] UNIQUE  ([SSN_UNQ])
)

As you can see, the computed field will contain the SSN unless the field is NULL, in which case the CustID will be used.

The ~ character is appended to the SSN to cover the unlikely event that a CustID matches an existing SSN.

Note: If you're using SQL 2005 or later, labelling the calculated column PERSISTED may help you get some extra performance out of this.

Solution 3: UDF Check Constraint (Not recommended)

I'm including this for completeness' sake, but with the disclaimer that using this strategy on any table with a lot of INSERT or UPDATE traffic could cause serious performance concerns.

A check constraint is often used for simple validation using only the values from the row being inserted or changed, but there is a loophole that allows you to access other rows in the table by using a User Defined function in the check constraint.

To implement this strategy, simply construct a UDF that checks the table for duplicates for a specific value, ignoring the current row being changed, of course.

There may be times when this is preferable to the computed column constraint method, but I'm not aware of any. Use this method at your own risk. [Implementation omitted to discourage its use.]

CREATE UNIQUE NONCLUSTERED  INDEX [UniqueExceptNulls]
ON [Customers] (SSN)
WHERE [SSN] IS

/* Try to insert a duplicate value in EmpCode */
INSERT INTO dbo.MTB_Table_C (EmpID, EmpCode) Values (5, 'A00A05')

Output:

To make this mandatory, use the UNIQUE constraint. SQL unique constraint on multiple columns ensures that there will be no second row in a SQL Server table that shares the same data on specified columns as another table row.

Syntax:

To create a UNIQUE constraint to a group of columns using the following syntax:

CREATE TABLE table (
    c1 data_type,
    c2 data_type,
    c3 data_type,
    UNIQUE (c2, c3)
);

The value combination in column c2 and c3 will be unique throughout the table. It is not necessary for the value of column c2 or c3 to be unique.

We use a comma-separated columns list within parenthesis after the UNIQUE keyword to construct a UNIQUE on multiple columns.

Example 1: A user's email address must also be unique in order for the system to send any notifications to the correct user. It also stops one user from generating several accounts in the system using the same email address.

SQL involves establishing a UNIQUE constraint for many columns to make things easier, as shown below:

CREATE TABLE users (
  userid int(11) NOT NULL AUTO_INCREMENT,
  username varchar(25) DEFAULT NULL,
  password varchar(25) DEFAULT NULL,
  firstname varchar(45) NOT NULL,
  lastname varchar(45) NOT NULL,
  created date DEFAULT NULL,
  email varchar(255) DEFAULT NULL,
  PRIMARY KEY (userid), 
  CONSTRAINT user_info UNIQUE(username,email)
)

This ensures that the username and email value pair in the user's table are unique.

The UNIQUE constraints are identified by the keyword CONSTRAINT. If we like, we can utilize this name to eliminate the UNIQUE restriction afterward.

Example 2: If you have a system where you manage and track reservations of meeting rooms, you will want to prevent reservations of a resource for the same time slot for different requestors. As a database desing solution, this case a unique constraint that will keep your database table data consistent. I assume that in your application's database model you have a reservations time table. In that case an SQL Unique Constraint multiple columns are defined in the column list will throw exception while adding a second record with same that on defined columns.

Alter Table tablename Add Constraint constraintname UNIQUE (column1, ..., columnn)

Let's look at a few examples of applying unique constraints to numerous columns in a SQL Server database table and seeing if the unique constraint is violated when the same column data is inserted in two different rows.

Unique Constraint Example:

Create Table Reservation (
 Id int identity(1,1),
 Resource varchar(10),
 TimeSlot varchar(20),
 Requestor varchar(50)
)

If the SQL developer or SQL Server database administrator did not set a unique constraint on the columns Resource and TimeSlot of the aforementioned database table, it will be possible to book the same meeting room for the same time period. Here are two database insert statements to test the circumstance where the SQL unique constraint is not present.

INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'SQL Development Team'
INSERT INTO Reservation SELECT 'Meeting101', '10:00-11:00', 'SQL Server Administration Team'

This is an example when our database design requirements have been broken.

Let's stick with the same scenario. Using the Transact-SQL command below, I'll add SQL Server unique constraints to several columns (Resource and TimeSlot columns).

Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)

Despite the fact that the add unique constraint syntax is valid and the multiple column list contains valid sql table columns, SQL Engine will throw the accompanying exception.

In fact, the error notice above is self-explanatory. Because the existing table data contains non-unique key values, a unique constraint for those key columns cannot be implemented. So, before adding a unique constraint to a SQL Server table, you should delete or replace duplicate key column values in some way. The key column values that break the unique requirement are also returned to the user who ran the statement in this example (I ran SQL command within Visual Studio 2012).

Let's see the rows that prevent unique constraint creation and violate our unique constraint

Select * From Reservation Where Resource = 'Meeting101' And TimeSlot ='10:00-11:00'

duplicate key column values in sample sql database table

Meeting102 will be the new meeting room for the SQL Server Administrators team. This will prevent a unique constraint violation error when numerous columns are added to a table.

Update Reservation Set Resource = 'Meeting102'
Where Requestor = 'SQL Server Administration Team'

After updating table data to provide a unique initial situation before we add unique constraint on multiple columns of table, we are ready to execute the Alter Table table_name Add Constraint constraint_name Unique (key_column_list) script once more.

Alter Table Reservation Add Constraint sqlUniqueConstraint UNIQUE (Resource, TimeSlot)

This time SQL Server database engine will successfully add new unique constraint covering given multiple columns list on target table.

Developers cannot insert any record to the table containing duplicate key with existing table data after a unique constraint has been created. Let's look at what occurs when a SQL Server table's unique constraint is violated. Execute the following Insert instructions, taking note that the resource and time-slot columns contain duplicate keys once again.

INSERT INTO Reservation SELECT 'Meeting102', '11:00-12:00', 'SQL Development Team'
INSERT INTO Reservation SELECT 'Meeting102', '11:00-12:00', '.NET Programmers'

Despite the fact that the first entry is successfully placed into the database table, the second command will fail due to duplicate values.

The UNIQUE KEY constraint'sqlUniqueConstraint' has been violated. In object 'dbo.Reservation,' a duplicate key cannot be inserted. The statement has come to an end.

Example 3: Adding unique constraint using a unique index

You might want to add a unique constraint to an existing column or set of columns on occasion. Take a look at the following illustration.

First, suppose you have a table named equipment:

CREATE TABLE equipment (
	id SERIAL PRIMARY KEY,
	name VARCHAR (50) NOT NULL,
	equip_id VARCHAR (16) NOT NULL
);

Second, create a unique index based on the equip_id column.

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id 
ON equipment (equip_id);

Third, add a unique constraint to the equipment table using the equipment_equip_id index.

ALTER TABLE equipment 
ADD CONSTRAINT unique_equip_id 
UNIQUE USING INDEX equipment_equip_id;

It's worth noting that the ALTER TABLE command grants the table an exclusive lock. It will wait for all pending transactions to complete before altering the table if you have any. As a result, you should use the following query to look for the current pending transactions in the pg_stat_activity table:

SELECT datid,
  datename,
  usename,
state
FROM
pg_stat_activity;

The state column with the value idle in transaction should be found in the outcome. These are the deals that haven't been completed yet.

A group of one or more table fields/columns that uniquely identify a record in a database table is referred to as a unique key. It's similar to a primary key, except that it can only allow one null value. Unique key constraints should be named with a "UQ_" prefix, followed by the table name, and finally the column name.

Each record in a database table is uniquely identified by a Unique Constraint. A unique constraint prevents two records from having the same values in the same column. A unique constraint has the following naming convention:

Syntax:

The unique constraint should use the syntax:

“UQ_<TableName>_<ColumnName(s)>.

UQ_Employees_EmailID
UQ_Items_Code

• A "UQ_" prefix should be added to each Unique Constraint name.
• Both TableName and ColumnName should start with a capital letter.
• To indicate plural, the tablename should conclude with the letter "s" (or "es").
• Each column name is separated by an underscore (_) if the unique key comprises more than one column.

Example:

UQ_Employees_EmpId_EmployeeName
UQ_OrderDetails_OrderNumber

A SQL query with the ALTER clause can be used to add a unique key constraint to a column(s) in an MS SQL Server database.

Syntax:

In SQL Server, the syntax for creating a unique constraint with an ALTER TABLE query is as follows:

Adding unique key constraint to a column.

ALTER TABLE <table_name>

ADD UNIQUE (<column_name>);

Adding unique key constraint to multiple columns

ALTER TABLE <table_name>

ADD  CONSTRAINT <identifier_name> UNIQUE (<column_name1>, <column_name2>,…);

Example 1:

ALTER TABLE demo
ADD UNIQUE (PHONE);

Let's insert a row into the table.

INSERT INTO demo
VALUES ('GeeksforGeeks','000000001'); --error

Because the text '000000001' already exists in the phone column, which now has a unique key constraint, the above query returns an error.

Example 2: Let's have a look at how to use the ALTER TABLE statement in SQL Server to add a unique constraint to an existing table.

ALTER TABLE employees
ADD CONSTRAINT employees_unique UNIQUE (employee_number);

In this example, we've created a unique constraint on the existing employees table called employees_unique. It consists of the field called employee_number.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employee_name_unique UNIQUE (last_name, first_name);

Add unique constraint

A single field or a set of data that defines a record uniquely is referred to as a unique constraint. Certain fields can have null values as long as the value combination is unique.

In SQL Server, the syntax for creating a unique constraint with an ALTER TABLE query is as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

• table_name - The name of the table that has to be changed. This is the table to which a unique constraint should be applied.
• constraint_name - The name of the unique constraint.
• column1, column2, ... column_n - The columns that make up the unique constraint.

Example 1: Let's look at an example of how to add a unique constraint to an existing table in SQL Server using the ALTER TABLE statement :

ALTER TABLE employees
ADD CONSTRAINT employees_unique UNIQUE (employee_number);

In this example, we've added the employees unique constraint to the existing employees table. It consists of the employeenumber_field.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE employees
ADD CONSTRAINT employee_name_unique UNIQUE (last_name, first_name);

Example 2: In the table product, we'd like to make the column name unique. One method is presented in the query below.

CREATE TABLE product (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) UNIQUE,
producer VARCHAR(100),
category VARCHAR(100)
);

Discussion: In this instance, the phrase UNIQUE was added to the end of the defining column (name VARCHAR(100) UNIQUE) to make a given column (the column name) unique. This new table (in our example: product) will have a column that contains unique values in rows (in our example: name).

Example: Last week I was asked by a colleague, Sander (Blog | @SanderKooij), “What’s the easiest way of adding a unique identifier (or row ID) to a result set?”. That’s an interesting question. There are several ways to do this. For example, you could insert the resultset into a temp table (physical or memory temp table), with an extra column (Identity). But what if you want to do this in a Select statement itself? Here’s a solution.

If you use the scripts I’ve added to this post (check Code Samples), you’ve created a table with country abbreviation codes. This table contains the data as shown below:

If you select the data, and want to add a record ID to your result set, you can use the following script:

SELECT
    DefaultAbbreviation,
    CountryProper,
    CountryNumber,
    ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber
FROM CountryAbbreviationCodes

The statement above uses the built-in function ROW_NUMBER to produce a row number for the result set. The ORDER BY clause in the functions tells SQL Server which column(s) to use to generate a row number.In this situation, the acronym column is used to sort the results.

But what if you want to pick data based on a specific row number? You get the following problem when you try to utilise the ROW NUMBER function in the where clause:

“Windowed functions can only appear in the SELECT or ORDER BY clauses.”

So you need another solution. From SQL Server 2005 onwards we can use a Common Table Expression (CTE). With a CTE you can use a select statement as a table. So if you want to return the rows that are numbered 50 through 60, you can use the following query:

WITH OrderedCountries AS
(
    SELECT
        DefaultAbbreviation,
        CountryProper,
        CountryNumber,
        ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber
    FROM CountryAbbreviationCodes
)
 
SELECT
    DefaultAbbreviation,
    CountryProper,
    CountryNumber,
    RowNumber
FROM OrderedCountries
WHERE 1 = 1
AND RowNumber BETWEEN 50 AND 60

The 10 rows we wanted are the outcome of this statement. The ROW_NUMBER function can be used with a CTE to return specified rows from a table.

You can also eliminate duplicate records from your tables using the ROW_NUMBER methods.

Main Article :- Sql difference between UNIQUE KEY and PRIMARY KEY Constraints

The PRIMARY Key and UNIQUE Key constraints, both are similar and enforce uniqueness of the column on which they are defined.

1.Primary Key

• It was once used as a table centrepiece.
• NULL values are not permitted in the primary key.
• When a primary key and data in a database table are physically ordered in the clustered index's sequence, a clustered index is formed automatically.
• There can only be one primary key per table.
• As a Foreign Key, the primary key can be linked to other tables.
• With the aid of the Auto Increment field, we may produce ID automatically.
• Auto Increment value is supported by the primary key.
• On temporary tables and table variables, we can define primary key constraints.
• We are unable to delete the primary key value from the parent database that is used as a foreign key in the child table. To delete a primary key value, we must first delete it from the child table.

2.Unique Key

• Involves determining each row's unique identifier, which isn't the primary key.
• NULL values are allowed for unique keys.
• A table can have numerous unique keys.
• Unique key creates a non-clustered index by default.
• As a Foreign Key, Unique Constraint cannot be linked to another table.
• There can be multiple unique keys specified in the table.