表约束

这里讲表的一些约束。

一.主键

1). 主键的创建

示例1：在现有表创建主键

ALTER TABLE Production.TransactionHistoryArchive
   ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID);

示例2：在新表创建主键

CREATE TABLE Production.TransactionHistoryArchive1
   (
      TransactionID int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)
   )
;

示例3：在新表创建具有聚集索引的主键

-- Create table to add the clustered index
CREATE TABLE Production.TransactionHistoryArchive1
   (
      CustomerID uniqueidentifier DEFAULT NEWSEQUENTIALID()
      , TransactionID int IDENTITY (1,1) NOT NULL
      , CONSTRAINT PK_TransactionHistoryArchive1_CustomerID PRIMARY KEY NONCLUSTERED (CustomerID)
   )
;

-- Now add the clustered index
CREATE CLUSTERED INDEX CIX_TransactionID ON Production.TransactionHistoryArchive1 (TransactionID);

 2). 主键的修改

修改主键时，必须先删除现有的PRIMARY KEY 约束，然后再用新定义重新创建该约束。

3). 主键的删除

USE AdventureWorks2012;  
GO  
-- Return the name of primary key.  
SELECT name  
FROM sys.key_constraints  
WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'TransactionHistoryArchive';  
GO  
-- Delete the primary key constraint.  
ALTER TABLE Production.TransactionHistoryArchive  
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;   
GO

二.外键

当希望一个表的行和另一个表的行相关联时，可以在两个表之间创建关系。

<1>. 创建外键

1.在新表中创建外键

CREATE TABLE Sales.TempSalesReason 
   (
      TempID int NOT NULL, Name nvarchar(50)
      , CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
      , CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
        REFERENCES Sales.SalesReason (SalesReasonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
   )
;

其中ON DELETE CASCADE 和 ON UPDATE CASCADE的子句用于确保对Sales.SalesReason表的更改自动传播到Sales.TempSalesReason表

2.在现有表中创建外键

下面的示例对列 TempID 创建外键，并引用 AdventureWorks 数据库中 SalesReasonID 表内的列 Sales.SalesReason。

ALTER TABLE Sales.TempSalesReason
   ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
      REFERENCES Sales.SalesReason (SalesReasonID)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

<2>.修改外键

对外键的删除，也是先删除现有的FOREIGN KEY 约束，然后再重新创建该约束。

<3>.删除外键

USE AdventureWorks2012;  
GO  
ALTER TABLE dbo.DocExe   
DROP CONSTRAINT FK_Column_B;   
GO

<4>.查看特定表中关系的外键属性

这里返回数据库表HumanResources.Employee 的所有外键以及属性。

USE AdventureWorks2012;  
GO  
SELECT   
    f.name AS foreign_key_name  
   ,OBJECT_NAME(f.parent_object_id) AS table_name  
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name  
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object  
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name  
   ,is_disabled  
   ,delete_referential_action_desc  
   ,update_referential_action_desc  
FROM sys.foreign_keys AS f  
INNER JOIN sys.foreign_key_columns AS fc   
   ON f.object_id = fc.constraint_object_id   
WHERE f.parent_object_id = OBJECT_ID('HumanResources.Employee');

三. 唯一约束和CHECK 约束

这两种约束是SQL server 表中强制数据完整性的两种类型的约束。

unique约束确保不重复，但可为null.

check 约束 是通过限制一个或多个列可接受的值，check 约束可以强制域完整性。可以通过任何基于逻辑运算符返回true或false的逻辑(布尔)表达式创建check约束。

例如可以通过check约束将salary列中的值范围限制在 $150 到 $300 之间。逻辑表达式为：salary > = 150 and salary <= 300 。

<1>. 创建唯一约束

在新表创建唯一约束

USE AdventureWorks2012;  
GO  
CREATE TABLE Production.TransactionHistoryArchive4  
 (  
   TransactionID int NOT NULL,   
   CONSTRAINT AK_TransactionID UNIQUE(TransactionID)   
);   
GO

在现有表中创建唯一约束

USE AdventureWorks2012;   
GO  
ALTER TABLE Person.Password   
ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);   
GO

<2>.修改唯一约束

也是先删除，再重新创建

<3>.删除唯一约束

-- Return the name of unique constraint.  
SELECT name  
FROM sys.objects  
WHERE type = 'UQ' AND OBJECT_NAME(parent_object_id) = N' DocExc';  
GO  
-- Delete the unique constraint.  
ALTER TABLE dbo.DocExc   
DROP CONSTRAINT UNQ_ColumnB_DocExc;  
GO

四.检查约束

<1>.创建新的check约束

ALTER TABLE dbo.DocExc   
   ADD ColumnD int NULL   
   CONSTRAINT CHK_ColumnD_DocExc   
   CHECK (ColumnD > 10 AND ColumnD < 50);  
GO  
-- Adding values that will pass the check constraint  成功
INSERT INTO dbo.DocExc (ColumnD) VALUES (49);  
GO  
-- Adding values that will fail the check constraint  失败
INSERT INTO dbo.DocExc (ColumnD) VALUES (55);  
GO

<2>.修改check约束

新删除，再重新创建

<3>.删除check 约束

ALTER TABLE dbo.DocExc   
DROP CONSTRAINT CHK_ColumnD_DocExc;  
GO

 

参考网址