第十章
使用約束強制數據完整性
Data Integrity
•資料的完整性
–確保該列中的任何一列的值在可接受的值是
•個體的完整性
–確保可以唯一標識在每一資料表的每一行
•參考性的完整性
–當一個值儲存的時候，必需確保在其他的表格內可以找到
•Constraint
•約束是一個CREATE TABLE語句的元素，執行數據完整性的三種類型之一
•Domain Integrity
•資料的完整性 ，確保在可接受的值是該列中的任何一列的值
•Check Constraint
•在新增和更新的時候檢查並約束強值的完整性
•Column-Level Check Constraint
•Create table table_name(
column_name datatype[ NULL | NOT NULL | IDENTITY ]
[ constraint constraint_name ]
check ( condition_using_column_name ),
…
column_name datatype [ NULL | NOT NULL | IDENTITY ]
)
•如果constraint_name不去命名的話，系統會自動給於名稱，建議自行命名
•多列檢查約束可以在列級或表級聲明
•可以有多個單個列的檢查約束。這是非常有用的，當兩個或兩個以上的限制，適用於一列，insert時失敗，你想知道這違反了規則
•Table-Level Check Constraint
•Create table table_name (
column_name datatype [ NULL | NOT NULL | IDENTITY ],
…
column_name datatype [ NULL | NOT NULL | IDENTITY ],
[ constraint constraint_name ]
check ( condition_involving_any_column_or_columns)
)
•表級Check Constraint只能引用任何表中的列
•如果有兩個或兩個以上的表級約束，除了最後的每一個表級約束，必須終止一個逗號
•Check Constraints and Defaults
•Adaptive Server允許有一個默認表檢查約束，互相矛盾
–任何使用默認的插入會失敗
Entity Integrity
•確保可以唯一標識在每一資料表的每一行
•Primary Key
•唯一的一個或多個列的組合的身份，在一個表的每一行
•主鍵約束強制執行實體完整性
•它指定的列上創建唯一索引
•在插入和更新時，強制執行的完整性
•限制每個表的一個主鍵約束
•主鍵約束創建的索引
•Unique
•Clustered by default
•不可以是NULL值
–不能創建空列
•不能使用DROP INDEX語句刪除
•Column-Level Primary Key Constraint
•create table table_name(
column_name datatype [NOT NULL | IDENTITY]
[constraint constraint_name]
primary key [clustered | nonclustered],
…
column_name datatype [NOT NULL | IDENTITY]
)
•Table-Level Primary Key Constraint
•create table table_name (
column_name datetype [ NULL | NOT NULL | IDENTITY ],
…
column_name datatype [ NULL | NOT NULL | IDENTITY ],
[ constraint constraint_name ]
primary key [ clustered | nonclustered ] ( column1[, column2 … ] )
)
•多列主鍵約束，必須在表級別聲明
•Unique Constraint
•唯一約束也強制執行實體完整性
•它指定的列上創建唯一索引
•在插入和更新時，強制執行的完整性
•表中可以有多個唯一約束
•唯一約束創建的索引
•Unique
•Nonclustered by default
•可以擁一個NULL值
–能創建空列
•不可以被drop index
•Column-Level Unique Constraint
•create table table_name(
column_name datatype [NULL | NOT NULL | IDENTITY]
[constraint constraint_name]
unique [clustered | nonclusterd],
…. column_name datatype [NULL | NOT NULL | IDENTITY ]
)
•Table-Level Unique Constraint
•create table table_name (
column_name datatype [ NULL | NOT NULL | IDENTITY],
…
column_name datatype [ NULL | NOT NULL | IDENTITY ],
[ constraint constraint_name ]
unique [ clustered | nonclustered ] ( column1 [, column2 … ] )
)
•多列唯一約束，必須在表級別聲明
Referential Integrity
•當一個值儲存的時候，必需確保在其他的表格內可以找到
•Foreign Key
•外鍵是另一個表的主鍵對應一個或多個列的組合
•引用約束強制參照完整性
•約束是執行過程中新增，更新和刪除
–如果新增或更新包含外鍵的值中不存在主鍵列，回報失敗
–如果更新或刪除試圖刪除一個主鍵值存在一個相應的外鍵，語句失敗
•Column-Level References Constraint
•create table table_name(
column_name datatype [NULL | NOT NULL | IDENTITY]
[constraint constraint_name]
references table_name(primary_key_column),
…
column_name datatype [NULL | NOT NULL | IDENTITY]
)
•Table-Level References Constraint
•create table table_name (
column_name datatype [ NULL | NOT NULL | IDENTITY ],
…
column_name datatype [ NULL | NOT NULL | IDENTITY ],
[ constraint constraint_name ]
foreign key ( list_of_columns_from_this_table)
references table_name ( primary_key_column_list )
)
•多重引用約束，必須在表級別上宣布
•規則
•在子表引用約束父表中的列必須有下列之一
–主鍵約束
–唯一約束
–使用CREATE index命令創建一個唯一的索引
•限制
•修改主鍵的值
–不能更新或刪除，如果值中存在任何外鍵
•修改外鍵值
–無法插入或更新，如果不存在新值，在對應的主鍵
•刪除主鍵的表
–如果任何外鍵引用的主鍵不能刪除表
Constraint
•Constraints並不會對已經存在的資料做限制
•Adding
•alter table table_name
•add constraint constraint_name
•{check (condition) | primary key (column_list) | uqique (column_list) | foreign key (column_list) references table (column_list)}
•Dropping
•alter table table_name
•drop constraint constraint_name
•Constraint Message
•System-Defined Constraint Message
•系統定義的約束消息顯示，當一個語句失敗是由於一個約束衝突
•User-Defined Constraint Messages
•可以自行定義約束消息
•建立
–sp_addmessage message_number, “message_text”
•給予
–sp_bindmsg constraint_name, message_number
•解除
–sp_unbindmsg constraint_name
•刪除
–sp_dropmessage message_number
•sp_helpconstraint table_name
–查詢特定資料表中的約束訊息
•sp_remame old_constraint_name, new_constraint_name
–修改訊息的名稱