sql添加约束相关示例

---

A. 添加新列

下面的示例将添加一个允许空值的列，而且没有通过 DEFAULT 定义提供的值。在该新列中，每一行都将有 NULL 值。

SQL

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. 删除列

下面的示例将修改一个表以删除列。

SQL

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. 更改列的数据类型

下面的示例将表中列的数据类型由 INT 改为 DECIMAL。

SQL

CREATE TABLE dbo.doc_exy (column_a INT ) ;
GO
INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO
ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE dbo.doc_exy ;
GO

D. 添加包含约束的列

以下示例将添加一个包含 UNIQUE 约束的新列。

SQL

CREATE TABLE dbo.doc_exc (column_a INT) ;
GO
ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL 
    CONSTRAINT exb_unique UNIQUE ;
GO
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. 在现有列中添加一个未经验证的 CHECK 约束

下面的示例将在表中的现有列中添加一个约束。该列包含一个违反约束的值。因此，将使用 WITH NOCHECK 以避免根据现有行验证该约束，从而允许添加该约束。

SQL

CREATE TABLE dbo.doc_exd ( column_a INT) ;
GO
INSERT INTO dbo.doc_exd VALUES (-1) ;
GO
ALTER TABLE dbo.doc_exd WITH NOCHECK 
ADD CONSTRAINT exd_check CHECK (column_a > 1) ;
GO
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. 在现有列中添加一个 DEFAULT 约束

下面的示例将创建一个包含两列的表，在第一列插入一个值，另一列保持为 NULL。然后在第二列中添加一个 DEFAULT 约束。验证是否已应用了默认值，另一个值是否已插入第一列以及是否已查询表。

SQL

CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

G. 添加多个包含约束的列

下面的示例将添加多个包含随新列定义的约束的列。第一个新列具有 IDENTITY 属性。表中的每一行在标识列中都有新的增量值。

SQL

CREATE TABLE dbo.doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE dbo.doc_exe ADD 

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY, 

-- Add a column that references another column in the same table.
column_c INT NULL  
CONSTRAINT column_c_fk 
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that 
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL 
CONSTRAINT column_d_chk
CHECK 
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. 添加包含默认值的可为空的列

下面的示例将添加一个包含 DEFAULT 定义的可为空的列，并使用 WITH VALUES 为表中的各个现有行提供值。如果没有使用 WITH VALUES，那么每一行的新列中都将具包含 NULL 值。

SQL

USE AdventureWorks2008R2 ; 
GO
CREATE TABLE dbo.doc_exf ( column_a INT) ;
GO
INSERT INTO dbo.doc_exf VALUES (1) ;
GO
ALTER TABLE dbo.doc_exf 
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. 禁用和重新启用约束

下面的示例禁用对数据中接受的薪金进行限制的约束。NOCHECK CONSTRAINT 将与 ALTER TABLE 配合使用来禁用该约束，从而允许执行通常会违反该约束的插入操作。CHECK CONSTRAINT 将重新启用该约束。

SQL

CREATE TABLE dbo.cnst_example 
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1,'Joe Brown',65000);
INSERT INTO dbo.cnst_example VALUES (2,'Mary Smith',75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3,'Pat Jones',105000);

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. 删除约束

下面的示例将从表中删除 UNIQUE 约束。

SQL

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. 在表之间切换分区

以下示例创建一个已分区表，并假定在数据库中已经创建了分区方案 myRangePS1。然后，在 PartitionTable 表的 PARTITION 2 所在的同一文件组中，创建与已分区表结构相同的未分区的表。最后，将PartitionTable 表的PARTITION 2 中的数据切换到NonPartitionTable 表中。

CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
CREATE TABLE NonPartitionTable (col1 int, col2 char(10))
ON test2fg ;
GO
ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ;
GO

L. 禁用和重新启用触发器

下面的示例将使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器，以允许执行通常会违反此触发器的插入操作。然后，使用ENABLE TRIGGER 重新启用触发器。

SQL

CREATE TABLE dbo.trig_example 
(id INT, 
name VARCHAR(12),
salary MONEY) ;
GO
-- Create the trigger.
CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT
AS
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
    print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
END ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1,'Pat Smith',100001) ;
GO
-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ;
GO
-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2,'Chuck Jones',100001) ;
GO
-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ;
GO
-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3,'Mary Booth',100001) ;
GO

M. 创建包含索引选项的 PRIMARY KEY 约束

下面的示例将创建 PRIMARY KEY 约束 PK_TransactionHistoryArchive_TransactionID，并设置FILLFACTOR、ONLINE 和PAD_INDEX 选项。生成的聚集索引将与约束同名。

SQL

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

N. 在 ONLINE 模式下删除 PRIMARY KEY 约束

下面的示例在 ONLINE 选项设置为 ON 的情况下删除 PRIMARY KEY 约束。

SQL

USE AdventureWorks2008R2;
GO
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);
GO

O. 添加和删除 FOREIGN KEY 约束

下面的示例将创建 ContactBackup 表，然后更改此表。首先添加引用 Person 表的 FOREIGN KEY 约束，然后再删除 FOREIGN KEY 约束。

SQL

USE AdventureWorks2008R2 ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Person (BusinessEntityID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P.更改列的大小

下面的示例增加 varchar 列的大小和 decimal 列的精度和小数位数。因为列包含数据，所以只能增加列的大小。此外，请注意：col_a 是在一个唯一索引中定义的。仍然可以增加col_a 的大小，因为其数据类型为varchar 并且该索引不是 PRIMARY KEY 约束的结果。

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy ( col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2));
GO
INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO
-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25);
GO
-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4);
GO
-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999) ;
GO
-- Verify the current column size.
SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

Q. 允许已分区表中的锁升级

下面的示例在已分区表的分区级别启用锁升级。如果该表未分区，则会将锁升级到 TABLE 级别。

ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO);
GO

R. 配置表的更改跟踪

下面的示例启用 AdventureWorks2008R2 数据库中 Person.Person 表的更改跟踪。

Transact-SQL

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING;

下面的示例启用更改跟踪，并启用在进行某项更改期间会进行更新的列的跟踪。

Transact-SQL

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

下面的示例禁用 AdventureWorks2008R2 数据库中 Person.Person 表的更改跟踪。

Transact-SQL

USE AdventureWorks2008R2;
ALTER TABLE Person.Person
DISABLE CHANGE_TRACKING;

S. 修改表以更改压缩

下面的示例更改未分区表的压缩。将会重新生成堆或聚集索引。如果表是一个堆，将重新生成所有非聚集索引。

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);

下面的示例更改已分区表的压缩。REBUILD PARTITION = 1 语法仅仅导致重新生成编号为 1 的分区。

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

使用以下替代语法的相同操作则会导致重新生成表中的所有分区。

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

有关其他数据压缩示例，请参阅创建压缩表和索引。

T. 添加稀疏列

下面的示例演示如何在表 T1 中添加和修改稀疏列。创建表 T1 的代码如下所示。

CREATE TABLE T1
(C1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
C4 int ) ;
GO

若要添加另一个稀疏列 C5，请执行以下语句。

ALTER TABLE T1
ADD C5 char(100) SPARSE NULL ;
GO

若要将 C4 非稀疏列转换为稀疏列，请执行以下语句。

ALTER TABLE T1
ALTER COLUMN C4 ADD SPARSE ;
GO

若要将 C4 稀疏列转换为非稀疏列，请执行以下语句。

ALTER TABLE T1
ALTER COLUMN C4 DROP SPARSE;
GO

U. 添加列集

下面的示例演示如何向表 T2 中添加一列。如果表已包含稀疏列，则不能向该表添加列集。创建表 T2 的代码如下所示。

CREATE TABLE T2
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

下面的三个语句添加名为 CS 的列集，然后将列 C2 和C3 修改为SPARSE。

ALTER TABLE T2
ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;
GO

ALTER TABLE T2
ALTER COLUMN C2 ADD SPARSE ; 
GO

ALTER TABLE T2
ALTER COLUMN C3 ADD SPARSE ;
GO

V. 更改排序规则

下面的示例说明了如何更改列的排序规则。首先，我们创建表 T3 以及默认的用户排序规则：

CREATE TABLE T3
(C1 int PRIMARY KEY,
C2 varchar(50) NULL,
C3 int NULL,
C4 int ) ;
GO

接着，将列 C2 排序规则更改为 Latin1_General_BIN。请注意，需要数据类型，即使排序规则未更改也不例外。

ALTER TABLE T3
ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN 
GO