（4.46）sql server中的rule

回到顶部

【1】create rule

作用：

规则是对存储的数据表中的列或者用户自定义数据类型中的值的约束。

　　规则可以在创建表之后单独对列进行约束。

　　规则可以同时作用于多个数据列。

【1.1】基本形式

CREATE RULE [ schema_name . ] rule_name AS condition_expression [ ; ]

schema_name
是规则所属的架构的名称。

rule_name
是新规则的名称。规则名称必须符合标识符规则。指定规则所有者名称是可选的。

condition_expression
是定义规则的一个或多个条件。规则可以是在WHERE子句中有效的任何表达式，并且可以包括诸如算术运算符，关系运算符和谓词之类的元素（例如，IN，LIKE，BETWEEN）。规则不能引用列或其他数据库对象。可以包含不引用数据库对象的内置函数。不能使用用户定义的功能。

condition_expression

包含一个变量。每个局部变量之前都有@符号（@）。该表达式引用用UPDATE或INSERT语句输入的值。创建规则时，可以使用任何名称或符号来表示值，但第一个字符必须为at符号（@）

【1.2】权限与限制

限制：

（1）CREATE RULE不能与其他Transact-SQL语句合并在一个批处理中。

（2）只能在当前数据库中创建规则。创建规则后，执行sp_bindrule将规则绑定到列或别名数据类型。规则必须与列数据类型兼容。例如，“ @ value LIKE A％”不能用作数字列的规则。

　　规则不能绑定到text，ntext，image，varchar（max），nvarchar（max），varbinary（max），xml，CLR用户定义类型或时间戳列。规则不能绑定到计算列。

（3）字符和日期常量用单引号（'）括起来，二进制常量前面加0x。如果该规则与其绑定的列不兼容，则在插入值时，SQL Server数据库引擎会返回一条错误消息，但在绑定该规则时，则不会返回错误消息。

（4）仅当您尝试向别名数据类型的数据库列中插入值或更新值时，才会激活与别名数据类型绑定的规则。由于规则不测试变量，因此请勿为别名数据类型变量分配值，该值将被绑定到相同数据类型的列的规则拒绝。

（5）要获取有关规则的报告，请使用 sp_help。若要显示规则的文本，请以规则名称为参数执行 sp_helptext 。要重命名规则，请使用 sp_rename。

　　在创建具有相同名称的新规则之前，必须使用 DROP RULE 删除该规则，并且在删除该规则之前必须使用 sp_unbindrule 取消绑定该规则。要从列中取消绑定规则，请使用 sp_unbindrule。

（6）

您可以将新规则绑定到列或数据类型，而无需取消绑定前一条；新规则将覆盖前一条。绑定到列的规则始终优先于绑定到别名数据类型的规则。将规则绑定到列将替换已经绑定到该列的别名数据类型的规则。但是，将规则绑定到数据类型不会替换绑定到该别名数据类型的列的规则。下表显示了将规则绑定到已存在规则的列和别名数据类型时生效的优先级。

表格1
新规则必然	绑定到别名数据类型的旧规则	绑定到 Column的旧规则
别名数据类型	旧规则被替换	没变
柱	旧规则被替换	旧规则被替换

如果列中既有默认值又有与其关联的规则，则该默认值必须在该规则定义的域内。永远不会插入与规则冲突的默认值。每次尝试插入这样的默认值时，SQL Server数据库引擎都会生成一条错误消息。

权限：

　　要至少执行CREATE RULE，用户必须在当前数据库中具有CREATE RULE权限，并在创建规则的模式上具有ALTER权限。

回到顶部

【2】创建rule 举例

A.创建一个带范围的规则

以下示例创建一个规则，该规则限制插入此规则所绑定的一个或多个列中的整数的范围。

CREATE RULE range_rule  
AS   
@range>= $1000 AND @range <$20000;

B.使用列表创建规则

下面的示例创建一个规则，该规则将输入到一个或多个列（绑定到该规则）中的实际值限制为仅该规则中列出的值。

CREATE RULE list_rule  
AS   
@list IN ('1389', '0736', '0877');

C.用模式创建规则

以下示例创建了一个规则，该规则遵循一个由两个字符组成的模式，后接连字符（-），任意数量的字符或不包含任何字符，并以从0到的整数结尾9。

CREATE RULE pattern_rule   
AS  
@value LIKE '__-%[0-9]'

回到顶部

【3】整体使用最佳实践

回到顶部

【3.1】使用规则

　　规则是对存储的数据表中的列或者用户自定义数据类型中的值的约束。

　　规则可以在创建表之后单独对列进行约束。

　　规则可以同时作用于多个数据列。

创建规则　　　　

USE DB_NAME
GO
CREATE RULE rule_name
AS
@value > 0

--创建一个规则，使用该规则的列值必须大于0

绑定规则

--绑定
EXEC sp_bindrule 'rule_name', 'Table_Name.FieldName'

--解绑
EXEC sp_unbindrule 'Table_Name.FieldName'

查看规则

EXEC sp_help 'rule_name'

--详情
EXEC sp_helptext 'rule_name'

删除规则

DROP RULE rule_name

回到顶部

【4】使用默认值规则

创建默认值

CREATE DEFAULT default_date AS GETDATE()

绑定默认值

--绑定
EXEC sp_bindefault 'default_date', 'table_Name.FieldName'
--解绑
EXEC sp_unbindefault 'table_Name2.FieldName'

查看默认值

 EXEC sp_help  default_date
--详情 
EXEC sp_helptext default_date

删除默认值

DROP DEFAULT default_date

回到顶部

【5】查看某个表绑定了哪些rule，查看一共有哪些rule

create table test(id int, name varchar(10))
go
CREATE RULE ARule  AS @A BETWEEN 0 AND 50000 
go
EXEC sp_bindrule 'ARule', 'test.id'
go
select object_name(object_id) oname , 
       name, column_id, object_name(rule_object_id) rule_name
 from sys.columns where object_id = object_id('test')
go
drop table test 
go
drop RULE ARule
go