We can create Key constraints on a table and there are 2 types on Unique constraints.
Primary Key and Unique Key
following is the comparison and usage of unique key constraints
- UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness
- UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
- Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
- Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL but only NULL will allow.
- A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
- Unique Key constraint creates Non-clustered Index when there is Primary Key exists in a table.
We can create Unique key constraint using T-SQL Command and SSMS
T-SQL:
To create a UNIQUE constraint when you create a table
Create Table dbo.Doc_exec
(ID int primary key,
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
)
To create a UNIQUE constraint on an existing table
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ;
To delete a UNIQUE constraint
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
SSMS:
right click on a table in design mode
Click on Columns and you can select multiple columns in a unique key
Change Unique value to True
3 comments:
hi how if i have an existing table and i want to add new column and set it as unique?
Hi, You can create column using Alter table command and add unique key by setting its option, as mentioned query below
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE ;
If you have data in table then you need to do this in 4 steps.
1- Create new column with NULL
2- Update data in column
3- Alter column as not null
4- Add unique key constraint.
Step 4 command is as follows
USE AdventureWorks2012;
GO
ALTER TABLE Person.Password
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);
GO
Post a Comment