这里讲表的一些约束。
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');
这两种约束是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