MS SQL Server 中的”預設值”

-- 說明: T-SQL 中沒有"預設值對象"和"預設值約束"的區別, 而是統稱為"預設值". 這裡僅為了討論方便.<br />-- 所謂"預設值對象", 指作為 CREATE DEFAULT 語句的一部分建立的預設值定義;<br />-- 所謂"預設值約束", 指作為 CREATE TABLE 或 ALTER TABLE 語句的一部分建立的預設值定義.<br />-- 注意: 後續版本的 Microsoft SQL Server 將刪除由 CREATE DEFAULT 語句建立的預設值定義, 應使用<br />-- 通過 ALTER TABLE 或 CREATE TABLE 的 DEFAULT 關鍵字建立的預設定義. </p><p>-- 1. 建立"預設值對象"<br />create default df_Test as 100;<br />go </p><p>-- 2. 建立測試表<br />create table t_Tab1<br />(<br /> c1 smallint identity,<br /> c2 smallint null default 2, -- 作為 CREATE TABLE 語句的一部分建立匿名的"預設值約束"<br /> c3 smallint null,<br /> c4 smallint null constraint df_t_Tab1_c4 default 4, -- 作為 CREATE TABLE 語句的一部分建立命名的"預設值約束"<br /> c5 smallint null,<br /> c6 smallint null,<br /> c7 smallint null<br />); </p><p>create table t_Tab2<br />(<br /> c1 smallint identity,<br /> c2 smallint null,<br /> c3 smallint null<br />);<br />go</p><p>-- 3. 作為 ALTER TABLE 語句的一部分建立匿名的和命名的"預設值約束"<br />alter table t_Tab1 add constraint df_t_Tab1_c6 default 6 for c6;<br />alter table t_Tab1 add default 7 for c7;<br />go </p><p>-- 4. 將"預設值對象" df_Test 綁定到列上<br />-- ! 同一個"預設值對象"既可同時綁定到同一個表的不同列上, 也可同時綁定到不同表的列上.<br />execute sp_bindefault df_Test, N't_Tab1.c3';<br />execute sp_bindefault df_Test, N't_Tab1.c5';<br />execute sp_bindefault df_Test, N't_Tab2.c2';<br />-- 作為 CREATE TABLE 語句的一部分建立的命名的"預設值約束"不能綁定到其它表上.<br />-- execute sp_bindefault df_t_Tab1_c6, N't_Tab2.c3'; </p><p>-- 作為 CREATE DEFAULT 語句的一部分建立的"預設值對象", 不能通過 ALTER TABLE 綁定到列上.<br />-- alter table t_Tab1 add df_Test for c3;<br />-- alter table t_Tab1 add df_Test default for c3;<br />-- alter table t_Tab1 add constraint df_Test default for c3;<br />go </p><p>-- 5. 測試預設值<br />insert into t_Tab1 default values;<br />insert into t_Tab1 default values;<br />insert into t_Tab1 default values;<br />select * from t_Tab1;<br />go </p><p>insert into t_Tab2 default values;<br />insert into t_Tab2 default values;<br />insert into t_Tab2 default values;<br />select * from t_Tab2;<br />go </p><p>-- 6. 通過 sys.default_constraints 目錄檢視, 檢測不到作為 CREATE DEFAULT 語句的一部分建立的"預設值對象".<br />SELECT sys.objects.name 表名,<br /> sys.columns.column_id 列ID,<br /> sys.columns.name 列名,<br /> sys.default_constraints.name 預設值約束名<br />FROM sys.default_constraints INNER JOIN sys.columns<br /> ON sys.default_constraints.parent_object_id = sys.columns.object_id<br /> AND sys.default_constraints.parent_column_id = sys.columns.column_id<br /> INNER JOIN sys.objects<br /> ON sys.columns.[object_id] = sys.objects.[object_id]<br />WHERE sys.objects.name = N't_Tab1';<br />go </p><p>-- 7. "預設值對象", 命名的"預設值約束", 匿名的"預設值約束"都是"結構描述範圍物件", 並且其 type 均是 D = DEFAULT（約束或獨立）.<br />select name into t_Temp from sys.objects where type = 'D';<br />select name as [架構範圍內 type 為 D 的資料庫物件名] from t_Temp;<br />go </p><p>-- 8. "預設值對象"是"預設值", 但不是"預設值約束"; 命名或匿名的"預設值約束"不是"預設值", 卻是"預設值約束".<br />select name as [架構範圍內 type 為 D 的資料庫物件名],<br /> OBJECTPROPERTY(OBJECT_ID(name, N'D'), N'IsDefault') as IsDefault<br />from t_Temp; </p><p>select name as [架構範圍內 type 為 D 的資料庫物件名],<br /> OBJECTPROPERTY(OBJECT_ID(name, N'D'), N'IsDefaultCnst') as IsDefaultCnst<br />from t_Temp; </p><p>drop table t_Temp;<br />go </p><p>-- 9. 為列解除（刪除）預設值綁定<br />-- #1 不能對"表名"使用 sp_unbindefault 來解除其所有列上的預設值綁定<br />-- execute sp_unbindefault N't_Tab1';<br />-- #2 解被除綁定了"預設值對象"的列的正確方法<br />execute sp_unbindefault N't_Tab1.c3';<br />execute sp_unbindefault N't_Tab1.c5';<br />execute sp_unbindefault N't_Tab2.c2';<br />-- #3 對命名或匿名的"預設值約束", 不能使用 sp_unbindefault 的方法來解除綁定; 需要使用 ALTER TABLE DROP CONSTRAINT 來解除.<br />-- execute sp_unbindefault N't_Tab1.c2';<br />-- execute sp_unbindefault N't_Tab1.c4';<br />go </p><p>-- 10. 要刪除使用者定義的預設值(即, "預設值對象"), 必須首先解除它與列的綁定.<br />-- 這裡 df_Test 與資料行繫結的解除已經在 9 中完成, 所以可以刪除了.<br />drop default df_Test;<br />go </p><p>-- 11. 刪除綁定了預設值的列<br />-- 說明: 假如一個列上綁定了預設值, 則刪除該列之前, 必須先解除其預設值綁定.<br />-- 如果綁定的是"預設值對象", 通過 sp_unbindefault 來解除;<br />-- 如果綁定的是"預設值約束"(命名或匿名的), 通過 ALTER TABLE DROP CONSTRAINT 來解除. </p><p>-- 12. 刪除表建立的資料庫物件<br />drop table t_Tab1;<br />drop table t_Tab2;<br />go

執行結果:

已將預設值綁定到列。<br />已將預設值綁定到列。<br />已將預設值綁定到列。</p><p>(1 行受影響)</p><p>(1 行受影響)</p><p>(1 行受影響)<br />c1 c2 c3 c4 c5 c6 c7<br />------ ------ ------ ------ ------ ------ ------<br />1 2 100 4 100 6 7<br />2 2 100 4 100 6 7<br />3 2 100 4 100 6 7</p><p>(3 行受影響)</p><p>(1 行受影響)</p><p>(1 行受影響)</p><p>(1 行受影響)<br />c1 c2 c3<br />------ ------ ------<br />1 100 NULL<br />2 100 NULL<br />3 100 NULL</p><p>(3 行受影響)</p><p>表名 列ID 列名 預設值約束名<br />-------- -------- --------- --------------------------<br />t_Tab1 2 c2 DF__t_Tab1__c2__3AD78439<br />t_Tab1 4 c4 df_t_Tab1_c4<br />t_Tab1 6 c6 df_t_Tab1_c6<br />t_Tab1 7 c7 DF__t_Tab1__c7__3EA8151D</p><p>(4 行受影響)</p><p>(5 行受影響)<br />架構範圍內 type 為 D 的資料庫物件名<br />-------------------------------------<br />df_Test<br />DF__t_Tab1__c2__3AD78439<br />df_t_Tab1_c4<br />df_t_Tab1_c6<br />DF__t_Tab1__c7__3EA8151D</p><p>(5 行受影響)</p><p>架構範圍內 type 為 D 的資料庫物件名 IsDefault<br />------------------------------------- -----------<br />df_Test 1<br />DF__t_Tab1__c2__3AD78439 0<br />df_t_Tab1_c4 0<br />df_t_Tab1_c6 0<br />DF__t_Tab1__c7__3EA8151D 0</p><p>(5 行受影響)</p><p>架構範圍內 type 為 D 的資料庫物件名 IsDefaultCnst<br />------------------------------------- -------------<br />df_Test 0<br />DF__t_Tab1__c2__3AD78439 1<br />df_t_Tab1_c4 1<br />df_t_Tab1_c6 1<br />DF__t_Tab1__c7__3EA8151D 1</p><p>(5 行受影響)</p><p>已解除了表列與其預設值之間的綁定。<br />已解除了表列與其預設值之間的綁定。<br />已解除了表列與其預設值之間的綁定。