Unique constraints of SQL Server

Unique constraint Add rule

1. A unique constraint ensures that a column of data in a table does not have the same value.

2. Similar to a PRIMARY KEY constraint, a unique constraint is also mandatory for uniqueness, but a unique constraint is used for a single column of non-primary keys or a combination of multiple columns, and a table can define multiple unique constraints.

To add a unique constraint using the SSMS database management tool

1, connect the database, select the database, select data Table-"Right click-" Select the design.

2, in the table Design window-"Select the data column to add constraints-" Right click-"Select the index/key."

3. In the Index/Key window-click Add.

4. Select the new index/Key-"in the general window-" type select Unique key.

5. In the General window-click on the column.

6, in the Index column window-"SELECT constraint column First" and then select the Constraint column collation-click OK.

7, in the index/key pop-up box in the general window-"Enter the constraint name in the name-" in the description of the input constraint description-"Other can choose Default-" click Close.

8. Click the Save button (or Ctrl+s)-"Refresh the table to see the results."

To add a unique constraint using a T-SQL script when the table structure already exists

When you add a unique constraint to one or more columns, you first determine whether the constraint you want to add exists, and if it does, delete it and add it directly if it does not exist.

Grammar:

if exists (select * from sysobjects where name= constraint name)
ALTER TABLE database name. [dbo]. Table name drop constraint constraint name;
Go
ALTER TABLE database name. [dbo]. Table name add constraint constraint name unique (column name 1, column name 2);
Go

Example:

if exists (select * from sysobjects where name= ' unique_t_name ')
ALTER TABLE [TESTSS]. [dbo]. [Test1] drop constraint unique_t_name;
Go
ALTER TABLE [TESTSS]. [dbo]. [Test1] Add constraint Unique_t_nameunique (name,sex);
Go

When the table structure does not exist

When a table structure does not exist, it needs to be added in the build Table statement, adding a column of unique indexes and a multiple column unique index syntax.

Grammar:

--Add a UNIQUE constraint when the table structure does not exist
if exists (select * from sysobjects where name= database name. DBO]. Table name and type = ' U ')
drop table database name. [dbo]. Table name;
Go

--when the table structure does not exist
--Build table Syntax declaration
create table  database name. [dbo]. Table name
(
--Field declaration
Span style= "font-size:15px" > column name 1 int identity (+) not NULL,


primary Key Clustered (column name 1 asc) with (Ignore_dup_key=off) on [primary],--primary key index declaration
Constraint unique_name_sex unique (column name 2, column name 3)
) on [primary]

--Field Comment declaration
exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Description 1 ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' row name 1 ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Description 2 ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' row name 2 ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' Description 3 ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' table ', @level1name =n ' name ', @level2type =n ' column ', @level2name =n ' row name 3 ';
Go

Example:

--Add a UNIQUE constraint when the table structure does not exist
if exists (select * from sysobjects where name= ' test1 ' and type = ' U ')
drop table test1;
Go

--when the table structure does not exist
--Build table Syntax declaration
create table test1
(
--Field declaration
id int identity (a) not NULL,
name nvarchar () null,
age int not NULL,
primary key clustered (ID ASC) with (Ignore_dup_key=off) on [primary],--primary key index declaration
constraint unique_name_sex unique (name,age)
) on [ Primary]

--Field Comment declaration
exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' ID primary key ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' id ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' name ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' name ';

exec sys.sp_addextendedproperty @name =n ' ms_description ', @value =n ' age ', @level0type =n ' SCHEMA ',
@level0name =n ' dbo ', @level1type =n ' TABLE ', @level1name =n ' test1 ', @level2type =n ' COLUMN ', @level2name =n ' age ';
Go

Unique index pros and cons

Advantages:

1. The column that contains the uniqueness constraint allows null values.

2. The uniqueness constraint can be placed on one or more columns, and the combination of these columns or columns must be unique.

3. Uniqueness constraint forces a unique index to be created on the specified column. By default, a unique nonclustered index is created, but you can also specify that the index you are creating is a clustered index.

Disadvantages:

1. Creating a UNIQUE constraint creates an index that consumes disk space.

2, when inserting or modifying the value of the constraint column, there will be a validation rule, it will be more trouble.

Unique constraints of SQL Server