MS SQL Server 中的"預設值"

-- 說明: T-SQL 中沒有"預設值對象"和"預設值限制"的差別, 而是統稱為"預設值". 這裡僅為了讨論友善.

-- 所謂"預設值對象", 指作為 CREATE DEFAULT 語句的一部分建立的預設值定義;

-- 所謂"預設值限制", 指作為 CREATE TABLE 或 ALTER TABLE 語句的一部分建立的預設值定義.

-- 注意: 後續版本的 Microsoft SQL Server 将删除由 CREATE DEFAULT 語句建立的預設值定義, 應使用

-- 通過 ALTER TABLE 或 CREATE TABLE 的 DEFAULT 關鍵字建立的預設定義.

-- 1. 建立"預設值對象"

create default df_Test as 100;

go

-- 2. 建立測試表

create table t_Tab1

(

c1 smallint identity,

c2 smallint null default 2, -- 作為 CREATE TABLE 語句的一部分建立匿名的"預設值限制"

c3 smallint null,

c4 smallint null constraint df_t_Tab1_c4 default 4, -- 作為 CREATE TABLE 語句的一部分建立命名的"預設值限制"

c5 smallint null,

c6 smallint null,

c7 smallint null

);

create table t_Tab2

(

c1 smallint identity,

c2 smallint null,

c3 smallint null

);

go

-- 3. 作為 ALTER TABLE 語句的一部分建立匿名的和命名的"預設值限制"

alter table t_Tab1 add constraint df_t_Tab1_c6 default 6 for c6;

alter table t_Tab1 add default 7 for c7;

go

-- 4. 将"預設值對象" df_Test 綁定到列上

-- ! 同一個"預設值對象"既可同時綁定到同一個表的不同列上, 也可同時綁定到不同表的列上.

execute sp_bindefault df_Test, N't_Tab1.c3';

execute sp_bindefault df_Test, N't_Tab1.c5';

execute sp_bindefault df_Test, N't_Tab2.c2';

-- 作為 CREATE TABLE 語句的一部分建立的命名的"預設值限制"不能綁定到其它表上.

-- execute sp_bindefault df_t_Tab1_c6, N't_Tab2.c3';

-- 作為 CREATE DEFAULT 語句的一部分建立的"預設值對象", 不能通過 ALTER TABLE 綁定到列上.

-- alter table t_Tab1 add df_Test for c3;

-- alter table t_Tab1 add df_Test default for c3;

-- alter table t_Tab1 add constraint df_Test default for c3;

go

-- 5. 測試預設值

insert into t_Tab1 default values;

insert into t_Tab1 default values;

insert into t_Tab1 default values;

select * from t_Tab1;

go

insert into t_Tab2 default values;

insert into t_Tab2 default values;

insert into t_Tab2 default values;

select * from t_Tab2;

go

-- 6. 通過 sys.default_constraints 目錄視圖, 檢測不到作為 CREATE DEFAULT 語句的一部分建立的"預設值對象".

SELECT sys.objects.name 表名,

sys.columns.column_id 列ID,

sys.columns.name 列名,

sys.default_constraints.name 預設值限制名

FROM sys.default_constraints INNER JOIN sys.columns

ON sys.default_constraints.parent_object_id = sys.columns.object_id

AND sys.default_constraints.parent_column_id = sys.columns.column_id

INNER JOIN sys.objects

ON sys.columns.[object_id] = sys.objects.[object_id]

WHERE sys.objects.name = N't_Tab1';

go

-- 7. "預設值對象", 命名的"預設值限制", 匿名的"預設值限制"都是"架構範圍内的對象", 并且其 type 均是 D = DEFAULT（限制或獨立）.

select name into t_Temp from sys.objects where type = 'D';

select name as [架構範圍内 type 為 D 的資料庫對象名] from t_Temp;

go

-- 8. "預設值對象"是"預設值", 但不是"預設值限制"; 命名或匿名的"預設值限制"不是"預設值", 卻是"預設值限制".

select name as [架構範圍内 type 為 D 的資料庫對象名],

OBJECTPROPERTY(OBJECT_ID(name, N'D'), N'IsDefault') as IsDefault

from t_Temp;

select name as [架構範圍内 type 為 D 的資料庫對象名],

OBJECTPROPERTY(OBJECT_ID(name, N'D'), N'IsDefaultCnst') as IsDefaultCnst

from t_Temp;

drop table t_Temp;

go

-- 9. 為列解除（删除）預設值綁定

-- #1 不能對"表名"使用 sp_unbindefault 來解除其所有列上的預設值綁定

-- execute sp_unbindefault N't_Tab1';

-- #2 解被除綁定了"預設值對象"的列的正确方法

execute sp_unbindefault N't_Tab1.c3';

execute sp_unbindefault N't_Tab1.c5';

execute sp_unbindefault N't_Tab2.c2';

-- #3 對命名或匿名的"預設值限制", 不能使用 sp_unbindefault 的方法來解除綁定; 需要使用 ALTER TABLE DROP CONSTRAINT 來解除.

-- execute sp_unbindefault N't_Tab1.c2';

-- execute sp_unbindefault N't_Tab1.c4';

go

-- 10. 要删除使用者定義的預設值(即, "預設值對象"), 必須首先解除它與列的綁定.

-- 這裡 df_Test 與列綁定的解除已經在 9 中完成, 是以可以删除了.

drop default df_Test;

go

-- 11. 删除綁定了預設值的列

-- 說明: 假如一個列上綁定了預設值, 則删除該列之前, 必須先解除其預設值綁定.

-- 如果綁定的是"預設值對象", 通過 sp_unbindefault 來解除;

-- 如果綁定的是"預設值限制"(命名或匿名的), 通過 ALTER TABLE DROP CONSTRAINT 來解除.

-- 12. 删除表建立的資料庫對象

drop table t_Tab1;

drop table t_Tab2;

go

執行結果:

已将預設值綁定到列。

已将預設值綁定到列。

已将預設值綁定到列。

(1 行受影響)

(1 行受影響)

(1 行受影響)

c1 c2 c3 c4 c5 c6 c7

------ ------ ------ ------ ------ ------ ------

1 2 100 4 100 6 7

2 2 100 4 100 6 7

3 2 100 4 100 6 7

(3 行受影響)

(1 行受影響)

(1 行受影響)

(1 行受影響)

c1 c2 c3

------ ------ ------

1 100 NULL

2 100 NULL

3 100 NULL

(3 行受影響)

表名 列ID 列名 預設值限制名

-------- -------- --------- --------------------------

t_Tab1 2 c2 DF__t_Tab1__c2__3AD78439

t_Tab1 4 c4 df_t_Tab1_c4

t_Tab1 6 c6 df_t_Tab1_c6

t_Tab1 7 c7 DF__t_Tab1__c7__3EA8151D

(4 行受影響)

(5 行受影響)

架構範圍内 type 為 D 的資料庫對象名

-------------------------------------

df_Test

DF__t_Tab1__c2__3AD78439

df_t_Tab1_c4

df_t_Tab1_c6

DF__t_Tab1__c7__3EA8151D

(5 行受影響)

架構範圍内 type 為 D 的資料庫對象名 IsDefault

------------------------------------- -----------

df_Test 1

DF__t_Tab1__c2__3AD78439 0

df_t_Tab1_c4 0

df_t_Tab1_c6 0

DF__t_Tab1__c7__3EA8151D 0

(5 行受影響)

架構範圍内 type 為 D 的資料庫對象名 IsDefaultCnst

------------------------------------- -------------

df_Test 0

DF__t_Tab1__c2__3AD78439 1

df_t_Tab1_c4 1

df_t_Tab1_c6 1

DF__t_Tab1__c7__3EA8151D 1

(5 行受影響)

已解除了表列與其預設值之間的綁定。

已解除了表列與其預設值之間的綁定。

已解除了表列與其預設值之間的綁定。