SQL Server Check constraints

I. Definition:

The CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint for a single column, this column only allows specific values.

If you define a CHECK constraint for a table, this constraint limits the value in a specific column.

 

As a database, you must write constraints.

Operator		Description
=		Equal
>		Greater
<		Less
> =		Greater than or equal
<=		Less than or equal
<>		Not equal
!		Non(JavaThe language is not.)
Wildcard	Explanation		Example
'_'	One character		A Like 'c _'
%	String of any length		B Like 'co _ %'
[]	A character in the specified range in parentheses		C Like '9w0 [1-2]'
[^]	A character that is not within the specified range in parentheses.		D Like '% [A-D] [^ 1-2]'

2. Attach the new CHECK constraint to a table or column
1. Add new CHECK constraints
(1) in the database graph, right-click the table containing constraints and select the constraint command from the shortcut menu.
-Or-

Open the table designer for a table that contains constraints, right-click the table designer, and select the constraint command from the shortcut menu.

(2) select the "new" command. The "selected constraints" box displays the new constraint names assigned by the system. The name assigned by the system starts with "CK _", followed by the table name.


In the constraint expression box, type an SQL expression for the CHECK constraint. For example, to restrict the input of the state column in the authors table to New York, type:
State = 'NY'
Alternatively, if you want the input in the zip column to be 5-digit, type:

Zip LIKE '[0-9] [0-9] [0-9] [0-9] [0-9]' cannot contain spaces

To provide a different name for the constraint, enter a name in the constraint name box.


Use the check box to control when to force constraints:

To test the constraints on existing data before creating a constraint, select the check existing data in creation check box.


To force constraints when a copy operation occurs in the table, select the "force constraints on replication" check box.


To INSERT or UPDATE row-based mandatory constraints in the table, select the "force INSERT and UPDATE constraints" check box.

2. Define the CHECK constraint expression

When you attach a CHECK constraint to a table or column, you must include an SQL expression.

You can create a simple constraint expression to check data under simple conditions, or use a Boolean operator to create a complex constraint expression to check data under multiple conditions. For example, assume that the authors table has a zip column, which requires a string of five digits. The following example constraints make sure that only five digits are allowed:

Zip LIKE '[0-9] [0-9] [0-9] [0-9] [0-9]'
Or assume that the sales table has a column named qty, which must be greater than 0. The following example constraints ensure that only positive values are allowed:

Qty> 0
Alternatively, assume that the orders table limits the acceptable credit card types for all credit card orders. The following example constraints ensure that only Visa, MasterCard, or American Express is accepted if an order is issued by credit card:

NOT (payment_method = 'credit card ') OR
(Card_type IN ('visa ', 'MasterCard', 'American EXPRESS '))

3. Define a constraint expression

Create a new CHECK constraint.

On the "CHECK constraints" tab of the property page, use the following syntax to type an expression in the "constraint expressions" box:

{Constant | column_name | function | (subquery )}
[{Operator | AND | OR | NOT}
{Constant | column_name | function | (subquery)}...]
 

II. Modify CHECK constraints
Modify the CHECK constraint when you want to change the constraint expression or the option to enable or disable the constraint for a specific condition.

I. In the database relationship diagram, right-click the table containing constraints and select the "attribute" command from the shortcut menu.
-Or-

Open the table designer for a table that contains constraints, right-click the table designer, and select the "attribute" command from the shortcut menu.

II. Select the CHECK constraints tab.

III. Select the constraint to be modified from the selected constraints list.
IV. Fill in the modification rules.

When you save a table or graph, the constraints are updated in the database.

III. Delete CHECK constraints

Delete the CHECK constraint when you want to delete the restriction on the data values of the columns contained in the constraint expression.


I. In the database relationship diagram, right-click the table containing constraints and select the "attribute" command from the shortcut menu.
-Or-

Open the table designer for a table that contains constraints, right-click the table designer, and select the "attribute" command from the shortcut menu.

II. Select the CHECK constraints tab.

III. Select constraints from the selected constraints list.

IV. Select "delete.

Note that selecting the "delete" button will lead to an unrecoverable operation and will not save all other changes made to the database relationship diagram. To cancel this operation, disable the current database relationship diagram and all other open database relationships without saving the changes.

When a table or graph is saved, the constraint is deleted from the database.

 How do I write a binding expression for a certificate number?

.......

Logical expression	Description	Example
AND	Logic and	1 AND 1 =; 1 AND 0 = 0; 0 AND 0 = 0;
OR	Logic or	1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;
NOT	Non-logical	NOT 1 = 0; NOT 0 = 1;

 

 

Right-click check constraint in the selected table -- add constraint -- write regular expression (sage indicates a field name and is the value of this field)

Sage. value> 15 & sage. value <31

Sage. value = sage

>=>

& Amp; = and

Sage> 15 and sage <31

Restricted phone number format: 13 .......... 15 ......18 .......... 147 .......

If (stel like 13 ....)

Stel like '[1]' indicates that the first character of the stel phone value is 1 and can only be 1 character.

([Stel] like '[1] [3] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9- 9] [0-9] [0-9] [0-9]'

OR [stel] like '[1] [5] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9- 9] [0-9] [0-9] [0-9] ')

                     

Verify email

Sqqemail like '[^ 0-9] % [@] [q] [q] [.] [c] [o] [m]'

It indicates that the first character cannot be a number, and the first character and @ symbol are any characters. Fixed format