 |
|
SQL Server Tips by Burleson |
UNIQUE
The UNIQUE constraint determines that no duplicate values are
allowed in a column. If a UNIQUE constraint is applied to a
composite key, NULL values are accepted and considered the same way
as any other values:
A UNIQUE constraint can be clustered as well.
Example: Creating a table with one UNIQUE constraint. Table
item_orders2 will have a UNIQUE constraint on column order_id:
CREATE TABLE [dbo].[item_orders2]
(
[order_id] [int] CONSTRAINT item_orders2_UNIQUE UNIQUE ,
[item_id] [int] NOT NULL ,
[data] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
Example: Creating a table with one UNIQUE constraint to two columns
from an existing table. Table item_orders3 will have a UNIQUE on
columns order_id and item_id:
CREATE TABLE [dbo].[item_orders3]
(
[order_id] [int] ,
[item_id] [int] NOT NULL ,
[data] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT item_orders3_UNIQUE UNIQUE ([order_id], [item_id])
) ON [PRIMARY]
Example: Adding one UNIQUE constraint to an existing table:
ALTER TABLE [dbo].[item_orders2]
ADD CONSTRAINT
UNIQUE_Table2 UNIQUE CLUSTERED
(
order_id
) ON [PRIMARY]
Example: Adding one UNIQUE constraint to two columns from an
existing table:
ALTER TABLE [dbo].[item_orders3] ADD CONSTRAINT
UNIQUE_Table3 UNIQUE CLUSTERED
(
order_id,
item_id
) ON [PRIMARY]
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
