CREATE RULE (Transact-SQL)CREATE RULE (Transact-SQL)

本主題適用於:是SQL Server (從 2008 開始)否Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

建立一個稱為規則的物件。Creates an object called a rule. 當繫結到資料行或別名資料型別時,規則會指定能夠插入這個資料行的可接受的值。When bound to a column or an alias data type, a rule specifies the acceptable values that can be inserted into that column.

重要

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.我們建議您改用檢查條件約束。 We recommend that you use check constraints instead. CHECK 條件約束是利用 CREATE TABLE 或 ALTER TABLE 的 CHECK 關鍵字來建立的。Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE. 如需詳細資訊,請參閱 Unique Constraints and Check ConstraintsFor more information, see Unique Constraints and Check Constraints.

資料行或別名資料型別只能有一個繫結的規則。A column or alias data type can have only one rule bound to it. 不過,資料行可以同時有一個規則以及一個或多個相關聯的檢查條件約束。However, a column can have both a rule and one or more check constraints associated with it. 在這個情況下,會評估所有限制。When this is true, all restrictions are evaluated.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax


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

引數Arguments

schema_nameschema_name
這是規則所屬的結構描述名稱。Is the name of the schema to which the rule belongs.

rule_namerule_name
這是新規則的名稱。Is the name of the new rule. 規則名稱必須符合識別碼的規則。Rule names must comply with the rules for identifiers. 您可以選擇性地指定規則擁有者名稱。Specifying the rule owner name is optional.

condition_expressioncondition_expression
這是定義規則的一個或多個條件。Is the condition or conditions that define the rule. 規則可以是 WHERE 子句中任何有效的運算式,可以包括算術運算子、關係運算子和述詞 (如 IN、LIKE、BETWEEN) 之類的元素。A rule can be any expression valid in a WHERE clause and can include elements such as arithmetic operators, relational operators, and predicates (for example, IN, LIKE, BETWEEN). 規則不能參考資料行或其他資料庫物件。A rule cannot reference columns or other database objects. 未參考資料庫物件的內建函數可以包括在內。Built-in functions that do not reference database objects can be included. 無法使用使用者自訂函數。User-defined functions cannot be used.

condition_expression包括一個變數。condition_expression includes one variable. 每個區域變數前面都會有 @ 記號 (@)。The at sign (@) precedes each local variable. 這個運算式參考 UPDATE 或 INSERT 陳述式所輸入的值。The expression refers to the value entered with the UPDATE or INSERT statement. 當建立規則時,您可以使用任何名稱或符號來代表值,但第一個字元必須是 @ 記號 (@)。Any name or symbol can be used to represent the value when creating the rule, but the first character must be the at sign (@).

注意

請避免建立使用別名資料型別之運算式的規則。Avoid creating rules on expressions that use alias data types. 雖然您可以建立使用別名資料型別之運算式的規則,但在規則繫結到資料行或別名資料型別之後,當參考運算式時,會無法編譯運算式。Although rules can be created on expressions that use alias data types, after binding the rules to columns or alias data types, the expressions fail to compile when referenced.

RemarksRemarks

CREATE RULE 無法在單一批次中,與其他 Transact-SQLTransact-SQL 陳述式結合起來。CREATE RULE cannot be combined with other Transact-SQLTransact-SQL statements in a single batch. 規則不適用於建立規則時已在資料庫中的資料,規則無法繫結到系統資料類型。Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types.

規則只能建立在目前資料庫中。A rule can be created only in the current database. 建立好規則之後,請執行 sp_bindrule,將規則繫結到資料行或別名資料類型。After you create a rule, execute sp_bindrule to bind the rule to a column or to alias data type. 規則必須相容於資料行資料類型。A rule must be compatible with the column data type. 例如, "@value LIKE A%" 不能用來作為數值資料行的規則。For example, "@value LIKE A%" cannot be used as a rule for a numeric column. 規則無法繫結到 textntextimagevarchar (max)nvarchar(max)varbinary(max)xml、CLR 使用者定義型別或 timestamp 資料行。A rule cannot be bound to a text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestampcolumn. 規則無法繫結到計算資料行。A rule cannot be bound to a computed column.

請用單引號 (') 括住字元和日期常數,二進位常數前面要附加 0x。Enclose character and date constants with single quotation marks (') and precede binary constants with 0x. 如果規則與所繫結的資料行不相容, SQL Server Database EngineSQL Server Database Engine 會在插入值時 (而不是繫結規則時),傳回一則錯誤訊息。If the rule is not compatible with the column to which it is bound, the SQL Server Database EngineSQL Server Database Engine returns an error message when a value is inserted, but not when the rule is bound.

只有在您試圖將值插入別名資料型別的資料庫資料行或更新這個資料行時,才會啟動繫結到別名資料型別的規則。A rule bound to an alias data type is activated only when you try to insert a value into, or to update, a database column of the alias data type. 由於規則不會測試變數,因此,不會將值指派給繫結到相同資料類型之規則所拒絕的別名資料型別變數。Because rules do not test variables, do not assign a value to an alias data type variable that would be rejected by a rule that is bound to a column of the same data type.

若要取得規則的報表,請使用 sp_helpTo get a report on a rule, use sp_help. 若要顯示規則的文字,請將規則名稱設為參數來執行 sp_helptextTo display the text of a rule, execute sp_helptext with the rule name as the parameter. 若要重新命名規則,請使用 sp_renameTo rename a rule, use sp_rename.

在建立同名的新規則之前,您必須先使用 DROP RULE 來卸除舊規則,在卸除舊規則之前,必須先使用 sp_unbindrule 來解除它的繫結。A rule must be dropped by using DROP RULE before a new one with the same name is created, and the rule must be unbound byusing sp_unbindrule before it is dropped. 若要解除規則和資料行的繫結,請使用 sp_unbindruleTo unbind a rule from a column, use sp_unbindrule.

您可以在未解除先前繫結的情況下,將新規則繫結到資料行或資料類型;新規則會覆寫先前的規則。You can bind a new rule to a column or data type without unbinding the previous one; the new rule overrides the previous one. 繫結到資料行的規則,一律優先於繫結到別名資料型別的規則。Rules bound to columns always take precedence over rules bound to alias data types. 將規則繫結到資料行,會取代已繫結到這個資料行的別名資料型別之規則。Binding a rule to a column replaces a rule already bound to the alias data type of that column. 但將規則繫結到資料類型,並不會取代繫結到這個別名資料型別之資料行的規則。But binding a rule to a data type does not replace a rule bound to a column of that alias data type. 下表顯示將規則繫結到資料行,或繫結到規則已存在的別名資料型別時,所採用的優先順序。The following table shows the precedence in effect when rules are bound to columns and to alias data types on which rules already exist.

新規則繫結到New rule bound to 舊規則繫結到Old rule bound to

別名資料型別alias data type
舊規則繫結到Old rule bound to

「資料行」Column
別名資料型別Alias data type 取代舊規則Old rule replaced 沒有變更No change
「資料行」Column 取代舊規則Old rule replaced 取代舊規則Old rule replaced

如果資料行有預設值及其相關聯的規則,預設值必須在規則所定義的網域內。If a column has both a default and a rule associated with it, the default must fall within the domain defined by the rule. 永遠不會插入與規則衝突的預設值。A default that conflicts with a rule is never inserted. SQL Server Database Engine 每次嘗試插入這類預設值時,都會產生一則錯誤訊息。The SQL Server Database Engine generates an error message each time it tries to insert such a default.

[權限]Permissions

若要執行 CREATE RULE,使用者至少必須有目前資料庫中的 CREATE RULE 權限,以及正在建立規則之結構描述的 ALTER 權限。To execute CREATE RULE, at a minimum, a user must have CREATE RULE permission in the current database and ALTER permission on the schema in which the rule is being created.

範例Examples

A.A. 利用範圍來建立規則Creating a rule with a range

下列範例會建立一個規則來限制插入這個規則所繫結之一個或多個資料行的整數範圍。The following example creates a rule that restricts the range of integers inserted into the column or columns to which this rule is bound.

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

B.B. 利用清單來建立規則Creating a rule with a list

下列範例會建立一個規則,將輸入 (這個規則所繫結的) 資料行之實際值限制在規則所列出的值。The following example creates a rule that restricts the actual values entered into the column or columns (to which this rule is bound) to only those listed in the rule.

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

C.C. 利用模式建立規則Creating a rule with a pattern

下列範例會建立一個遵照下列模式的規則:任兩個字元後面接著連字號 (-),再接著任意數目的字元或不接任何字元,再以 09 的整數做為結尾。The following example creates a rule to follow a pattern of any two characters followed by a hyphen (-), any number of characters or no characters, and ending with an integer from 0 through 9.

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

另請參閱See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE DEFAULT (Transact-SQL) CREATE DEFAULT (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DROP DEFAULT (Transact-SQL) DROP DEFAULT (Transact-SQL)
DROP RULE (Transact-SQL) DROP RULE (Transact-SQL)
運算式 (Transact-SQL) Expressions (Transact-SQL)
sp_bindrule (Transact-SQL) sp_bindrule (Transact-SQL)
sp_help (Transact-SQL) sp_help (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
sp_rename (Transact-SQL) sp_rename (Transact-SQL)
sp_unbindrule (Transact-SQL) sp_unbindrule (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)