Oracle Check Constraints

Oracle Check Constraints

Oracle | PL/SQL Check constraints usage

1. The target instance explains how to use the CHECK constraint (create, enable, disable, and delete) in Oracle. 2. What is the Check constraint? The CHECK constraint adds additional constraints to the columns of the table. Note:

• CHECK constraints cannot be defined in VIEW.
• Only the columns defined by the CHECK constraint must be included in the specified table.
• The CHECK constraint cannot contain subqueries.

3. Define the CHECK constraint 3.1 syntax when creating a table:

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 constraints of the CHECK constraint will not take effect after the CHECK constraint is created.

3.2 Example 1: Numerical range Verification

Create table tb_supplier (supplier_id number, supplier_name varchar2 (50), contact_name varchar2 (60),/* defines the CHECK constraint, which is verified when the field supplier_id is inserted or updated, triggered when the condition is not met. */CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999 ));

Verification:
Insert supplier_id in the table to meet the conditions and not meet the conditions:

-- Supplier_id meets the check constraints. This record can be inserted into tb_supplier values (200, 'dlt', 'stk'); -- supplier_id does not meet the check constraints, this record fails to be inserted and prompts the following error: insert into tb_supplier values (1, 'David louis tianc', 'stk ');

Error message that does not meet the conditions:

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 insert columns with uppercase letters

Create table tb_products (product_id number not null, product_name varchar2 (100) not null, supplier_id number not null,/* defines the CHECK constraint check_tb_products, the purpose is to restrict the inserted product names to uppercase letters */CONSTRAINT check_tb_products CHECK (product_name = UPPER (product_name )));

Verification:
Insert product_name in the table to meet the following conditions:

-- Product_name meets the check constraints. This record can be inserted into tb_products values (2, 'lenovo ', '2'); -- product_name does not meet the check constraints, this record fails to be inserted and prompts the following error: insert into tb_products values (1, 'iphone ', '1 ');

Error message that does not meet the conditions:

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 the CHECK constraint 4.1 syntax

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

Among them, the key to DISABLE is the option. If the DISABLE keyword is used, the constraints of the CHECK constraint will not take effect after the CHECK constraint is created.

4.2 Sample Preparation

Drop table tb_supplier; -- create table tb_supplier (supplier_id number, supplier_name varchar2 (50), contact_name varchar2 (60 ));

4.3 create CHECK Constraints

-- Create the check constraint alter table tb_supplieradd constraint check_tb_suppliercheck (supplier_name IN ('ibm ', 'lenovo', 'Microsoft '));

4.4 Verification

-- Supplier_name meets the check constraints. This record can be inserted into tb_supplier values (1, 'ibm ', 'us'); -- supplier_name does not meet the check constraints, this record fails to be inserted, and the following error is prompted: insert into tb_supplier values (1, 'Dell ', 'Ho ');

Error message that does not meet the conditions:

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 constraint 5.1 syntax

ALTER TABLE table_nameENABLE CONSTRAINT constraint_name;

5.2 example

Drop table tb_supplier; -- create table tb_supplier (supplier_id number, supplier_name varchar2 (50), contact_name varchar2 (60),/* defines CHECK constraints, this CONSTRAINT takes effect after it is enabled */CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999) DISABLE); -- ENABLE the constraint alter table tb_supplier enable constraint check_tb_supplier_id;

6. Disable 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. View constraint details
Statement:

-- View constraints details select constraint_name, -- constraint name constraint_type, -- constraint type table_name, -- constraint table search_condition, -- constraint expression status -- whether to enable from user_constraints -- [all_constraints | dba_constraints] where constraint_name = 'check _ TB_SUPPLIER_ID ';

8. Delete the CHECK constraint 8.1 syntax

ALTER TABLE table_nameDROP CONSTRAINT constraint_name;

8.2 example

ALTER TABLE tb_supplierDROP CONSTRAINT check_tb_supplier_id;

Bytes ---------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: indicate the source for reprinting. Otherwise, you will be held legally responsible!
Bytes ----------------------------------------------------------------------------------------------------------