SQL Server/T-SQL Tutorial/Constraints/Unique
Содержание
- 1 Adding a UNIQUE Constraint to an Existing Table
- 2 Creating a UNIQUE Constraints when creating a table
- 3 Creating UNIQUE Constraints on Existing Tables
- 4 Unique constraint
- 5 Unique Constraint: Enforce uniqueness on a column which is not the column used to primarily identify each row.
- 6 Unique constraints can be created when initially creating the table or added after.
- 7 Unique constraints will also allow one NULL, whereas primary keys will not.
Adding a UNIQUE Constraint to an Existing Table
<source lang="sql">
The syntax is as follows: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column [ ASC | DESC ] [ ,...n ] )</source>
Creating a UNIQUE Constraints when creating a table
<source lang="sql">
5> 6> CREATE TABLE Shippers 7> ( 8> ShipperID int IDENTITY NOT NULL 9> PRIMARY KEY, 10> ShipperName varchar(30) NOT NULL, 11> Address varchar(30) NOT NULL, 12> City varchar(25) NOT NULL, 13> State char(2) NOT NULL, 14> Zip varchar(10) NOT NULL, 15> PhoneNo varchar(14) NOT NULL 16> UNIQUE 17> ) 18> GO 1> 2> drop table Shippers; 3> GO</source>
Creating UNIQUE Constraints on Existing Tables
<source lang="sql">
4> 5> 6> CREATE TABLE Employees 7> ( 8> EmployeeID int IDENTITY NOT NULL, 9> FirstName varchar(25) NOT NULL, 10> MiddleInitial char(1) NULL, 11> LastName varchar(25) NOT NULL, 12> Title varchar(25) NOT NULL, 13> SSN varchar(11) NOT NULL, 14> Salary money NOT NULL, 15> PriorSalary money NOT NULL, 16> LastRaise AS Salary - PriorSalary, 17> HireDate smalldatetime NOT NULL, 18> TerminationDate smalldatetime NULL, 19> ManagerEmpID int NOT NULL, 20> Department varchar(25) NOT NULL 21> ) 22> GO 1> 2> ALTER TABLE Employees 3> ADD CONSTRAINT AK_EmployeeSSN 4> UNIQUE (SSN) 5> GO 1> 2> drop table Employees; 3> GO</source>
Unique constraint
<source lang="sql">
2> CREATE TABLE customer_location 3> ( 4> cust_id int NOT NULL, 5> cust_location_number int NOT NULL, 6> CONSTRAINT customer_location_unique UNIQUE 7> (cust_id, cust_location_number) 8> ) 9> GO 1> 2> EXEC sp_helpconstraint customer_location 3> GO Object Name
customer_location
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
UNIQUE (non-clustered) customer_location_unique
(n/a) (n/a) (n/a) (n/a) cust_id, cust_location_number
No foreign keys reference table "customer_location", or you do not have permissions on referencing tables.
1>
2> drop table customer_location;
3> GO</source>
Unique Constraint: Enforce uniqueness on a column which is not the column used to primarily identify each row.
<source lang="sql">
5> CREATE TABLE Employee ( 6> EmployeeID Int IDENTITY(1,1) NOT NULL 7> , LastName nVarChar(50) NOT NULL 8> , FirstName nVarChar(50) NOT NULL 9> , SSN Char(9) NOT NULL) 10> GO 1> 2> 3> drop table Employee; 4> GO 1></source>
Unique constraints can be created when initially creating the table or added after.
<source lang="sql">
7> CREATE TABLE Employee ( 8> EmployeeID Int IDENTITY(1,1) NOT NULL 9> , LastName nVarChar(50) NOT NULL 10> , FirstName nVarChar(50) NOT NULL 11> , SSN Char(9) NOT NULL 12> , CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN)) 13> GO 1> ALTER TABLE Employee 2> ADD CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN) 3> GO 1> drop table Employee; 2> GO</source>
Unique constraints will also allow one NULL, whereas primary keys will not.
<source lang="sql">
Unique constraints can be created when initially creating the table or added after. 7> CREATE TABLE Employee ( 8> EmployeeID Int IDENTITY(1,1) NOT NULL 9> , LastName nVarChar(50) NOT NULL 10> , FirstName nVarChar(50) NOT NULL 11> , SSN Char(9) NOT NULL 12> , CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN)) 13> GO 1> ALTER TABLE Employee 2> ADD CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN) 3> GO 1> drop table Employee; 2> GO</source>
