Sql Server系列：鍵和約束

libingql發表於2014-11-24
SQLServer

1 約束的型別

　　約束可以分為3大類：實體約束、域約束和參照完整性約束。

1.1 實體約束

　　實體約束都是關於比較行的，實體約束並不關心整體列中的資料，它只對特定行感興趣。

1.2 域約束

　　域約束處理一個或多個列，確保一個特定列或一組特定列滿足特地的標準。在插入或更新一行時，域約束不考慮其他行。如限定UnitPrice列的值大於或等於0，這就是域約束。

1.3 參照完整性約束

　　如果某列的值必須與其他列（該列可能在同一個表中，或者更通常的是在不同的表中）的值匹配，這就意味著建立了參照完整性約束。

2 鍵約束

　　常用的鍵約束包括：主鍵約束、外來鍵約束和唯一約束（也叫替換鍵約束）。一個表只能有一個主鍵約束，可以有多個外來鍵約束和多個唯一約束。

2.1 主鍵約束

　　主鍵是每行的唯一識別符號，必須包含唯一的值，不能為NULL。一個表可以有多個主鍵，當然也允許表中不設主鍵。

　　主鍵約束命名的可參考規則：PK_[table_name]

ALTER TABLE dbo.[Product] ADD CONSTRAINT PK_Product PRIMARY KEY ([ProductID])

　　一般情況下主鍵還會同時建立聚集索引：

ALTER TABLE dbo.[Product] ADD CONSTRAINT PK_Product PRIMARY KEY CLUSTERED ([ProductID])

2.2 外來鍵約束

　　外來鍵能確保資料完整性，也能表現表之間的關係。新增外來鍵之後，插入引用表的記錄要麼必須與被引用的表列中的某條記錄匹配，要麼外來鍵列的值設定為NULL。

　　外來鍵約束命名的可參考規則：FK_<foreign_key_table_name>_<primary_key_table_name>

　　若外來鍵表中存在多個外來鍵欄位對同一個主鍵表的引用，則外來鍵約束的命名可參考規則：

　　FK_<foreign_key_table_name>_<primary_key_table_name>_<colunm_name>

ALTER TABLE dbo.Product ADD CONSTRAINT
    FK_Product_Category FOREIGN KEY
    (
        CategoryID
    )
    REFERENCES dbo.Category
    (
    　　 CategoryID
    )
    ON DELETE  CASCADE

2.3 唯一約束

　　唯一約束要求在表中指定的列上有一個唯一值，一個表可以有多個唯一約束。

　　唯一約束可參考的命名規則：UQ_<table_name>_<column_name>

ALTER TABLE dbo.[Product] ADD CONSTRAINT UQ_Product_BarCode UNIQUE ([BarCode])

3 域約束

3.1 CHECK約束

　　CHECK約束不限於一個特定的列，CHECK約束可以和一個列關聯，也可以和表關聯。可以檢查一個列的值相對於另外一個列的值。CHECK約束可以用於檢查列值組合是否滿足條件。

　　CHECK約束可參考命名規則：CK_<table_name>_<column_name>

ALTER TABLE dbo.[Product] ADD CONSTRAINT CK_Product_UnitPrice CHECK ([UnitPrice] >= 0)
ALTER TABLE dbo.[Product] ADD CONSTRAINT CK_Product_UnitPrice CHECK ([BeginDate] > [EndDate])

3.2 DEFAULT約束

　　DEFAULT約束定義了當插入新的記錄時為未提供相應資料的列設定預設值，預設值只在INSERT時起作用。

　　DEFAULT約束可參考命名規則：DF_<table_name>_<column_name>

ALTER TABLE dbo.[Product] ADD CONSTRAINT DF_Product_CreateDate DEFAULT GETDATE() FOR [CreateDate]

4 禁用約束

　　有時會希望暫時或永久的消除約束，禁用一個資料完整性規則通常是因為已經有無效資料，這樣的資料通常分為兩類：在建立約束時已經存在的資料和在約束建立以後希望新增的資料。

　　不能禁用主鍵約束或者唯一約束。

4.1 在建立約束時忽略無效的資料

　　要新增一個約束，但又不將其應用到已存在的資料中，可以在執行ALTER TABLE語句新增約束時使用WITH NOCHECK選項。

ALTER TABLE dbo.[Product] WITH NOCHECK
ADD CONSTRAINT CK_Product_UnitPrice CHECK ([UnitPrice] > 0)

4.2 臨時禁用已存在的約束

　　在ALTER語句中使用NOCHECK選項可以臨時取消需要處理的約束。

ALTER TABLE dbo.[Product] NOCHECK CONSTRAINT CK_Product_UnitPrice

　　重新恢復約束

ALTER TABLE dbo.[Product] CHECK CONSTRAINT CK_Product_UnitPrice

　　禁用全部約束

ALTER TABLE [dbo].[Product] NOCHECK CONSTRAINT ALL

　　啟用全部約束

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT ALL

5 刪除約束

ALTER TABLE dbo.[Product] DROP CONSTRAINT CK_Product_UnitPrice

6 檢視錶中全部約束

EXEC sp_helpconstraint Product

7 規則

　　規則與CHECK類似，它們之間的區別是規則每次只能作用於一個列，而CHECK約束可以作用於多個列，如BeginDate > EndDate。可以將同一個規則分別繫結到一個表中的多個列，規則分別作用於每個列，不會意識到其他列的存在。

7.1 建立規則

　　建立規則語法

CREATE RULE [ schema_name . ] rule_name 
AS condition_expression
[ ; ]

　　condition_expression 包括一個變數。 每個區域性變數的前面都有一個@符號，在建立規則時可以使用任何名稱或符號表示值。

　　為特定列建立規則

CREATE RULE RU_UnitPrice
AS
    @UnitPrice > 0

　　建立具有範圍的規則

CREATE RULE RU_Range
AS
    @Value > 0 AND @Value < 100

7.2 檢視規則定義

EXEC sp_helptext RU_Range

7.3 繫結規則

sp_bindrule [ @rulename = ] 'rule' , 
     [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ] 
EXEC sp_bindrule 'RU_UnitPrice','[Product].[UnitPrice]'

7.4 取消規則

sp_unbindrule [ @objname = ] 'object_name' 
     [ , [ @futureonly = ] 'futureonly_flag' ]
EXEC sp_unbindrule '[Product].[UnitPrice]'

7.5 刪除規則

DROP RULE { [ schema_name . ] rule_name } [ ,...n ] [ ; ]
DROP RULE RU_UnitPrice

8. 預設值

8.1 建立預設值

CREATE DEFAULT [ schema_name . ] default_name 
AS constant_expression [ ; ]
CREATE DEFAULT DF_CurrentDateTime
AS
    GETDATE()

8.2 檢視預設值定義

EXEC sp_helptext DF_CurrentDateTime

8.3 繫結預設值

EXEC sp_bindefault 'DF_CurrentDateTime','[Product].[CreateDate]'

8.4 取消預設值

EXEC sp_unbindefault '[Product].[CreateDate]'

8.5 刪除預設值

DROP DEFAULT DF_CurrentDateTime

9 檢視錶中的約束

EXEC sp_helpconstraint [Product]

相關文章