Learning tutorials for CHECK constraints in SQL Server _mssql

0. What is a check constraint?

A check constraint adds additional restrictions to a column in a table.

NOTE: The check constraint cannot be defined in view. A check constraint can only be defined in a column that must be contained in the specified table. A check constraint cannot contain a subquery.

Define a CHECK constraint when creating a table

1.1 Syntax:

CREATE TABLE table_name
(
  column1 datatype null/not null, COLUMN2 datatype null/not
  null,
  ...
  ) CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);

Among them, the key to disable is the option. If the Disable keyword is used, the restriction condition for the CHECK constraint does not take effect when the check constraint is created.

1.2 Example 1: Numeric range validation

CREATE TABLE Tb_supplier
(
 supplier_id number    ,
 supplier_name   varchar2),
 contact _name   varchar2,/
 * Defines a CHECK constraint that is validated when the field supplier_id is inserted or updated, and is triggered when the condition is not satisfied. * *
 CONSTRAINT check_tb_supplier_id Check (supplier_id BETWEEN and 9999)
);

Verify:
Inserting supplier_id in a table satisfies the condition and does not meet the condition:

--SUPPLIER_ID satisfies the check constraint, this record can successfully insert into
tb_supplier values (' DLT ', ' Stk ');
 
--SUPPLIER_ID does not meet the CHECK constraint, this record can insert a failure and prompts for related errors as follows
INSERT into tb_supplier values (1, ' David Louis Tian ', ' Stk ');

Error prompts that do not meet the criteria:

Error-
SQL error:ora-02290:check constraint (502351838.check_tb_supplier_id) violated
02290.00000-"C Heck constraint (%s.%s) violated "
*cause: The values being inserted do not  satisfy the named check

1.3 Example 2: Force the letter of the inserted column to be uppercase

CREATE TABLE Tb_products
(
 product_id number not    null,
 product_name   varchar2 (m) not NULL,
 supplier_id number    is not NULL,/
 * defines a check constraint check_tb_products, which is used to restrict the inserted product name to an uppercase letter * *
 CONSTRAINT Check_tb_products
 CHECK (product_name = UPPER (product_name))
);

Verify:
Inserting product_name in a table satisfies the condition and does not meet the condition:

--product_name satisfies the check constraint, this record can successfully insert into
tb_products values (2, ' LENOVO ', ' 2 ');
--product_name does not meet the CHECK constraint, this record can insert a failure and prompts for related errors as follows
INSERT into tb_products values (1, ' IPhone ', ' 1 ');

Error prompts that do not meet the criteria:

SQL error:ora-02290:check constraint (502351838.check_tb_products) violated
02290.00000-"Check constraint" (%s.%s ) violated "
*cause: The  values being inserted do not satisfy the named check

2. ALTER table defines a check constraint

2.1 Grammar

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

Among them, the key to disable is the option. If the Disable keyword is used, the restriction condition for the CHECK constraint does not take effect when the check constraint is created.

2.2 Sample Preparation

drop table tb_supplier;
--Create
the   Instance table Tb_supplier (supplier_id number, supplier_name varchar2)
 Contact_Name   varchar2
);

2.3 Create a CHECK constraint

--Create a CHECK constraint
ALTER TABLE tb_supplier
add constraint check_tb_supplier
check (supplier_name in (' IBM ', ' LENOVO ', ' Microsoft '));

2.4 Verification

--supplier_name satisfies the check constraint, this record can successfully insert into
tb_supplier values (1, ' IBM ', ' US ');
 
--supplier_name does not meet the CHECK constraint, this record is able to insert a failure and prompts for related errors as follows
INSERT into tb_supplier values (1, ' DELL ', ' HO ');

Error prompts that do not meet the criteria:

SQL error:ora-02290:check constraint (502351838.check_tb_supplier) violated
02290.00000-"Check constraint" (%s.%s ) violated "
*cause: The  values being inserted do not satisfy the named check

3. Enable CHECK constraints

3.1 Grammar

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

3.2 Example

drop table tb_supplier;
--Rebuild table and CHECK constraint
CREATE table Tb_supplier
(supplier_id number    ,
 supplier_name   VARCHAR2 (M),
 contact_name   varchar2,/
 * Define a CHECK constraint, which takes effect after it has been enabled * *
 CONSTRAINT check_tb_ supplier_id CHECK (supplier_id BETWEEN and 9999) DISABLE
);
 
--Enable constraint
ALTER TABLE tb_supplier enable CONSTRAINT check_tb_supplier_id;

3.3 Improving performance with CHECK constraints

In SQL Server, the execution of an SQL statement relies on the execution plan generated by the query optimizer, and the execution plan is directly related to execution performance.

In the query optimizer generation execution plan, reference metadata is needed to generate an efficient execution plan as much as possible, so the more metadata is, the more likely the execution plan is to be efficient. The so-called need to refer to the metadata mainly include: index, table structure, statistics and so on, but there are not very noticeable metadata, including the check constraints described in this article.
Figure 1. Simple query
The query optimizer has a phase called algebraic tree optimization before generating execution plans, such as the following simple query:

The query optimizer realizes that the 1=2 condition is never equal, so there is no need to return any data and therefore there is no need to scan the table, since the execution plan from Figure 1 shows that the query can be completed only after the constants have been identified after the 1=2 is always false.

So what about check constraints?

A check constraint ensures that the values of one or more columns conform to the constraints of an expression. At some point, a check constraint can also provide information to the optimizer to optimize performance, such as looking at figure II's example.

Figure 2. Columns with CHECK constraints enhance query performance

Figure 2 is a simple example, and sometimes applying a check constraint in a partitioned view can also improve performance by testing the code as follows:

CREATE TABLE [dbo]. [Test2007] ([PRODUCTREVIEWID] [int] IDENTITY (1,1) not NULL, [reviewdate] [datetime] NOT NULL) On [PRIMARY] go ALTER TABLE [dbo]. [Test2007] With check ADD CONSTRAINT [ck_test2007] Check ([reviewdate]>= ' 2007-01-01 ' and [reviewdate] ' 2007-12-31 ')] go ALTER TA BLE [dbo]. [Test2007] CHECK CONSTRAINT [ck_test2007] Go CREATE TABLE [dbo]. [Test2008] ([PRODUCTREVIEWID] [int] IDENTITY (1,1) not NULL, [reviewdate] [datetime] NOT NULL) On [PRIMARY] go ALTER TABLE [dbo]. [Test2008] With check ADD CONSTRAINT [ck_test2008] Check ([reviewdate]>= ' 2008-01-01 ' and [Productreviewid] ' 2008-12-31 ')] go ALT ER TABLE [dbo]. [Test2008] CHECK CONSTRAINT [ck_test2008] go inserts into [Test2008] values (' 2008-05-06 ') inserts into [Test2007] values (' 2007-05-06 ') 
CREATE VIEW Testpartitionview as SELECT * FROM Test2007 UNION select * to Test2008 SELECT * from Testpartitionview where [reviewdate]= ' 2007-01-01 ' SELECT * from Testpartitionview where [reviewdate]= ' 2008-01-01 ' SELECT * from Testpartitionview WHERE [reviewdate]= ' 2010-01-01 '

 

We made a partitioned view of the tables that are identical to the Test2007 and Test2008 two table structures. A check constraint is made on the date column, restricting that each table contains data that is within a specific year. When we query the view and give different filter criteria, we see the result as shown in Figure 3.

Fig. 3. Different conditions produce different execution plans
As can be seen from Figure 3, when the filter condition is 2007 years, automatically scans only the 2007-year table, the 2008 table is the same. When the query scope exceeds the CHECK constraint of 2007 and 2008, the query optimizer automatically determines that the result is empty and therefore does not do any IO operations, thereby improving performance.
Conclusion
In cases where the check constraint is simple (meaning that the constraint is in a single column and the expression does not contain a function), not only can the data integrity be constrained, but it can also be provided to the query optimizer information in many cases to enhance performance.

4. Disable CHECK constraint

4.1 Grammar

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

4.2 Example

--Disables the constraint
ALTER TABLE tb_supplier DISABLE CONSTRAINT check_tb_supplier_id;

5. Constraint Details view
statement:

--View constraint details
select
constraint_name,--constraint name
constraint_type,--Constraint type
table_name the table
in which the constraint is,-- search_condition,--Constraint expression
status--enable from
user_constraints--[all_constraints|dba_constraints]
where constraint_name= ' check_tb_supplier_id ';

6. Delete Check Constraint
6.1 Grammar

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

6.2 Example

ALTER TABLE tb_supplier
DROP CONSTRAINT check_tb_supplier_id;