《SQL Server 2008從入門到精通》--20180629

標籤：建立   參數   create   建表   指定   從入門到精通   iss   ons   val   

約束主關鍵字約束（Primary Key Constraint）

用來指定表中的一列或幾列組合的值在表中具有唯一性。建立主鍵的目的是讓外鍵來引用。

Primary Key的建立方式

在建立表時建立Primary Key

CREATE TABLE table1(    t_id VARCHAR(12) ,    t_name VARCHAR(20),    t_phone VARCHAR(20),    CONSTRAINT t_idss PRIMARY KEY(t_id));

對t_id列建立主鍵，約束名為t_idss。

刪除Primary Key

ALTER TABLE table1DROP CONSTRAINT t_idss;

約束名與列名不一致，此處填寫約束名

向已有表中添加Primary Key

ALTER TABLE table1ADD CONSTRAINT t_idssPRIMARY KEY(t_id);

添加Primary Key的另一種樣本

ALTER TABLE ProductsADD PRIMARY KEY(prod_id);

雖然上述代碼運行沒問題，查看錶格設計也可以看到Primary Key設定成功，但是在刪除Primary Key操作時會提示：
訊息3728，層級16，狀態1，第1 行
‘prod_id‘ 不是約束。
訊息3727，層級16，狀態0，第1 行
未能刪除約束。請參閱前面的錯誤資訊。
原因是添加Primary Key語句中沒有用CONSTRAINT指明約束名，系統自動產生了主鍵名和約束名，要先查看主鍵名和約束名，刪除時填寫的也是約束名。
這種情況的正確刪除方法

ALTER TABLE ProductsDROP CONSTRAINT CK__Products__prod_p__1A14E395;ALTER TABLE ProductsDROP CONSTRAINT PK__Products__56958AB222AA2996;

多列組合添加主鍵約束

CREATE TABLE table1(    t_id VARCHAR(12),    s_id VARCHAR(20),    score FLOAT,    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id));

外關鍵字約束（Foreign Key Constraint）

定義了表之間的關係，用來維護兩個表之間的一致性的關係。
在建立表時建立Foreign Key Constraint

CREATE TABLE table2(    s_id VARCHAR(20),    s_name VARCHAR(12),    s_tellphone VARCHAR(11),    s_address VARCHAR(20),    CONSTRAINT PK_s_id PRIMARY KEY(s_id),);--首先建立table2，設定s_id為主鍵CREATE TABLE table1(    t_id VARCHAR(12),    s_id VARCHAR(20),    score FLOAT,    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--建立table1，對t_id和s_id設定聯合主鍵，鍵名pk_ts_id    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--對s_id設定外鍵fk_s_id    REFERENCES table2(s_id)--外鍵fk_s_id外部索引鍵關聯table2的列s_id    ON DELETE CASCADE--設定在table1的s_id刪除時table2的s_id同時刪除    ON UPDATE CASCADE--設定在table1的s_id更新時table2的s_id同時更新);

註：對table1設定外部索引鍵關聯table2，在插入資料時需要先插入table2的資料，才能成功插入table1的資料。更改table2.s_id資料，table1.s_id資料也會自動改變。但是更改table1.s_id資料，執行時報外鍵衝突會報。總之對table1設定外部索引鍵關聯table2後，table1的資料跟著table2走，不能反著來。

添加和刪除外鍵約束同主鍵。

UNIQUE約束

除主鍵外另一種可以定義唯一約束的類型，允許空值。添加UNIQUE的方法同上，這裡只簡單舉例。

USE testGOALTER TABLE table2ADD CONSTRAINT uk_s_tellphoneUNIQUE(s_tellphone);

CHECK約束

分為資料表條件約束和列約束，用於限制欄位值在某個範圍。

添加CHECK約束

ALTER TABLE table2ADD sex CHAR(2);--在table表中添加sex，資料類型為CHAR，用來存放性別GOALTER TABLE table2ADD CONSTRAINT ck_sex CHECK(sex in(‘男‘,‘女‘));

註：此時sex列資料類型不能是bit，如果填寫bit，只能儲存0和1，用CHECK約束限制結果為男和女就會報錯。

在建立表的時候添加CHECK 條件約束

CREATE TABLE table3(    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12),    CONSTRAINT ck_t3_type CHECK(t3_type in(‘類型1‘,‘類型2‘,‘類型3‘)) )

添加了約束後如果插入不符合約束的資料

INSERT INTO table3(    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038219‘,‘李建‘,‘社會與科學‘,‘任何資料‘);

訊息547，層級16，狀態0，第1 行
INSERT 語句與CHECK 條件約束"ck_t3_type"衝突。該衝突發生於資料庫"test"，表"dbo.table3", column ‘t3_type‘。
語句已終止。

刪除CHECK約束

ALTER TABLE table3DROP CONSTRAINT ck_t3_type;

DEFAULT約束

通過定義列的預設值或使用資料庫的預設值對象綁定表列，來指定列的預設值。

在建表時添加DEFAULT約束

CREATE TABLE table3(    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12) DEFAULT ‘類型1‘ )

刪除DEFAULT約束

ALTER TABLE table3DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;--DF__table3__t3_type__3D5E1FD2是DEFAULT約束的約束名

添加約束不指定約束名

ALTER TABLE table3ADD DEFAULT ‘類型2‘ FOR t3_type;GO

添加約束指定約束名

ALTER TABLE table3ADD CONSTRAINT df_t3_typeDEFAULT ‘類型2‘ FOR t3_type;GO

NOT NULL約束

限制欄位值不為空白。

建表時設定NOT NULL約束

CREATE TABLE table3(    t3_id VARCHAR(12) NOT NULL,    t3_name VARCHAR(20) NOT NULL,    t3_class VARCHAR(12) NOT NULL,    t3_type VARCHAR(12) NOT NULL )

為已存在的列添加NOT NULL約束

ALTER TABLE table3ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

刪除NOT NULL約束

ALTER TABLE table3ALTER COLUMN t3_type VARCHAR(12) NULL;GO

自訂預設值對象維護資料完整性

CREATE DEFAULT date_today AS GETDATE();--建立預設值對象名date_today,預設值為getdate()函數，擷取當前日期GOEXEC sp_addtype date_time,‘date‘,‘NULL‘;--利用預存程序建立自訂資料類型date_time,參照系統資料類型dateGOEXEC sp_bindefault ‘date_today‘,‘date_time‘;--將預設值對象date_today綁定到自訂資料類型date_time上GOCREATE TABLE table3(--建立table3，設定欄位t3_date的資料類型為date_time    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12),    t3_date date_time);GOINSERT INTO table3(--為建立表table3插入一條資料，不指定t3_date的值，看預設值是否有效    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038220‘,‘李建‘,‘社會與科學‘,‘類型1‘);GOSELECT * FROM table3;--查詢table3資料，看t3_date是否有預設值為當前日期

查詢結果如下

直接將預設值對象綁定給列

ALTER TABLE table3ADD t3_date1 DATE;--在table3表中新增一列t3_date1，資料類型為DATEGOEXEC sp_bindefault ‘date_today‘,‘table3.t3_date1‘;--直接將預設值對象date_today綁定到table3的t3_date1列GOINSERT INTO table3(--為建立表table3插入一條資料，不指定t3_date和t3_date1的值，看預設值是否有效    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038221‘,‘李建‘,‘社會與科學‘,‘類型‘);GOSELECT * FROM table3;GO

查詢結果如下

預存程序查詢預設值對象的所有者

USE testEXEC sp_help date_today;GO

結果

刪除預設值對象

DROP DEFAULT date_today;

刪除不成功，提示以下資訊：
訊息3716，層級16，狀態3，第1 行
無法刪除預設值‘date_today‘，因為它已綁定到一個或多個列。
那麼我們知道，當一個預設值對象綁定了列之後，就無法刪除它，如果想要刪除，就必須先解除綁定。在上面的操作中，我們的預設值對象date_today綁定了test資料庫table3表的t3_date1欄位。

對列解除綁定預設值對象

USE testGOEXEC sp_unbindefault ‘table3.t3_date1‘;

此時我們再次嘗試刪除預設值對象，發現還是不行，此時要注意，在上面的操作中，預設值對象date_today不止綁定了t3_date1列，還綁定了自訂資料類型date_time，並將該資料類型定義給了t3_date列，我們還需要對date_time解除綁定預設值對象。

EXEC sp_unbindefault ‘date_time‘;

此時再次刪除預設值對象，就可以成功刪除。刪除預設值對象後，原先綁定的欄位不會再有預設值。

自訂規則維護資料完整性

規則是對列或自訂資料類型的值的規定和限制。自訂規則的運算式一定要返回布爾類型的值，並且運算式中不能包含多個變數。

CREATE RULE score_rule AS @math_score>=0;GO--建立規則score_rule,參數@math_scoreEXEC sp_addtype ‘score_type‘,‘float‘,‘NULL‘;GO--建立自訂資料類型score_typeCREATE TABLE table_score(--建立表table_score，預設mt_score和at_score欄位用於綁定規則s_id VARCHAR(4),s_name VARCHAR(10),mt_score float,--該欄位將用於規則score_rule綁定到列at_score score_type--該欄位將用於規則score_rule綁定到自訂資料類型);GOEXEC sp_bindrule ‘score_rule‘,‘score_type‘;GO--將score_rule規則綁定到自訂資料類型score_typeEXEC sp_bindrule ‘score_rule‘,‘table_score.mt_score‘;GO--將score_rule規則綁定到table_score表的mt_score列----以下進行規則測試INSERT INTO table_score(s_id,s_name,mt_score,at_score)VALUES(‘0001‘,‘張華‘,‘-1‘,‘-1‘);GO

進行違反規則的插入後，資料庫報錯
訊息513，層級16，狀態0，第1 行
列的插入或更新與先前的CREATE RULE 語句所指定的規則發生衝突。該語句已終止。衝突發生於資料庫‘test‘，表‘dbo.table_score‘，列‘mt_score‘。
語句已終止。
很明顯，mt_score的插入值為-1，違反了必須大於等於0的規則，資料庫報錯。將mt_score的插入值改成符合規則的資料，再次運行插入語句，資料庫依然會報錯，因為at_score欄位的插入值也是違反規則的。將兩個資料改成符合規則的返回，執行成功。

註：建立規則時運算式一定要是返回布爾類型的值，否則會報錯

訊息4145，層級15，狀態1，過程sum_score，第1 行
在應使用條件的上下文(在‘;‘ 附近)中指定了非布爾類型的運算式。

刪除自訂規則

和自訂預設值對象一樣，刪除自訂規則要求該規則先與欄位和自訂資料類型解除綁定。在上面的操作中，score_rule規則與自訂資料類型score_type以及列mt_score已綁定。因此執行以下語句：

EXEC sp_unbindrule ‘score_type‘;GO--解除規則score_rule與score_type之間的綁定EXEC sp_unbindrule ‘table_score.mt_score‘;GO--解除規則score_rule與表table_score的mt_score列的綁定DROP RULE score_rule;--刪除score_rule規則

註：經過實驗，一個列只能綁定1條規則，如果對一個資料行繫結2條規則，前一條規則會被後一條規則頂替。

查看自訂規則

EXEC sp_help ‘score_rule‘;

結果

查看自訂規則的定義資訊

EXEC sp_helptext ‘score_rule‘;GO

結果

《SQL Server 2008從入門到精通》--20180629