Use check constraints in SQL Server to verify data

What are check constraints?

A check constraint is a rule that confirms the acceptable Field Values of data in a record in an SQL Server table. Check constraints to help execute domain integrity. Domain integrity defines the valid values of fields in a database table. Check constraints can verify the integrity of a single field or some fields. You can check the integrity of a single field. If the inserted or updated data violates a check constraint, the database engine will not allow this insert or update operation.

Check that the constraint contains a logical expression used to determine what a valid expression is. A logical expression may be a separate expression, such as "salary <200000.00" or multiple expressions, such as "salesaldate> getdate () and salesaldate <dateadd (YY, 1, getdate ()) ". If a check constraint of a logical expression returns a value of false, this check constraint will restrict data insertion or update in this table. All records that return values other than false in the logical expression will pass this check constraint and allow the record to be updated or inserted. In order for this record to be inserted or updated, all data related to the given insert or update statement cannot be checked and the constraints fail (a false value is returned ). Check constraints can be created at the field or table level.

Create check constraints on a create table statement

One way to create a check constraint is to create a table. This is a simple create table script, which creates a separate check constraint:

Create Table DBO. payroll ( Id int primary key, Positionid int, Salarytype nvarchar (10 ), Salary decimal (9, 2) Check (salary <150000.00) );

Here I have a check clause, which is associated with the salary field. This is a field-level constraint. If you create a field-level constraint, you can only use this field name in the logic expression of your check constraint. This check constraint only allows the salary field to be lower than $150,000.00. After a table is created, the check constraint is also created and assigned a name for the constraint generated by the system. If you want to name your check constraints during a create table operation, you can run the followingCode:

Create Table DBO. payroll ( Id int primary key, Positionid int, Salarytype nvarchar (10 ), Salary decimal (9, 2) Constraint ck_payroll_salary check (salary <150000.00) );

Here I named my check constraint ck_payroll_salary.

Each example above creates a separate condition field check constraint. A check constraint expression can have multiple conditions. The following is an example showing a check constraint with multiple conditions:

Create Table DBO. payroll ( Id int primary key, Positionid int, Salarytype nvarchar (10 ), Salary decimal (9, 2) Constraint ck_payroll_salary Check (salary> 10.00 and salary <150000.00) );

Remember, in order for SQL Server to reject a record, the final result of the logic expression of this check constraint must be false. Therefore, in this example, this check constraint verifies that a salary is greater than $10.00 and less than $150,000.00. When either of these conditions in this check constraint is false, a record is not inserted or updated in the payroll table and an error message is displayed.

To create a table-Level Check constraint, run the following code:

Create Table DBO. payroll ( Id int primary key, Positionid int, Salary decimal (9, 2 ), Salarytype nvarchar (10 ), Check (salary> 10.00 and salary <150000.00) );

Here I created a separate table constraint. It checks the salary field, but it is not associated with the field, but associated with the table. In this check constraint, I can use any field in my table, as long as I want, because it is a table check constraint, but in my example, I only use the salary field. Note that this check clause generates a check constraint name for SQL Server, because I did not give this constraint name.

Create a check constraint on an existing table

Sometimes, after you design and create a table, you want to add a check constraint to the table. This can be done by using the alter table statement. The following is an example:

Alter table dbo. payroll With nocheck add constraint ck_payroll_salarytype Check (salarytype in ('hourly ', 'monthly', 'annual '));

Here I created a check constraint that will check all records in my payroll table only have "hourly", "Monthly", or "annual" values in the salarytype field. I also named my check constraint with a name. In this example, it is "ck_payroll_salarytype ".

You can use a separate alter table statement to add multiple check constraints to your table at a time. The following is an example:

Alter table dbo. payroll With nocheck add constraint ck_payroll_salarytype Check (salarytype in ('hourly ', 'monthly', 'annual ')), Constraint ck_payroll_salary Check (salary> 10.00 and salary <150000.00 );

Here I have used a separate add constraint clause to add salarytype and salary constraints.

Create multiple field constraints

You do not have to create constraints that can only check the values of a single field. You can create a constraint to check the values in multiple fields at a time. For example, if you want to create a separate constraint to check the preceding salary and salarytype constraints, you can use the following code:

Alter table dbo. payroll with nocheck Add constraint ck_payroll_salary_n_salarytype Check (salarytype in ('hourly ', 'monthly', 'annual ') And salary> 10.00 and salary <150000.00 );

The independent constraint is the same as the preceding two constraints. Remember, when you do this, it is more difficult to know whether salarytype, salary, or both fields violate your check constraints ..

Another method in the previous example is to use this value in more than one field to determine whether a specified field value is valid. For example, suppose I want to make sure that when I enter a "hourly" salarytype, I want salary to be less than $100.00, or when I enter "Monthly" salarytype, salary cannot exceed $10,000, when an "annual" salarytype is input, any salary value can be used. To implement this constraint, I use the following add constraint clause:

Alter table dbo. payroll with nocheck Add constraint ck_payroll_salarytype_based_on_salary Check (salarytype = 'hourly 'and salary <100.00) or (Salarytype = 'monthly 'and salary <10000.00) or (Salarytype = 'annual '));

Here, I use multiple field conditions together and use an "or" condition to separate them, so my check constraints can verify the salary quantity of each different salarytype.

Understand what will happen when the value is null

Recall in this articleArticleIn the "What is a check constraint" section about how a record is considered not to pass the check constraint only when the result of the check constraint is false. Therefore, the null value in the field may allow you to input data to your database, which does not meet your needs. Suppose I only have ck_paryroll_salarytype check constraints on my payroll table. Here we need to recall this check constraint:

Alter table dbo. payroll With nocheck add constraint ck_payroll_salarytype Check (salarytype in ('hourly ', 'monthly', 'annual '));

Run the following insert statement:

Insert into DBO. payroll values (1, 1, 'urly ', 25.00 );

Insert into DBO. payroll values (2, 2, null, 25.00 );

Insert into DBO. payroll values (3, 3, 'horly, 25.00 );

what will happen? Will only the first insert statement work? What will happen to the second and third insert statements? Are they against ck_payroll_salarytype? The result is that only the third insert statement fails. It fails because the salarytype is incorrect. It is not "hourly", "Monthly", or "annual ". Why does the second insert fail to get false? Obviously, "null" is not the valid value of salarytypes. The reason why the second insert statement works is that when the second insert statement is run, the ck_payroll_salarytype constraint is not false. Therefore, the database engine inserts this record. So why is this happening? This is because when a null value is used in a comparison operation, it is treated as unknown. The check constraint is not violated because unknown is not false. Therefore, when writing your check constraints, you need to be cautious when you need to reject null values. Another way to write the above constraints so that the salarytype value is denied to be null is to write your constraints as follows:

Alter table dbo. payroll With nocheck add constraint ck_payroll_salarytype Check (salarytype in ('hourly ', 'monthly', 'annual ')) And salarytype is not null );

Another option is to make salarytype a non-null field. If you do this, you will not violate the check constraints, but in turn you will get an error message indicating that you cannot insert a null value to your table.

Verify data by checking Constraints

By using check constraints, you can ensure that your database only contains data that has passed the constraints. This allows the database engine to control your data verification. This will make your applicationProgramYou do not need to write the data validation rule code for each record you want to insert or update to a table. Check constraints are a simple method for performing data verification.