Oracle's CHECK constraint examples are detailed

Oracle | PL/SQL Check constraint usage detailed

1. Targetexamples of how check constraints (Create, enable, disable, and delete) are used in Oracle2. What is a check constraint? A check constraint refers to adding additional restrictions to a table's columns. Note:

• Check constraints cannot be defined in view.
• A check constraint can only define columns that must be included in the table that you specify.
• A check constraint cannot contain subqueries.

3. Defining a Check constraint when creating a table3.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, disable is the key to the option. If the Disable keyword is used, the check constraint's restrictions do not take effect when the check constraint is created.

3.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 fires when the condition is not met. *  /CONSTRAINT check_tb_supplier_id Check (supplier_id between and 9999));

Verify:
Inserting supplier_id in the table satisfies the condition and does not satisfy the condition two kinds of conditions:

--SUPPLIER_ID satisfies the check constraint, this record can be successfully inserted into the INSERT into tb_supplier values (+, ' DLT ', ' Stk '),--supplier_id does not meet the check constraint, This record is capable of inserting failures and prompting related errors as follows insert into tb_supplier values (1, ' David Louis Tian ', ' Stk ');

Error message that does not meet the criteria:

Error report-sql error:ora-02290:check constraint (502351838.check_tb_supplier_id) violated02290. 00000-  "Check constraint (%s.%s) violated" *cause: the    values being inserted do not satisfy the named check

3.3 Example 2: Forcibly inserting a column with uppercase letters

CREATE TABLE tb_products (  product_id number not        null,  product_name      varchar2 () is not NULL,  SUPPLIER_ID number       is not NULL,/  * Defines the CHECK constraint check_tb_products, and the purpose is to restrict the inserted product name to a capital letter */  CONSTRAINT check_tb_ Products  CHECK (product_name = UPPER (product_name)));

Verify:
Inserting product_name in the table satisfies the condition and does not satisfy the condition two kinds of conditions:

--product_name satisfies the check constraint, this record successfully inserts INSERT INTO tb_products values (2, ' LENOVO ', ' 2 '),--product_name does not satisfy the check constraint, This record is capable of inserting failures and prompting related errors as follows insert into tb_products values (1, ' IPhone ', ' 1 ');

Error message that does not meet the criteria:

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

4. ALTER table defines a check constraint4.1 Syntax

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

Among them, disable is the key to the option. If the Disable keyword is used, the check constraint's restrictions do not take effect when the check constraint is created.

4.2 Sample Preparation

drop table tb_supplier;--Creating instance table create Table Tb_supplier (  supplier_id number       ,  supplier_name     varchar2 (a),  contact_name      varchar2 (60));

4.3 creating a Check constraint

--Create CHECK constraint ALTER TABLE TB_SUPPLIERADD constraint Check_tb_suppliercheck (supplier_name in (' IBM ', ' LENOVO ', ' Microsoft ' ));

4.4 Verification

--supplier_name satisfies the check constraint, this record can be successfully inserted into the INSERT into tb_supplier values (1, ' IBM ', ' US '),--supplier_name does not meet the check constraint, This record is capable of inserting failures and prompting related errors as follows insert into tb_supplier values (1, ' DELL ', ' HO ');

error message that does not meet the criteria:

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

5. Enable CHECK constraints5.1 Syntax

ALTER TABLE table_nameenable CONSTRAINT constraint_name;

5.2 Example

DROP table tb_supplier;--rebuild table and check constraint CREATE TABLE tb_supplier (  supplier_id number       ,  supplier_name     VARCHAR2,  contact_name      varchar2,/  * Define a CHECK constraint, which takes effect after it is 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;

6. Disabling CHECK constraints

6.1 Syntax

ALTER TABLE table_namedisable CONSTRAINT constraint_name;

6.2 Example

--Disable the constraint alter TABLE tb_supplier DISABLE CONSTRAINT check_tb_supplier_id;

7. Constraint Details view
Statement:

--View details of the constraint select constraint_name,--constraint name constraint_type,--constraint type table_name The table search_condition where the constraint is,--,-- Constraint expression status--whether the from User_constraints--[all_constraints|dba_constraints]where constraint_name= ' CHECK_TB_ is enabled Supplier_id ';

8. Delete a check constraint8.1 Syntax

ALTER TABLE table_namedrop CONSTRAINT constraint_name;

8.2 Example

ALTER TABLE tb_supplierdrop CONSTRAINT check_tb_supplier_id;

---------------------------------------------------------------------------------------------------------

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint Please indicate the source, otherwise investigate legal responsibility!
----------------------------------------------------------------------------------------------------------

Oracle's CHECK constraint examples are detailed