в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:59Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Constraints/Unique - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия