SQL Server中Check束縛的進修教程

SQL Server中Check束縛的進修教程。本站提示廣大學習愛好者：（SQL Server中Check束縛的進修教程）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server中Check束縛的進修教程正文

0.甚麼是Check束縛？

CHECK束縛指在表的列中增長額定的限制前提。

注： CHECK束縛不克不及在VIEW中界說。CHECK束縛只能界說的列必需包括在所指定的表中。CHECK束縛不克不及包括子查詢。

創立表時界說CHECK束縛

1.1 語法：

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

個中，DISABLE症結之是可選項。假如應用了DISABLE症結字，當CHECK束縛被創立後，CHECK束縛的限制前提不會失效。

1.2 示例1：數值規模驗證

create table tb_supplier
(
 supplier_id    number,
 supplier_name   varchar2(50),
 contact_name   varchar2(60),
 /*界說CHECK束縛，該束縛在字段supplier_id被拔出或許更新時驗證，當前提不知足時觸發。*/
 CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999)
);

驗證：
在表中拔出supplier_id知足前提和不知足前提兩種情形:

--supplier_id知足check束縛前提，此筆記錄可以或許勝利拔出
insert into tb_supplier values(200, 'dlt','stk');
 
--supplier_id不知足check束縛前提，此筆記錄可以或許拔出掉敗，並提醒相干毛病以下
insert into tb_supplier values(1, 'david louis tian','stk');

不知足前提的毛病提醒：

Error report -
SQL Error: ORA-02290: check constraint (502351838.CHECK_TB_SUPPLIER_ID) violated
02290. 00000 - "check constraint (%s.%s) violated"
*Cause:  The values being inserted do not satisfy the named check

1.3 示例2：強迫拔出列的字母為年夜寫

create table tb_products
(
 product_id    number not null,
 product_name   varchar2(100) not null,
 supplier_id    number not null,
 /*界說CHECK束縛check_tb_products,用處是限制拔出的產物稱號必需為年夜寫字母*/
 CONSTRAINT check_tb_products
 CHECK (product_name = UPPER(product_name))
);

驗證：
在表中拔出product_name知足前提和不知足前提兩種情形：

--product_name知足check束縛前提，此筆記錄可以或許勝利拔出
insert into tb_products values(2, 'LENOVO','2');
--product_name不知足check束縛前提，此筆記錄可以或許拔出掉敗，並提醒相干毛病以下
insert into tb_products values(1, 'iPhone','1');

不知足前提的毛病提醒：

SQL Error: ORA-02290: check constraint (502351838.CHECK_TB_PRODUCTS) violated
02290. 00000 - "check constraint (%s.%s) violated"
*Cause:  The values being inserted do not satisfy the named check

2. ALTER TABLE界說CHECK束縛

2.1 語法

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

個中，DISABLE症結之是可選項。假如應用了DISABLE症結字，當CHECK束縛被創立後，CHECK束縛的限制前提不會失效。

2.2 示例預備

drop table tb_supplier;
--創立實例表
create table tb_supplier
(
 supplier_id    number,
 supplier_name   varchar2(50),
 contact_name   varchar2(60)
);

2.3 創立CHECK束縛

--創立check束縛
alter table tb_supplier
add constraint check_tb_supplier
check (supplier_name IN ('IBM','LENOVO','Microsoft'));

2.4 驗證

--supplier_name知足check束縛前提，此筆記錄可以或許勝利拔出
insert into tb_supplier values(1, 'IBM','US');
 
--supplier_name不知足check束縛前提，此筆記錄可以或許拔出掉敗，並提醒相干毛病以下
insert into tb_supplier values(1, 'DELL','HO');

不知足前提的毛病提醒：

SQL Error: ORA-02290: check constraint (502351838.CHECK_TB_SUPPLIER) violated
02290. 00000 - "check constraint (%s.%s) violated"
*Cause:  The values being inserted do not satisfy the named check

3. 啟用CHECK束縛

3.1 語法

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

3.2 示例

drop table tb_supplier;
--重建表和CHECK束縛
create table tb_supplier
(
 supplier_id    number,
 supplier_name   varchar2(50),
 contact_name   varchar2(60),
 /*界說CHECK束縛，該束縛盡在啟用後失效*/
 CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999) DISABLE
);
 
--啟用束縛
ALTER TABLE tb_supplier ENABLE CONSTRAINT check_tb_supplier_id;

 
3.3應用Check束縛晉升機能

在SQL Server中，SQL語句的履行是依附查詢優化器生成的履行籌劃，而履行籌劃的利害直接關乎履行機能。

在查詢優化器生成履行籌劃進程中，須要參考元數據來盡量生成高效的履行籌劃，是以元數據越多，則履行籌劃更能夠會高效。所謂須要參考的元數據重要包含：索引、表構造、統計信息等，但還有一些不是很被留意的元數據，個中包含本文論述的Check束縛。
圖1.簡略查詢
查詢優化器在生成履行籌劃之前有一個階段叫做代數樹優化，好比說上面這個簡略查詢：

查詢優化器認識到1=2這個前提是永久不相等的，是以不須要前往任何數據，是以也就沒有需要掃描表，從圖1履行籌劃可以看出僅僅掃描常量後肯定了1=2永久為false後，便可完成查詢。

那末Check束縛呢？

Check束縛可以確保一列或多列的值相符表達式的束縛。在某些時刻，Check束縛也能夠為優化器供給信息，從而優化機能，好比看圖二的例子。

圖2.有Check束縛的列晉升查詢機能

圖2是一個簡略的例子，有時刻在分區視圖中運用Check束縛也會晉升機能，測試代碼以下：

CREATE TABLE [dbo].[Test2007](
  [ProductReviewID] [int] IDENTITY(1,1) NOT NULL,
  [ReviewDate] [datetime] NOT NULL
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Test2007] WITH CHECK ADD CONSTRAINT [CK_Test2007] CHECK (([ReviewDate]>='2007-01-01' AND [ReviewDate]'2007-12-31'))
GO
 
ALTER TABLE [dbo].[Test2007] CHECK CONSTRAINT [CK_Test2007]
GO
 
CREATE TABLE [dbo].[Test2008](
  [ProductReviewID] [int] IDENTITY(1,1) NOT NULL,
  [ReviewDate] [datetime] NOT NULL
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Test2008] WITH CHECK ADD CONSTRAINT [CK_Test2008] CHECK (([ReviewDate]>='2008-01-01' AND [ProductReviewID]'2008-12-31'))
GO
 
ALTER TABLE [dbo].[Test2008] CHECK CONSTRAINT [CK_Test2008]
GO
 
INSERT INTO [Test2008] values('2008-05-06')
INSERT INTO [Test2007] VALUES('2007-05-06')
 
CREATE VIEW testPartitionView
AS
SELECT * FROM Test2007
UNION
SELECT * FROM Test2008
 
SELECT * FROM testPartitionView
WHERE [ReviewDate]='2007-01-01'
 
SELECT * FROM testPartitionView
WHERE [ReviewDate]='2008-01-01'
 
SELECT * FROM testPartitionView
WHERE [ReviewDate]='2010-01-01'

我們針對Test2007和Test2008兩張表構造如出一轍的表做了一個分區視圖。並對日期列做了Check束縛，限制每張表包括的數據都是特定一年內的數據。當我們對視圖停止查詢並給定分歧的挑選前提時，可以看到成果如圖3所示。

圖3.分歧的前提發生分歧的履行籌劃
由圖3可以看出，當挑選前提為2007年時，主動只掃描2007年的表，2008年的表也是異樣。而當查詢規模超越了2007和2008年的Check束縛後，查詢優化器主動剖斷成果為空，是以不做任何IO操作，從而晉升了機能。
結論
在Check束縛前提為簡略的情形下（指的是束縛限制在單列且表達式中不包括函數），不只可以束縛數據完全性，在許多時刻還可以或許供給給查詢優化器信息從而晉升機能。

4. 禁用CHECK束縛

4.1 語法

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

4.2 示例

--禁用束縛
ALTER TABLE tb_supplier DISABLE CONSTRAINT check_tb_supplier_id;

 
5. 束縛具體信息檢查
語句:

--檢查束縛的具體信息
select
constraint_name,--束縛稱號
constraint_type,--束縛類型
table_name,--束縛地點的表
search_condition,--束縛表達式
status--能否啟用
from user_constraints--[all_constraints|dba_constraints]
where constraint_name='CHECK_TB_SUPPLIER_ID';

6. 刪除CHECK束縛
6.1 語法

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

6.2 示例

ALTER TABLE tb_supplier
DROP CONSTRAINT check_tb_supplier_id;