-- 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 行受影响)
已解除了表列与其默认值之间的绑定。
已解除了表列与其默认值之间的绑定。
已解除了表列与其默认值之间的绑定。