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
The syntax is as follows:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column [ ASC | DESC ] [ ,...n ] )
Creating a UNIQUE Constraints when creating a table
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
Creating UNIQUE Constraints on Existing Tables
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
Unique constraint
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
Unique Constraint: Enforce uniqueness on a column which is not the column used to primarily identify each row.
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>
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
Unique constraints will also allow one NULL, whereas primary keys will not.
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