Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Unique constraint

Unique constraint:

Unique constraints are rules that enforce uniqueness or prevent duplication on column data.

Points to remember:

1. UNIQUE constraints allow null values, only one value is allowed per unique column.
2.  A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
3.  If you are trying to add unique key for existing column which has duplicate data, the Database Engine returns an error and does not add the constraint.
4.  For unique constraint column duplicates values are not allowed to add.
5.  The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint

Create a unique constraint using SQL Server management studio:

1.                  In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.


2.                  On the Table Designer menu, click Indexes/Keys.


3.                  In the Indexes/Keys dialog box, click Add.


4.                  In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.

 


                                                                                                                                                  Figure 4

 

5.                  On the File menu, click Save table name,Check created unique constraint.


 

To modify:

1.   In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design, then click on Indexes/Keys from table designer menu.
2.  In the Indexes/Keys dialog box, under Selected Primary/Unique Key or Index, select the constraint you want.
3.  Complete an action from the following:


Change the columns that the constraint is associated with:
In the grid, click Columns and then click the ellipses (…) to the right of the property.

Rename the constraint:

Type a new name in the Name box. Make sure that your new name does not duplicate a name in the Selected Primary/Unique Key or Index list.

Set the clustered option:
In the grid select the Create As Clustered and from the dropdown choose Yes to create a clustered index and No to create a non-clustered one. Only one clustered index can exist per table. If a clustered index already exists, you must first clear this setting on that index.

Define a fill factor: Expand the Fill Specification category and type an integer from 0 to 100 in the Fill factor box.

 

To Delete:

1.  In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design, then click on Indexes/Keys from table designer menu
2.  In the Indexes/Keys dialog box, select the unique key in the Selected Primary/Unique Key and Index list.
3.  Click Delete.

Unique constraint using T-SQL:

How to Add UNIQUE Constraint on CREATE TABLE?

Column Level:

USE [WebShop]
GO
CREATE TABLE [dbo].[User](
     [ID] [int] IDENTITY(1,1) NOT NULL Primary Key,
     [ManagerId] [int] NULL,
     [UserType] [tinyint] NULL,
     [LoginName] [nvarchar](50) NULL Unique,
     [Password] [nvarchar](8) NULL,
     [CreatedDate] [datetime] NULL,
     [UpdatedDate] [datetime] NULL,
     [DeletedDate] [datetime] NULL
     )
 
GO

Table Level:

USE [WebShop]
GO
CREATE TABLE [dbo].[User](
     [ID] [int] IDENTITY(1,1) NOT NULL Primary Key,
     [ManagerId] [int] NULL,
     [UserType] [tinyint] NULL,
     [LoginName] [nvarchar](50) NULL,
     [Password] [nvarchar](8) NULL,
     [CreatedDate] [datetime] NULL,
     [UpdatedDate] [datetime] NULL,
     [DeletedDate] [datetime] NULL,
     CONSTRAINT UK_LoginName UNIQUE ([LoginName]),
     CONSTRAINT UK_NameManager UNIQUE ([ManagerId],[LoginName])
     )
 
GO

 

How to add a UNIQUE Constraint on ALTER TABLE?

Single Unique Key:

ALTER TABLE [User]
ADD CONSTRAINT UK_LoginName UNIQUE(LoginName)

Multiple uniqe key:

ALTER TABLE [User]
ADD CONSTRAINT UK_NameManager UNIQUE(ManagerId,LoginName)

 

How to DROP a UNIQUE Constraint?

ALTER TABLE [User]
DROP CONSTRAINT UK_LoginName

 

Invalid entry,please enter valid data.

Loading