Oracle | PL/SQL Check constraints usage

Oracle | PL/SQL Check constraints usage

1. Objectives

The instance explains how to use CHECK constraints (create, enable, disable, and delete) in Oracle)

2. What are Check constraints?

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 CHECK constraints when creating a table

3.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 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 ),
/* Define the CHECK constraint. This constraint is verified when the supplier_id field is inserted or updated. It is 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 successfully.
Insert into tb_supplier values (200, 'dlt', 'stk ');

-- Supplier_id does not meet the check constraints. This record cannot be inserted and the following error is prompted:
Insert into tb_supplier values (1, 'David louis tid', 'stk ');

Error message that does not meet the conditions:

Error report-
SQL Error: ORA-02290: check constraint (502351838. CHECK_TB_SUPPLIER_ID) violated
02290. 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,
/* Define 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 successfully.
Insert into tb_products values (2, 'lenovo ', '2 ');
-- Product_name does not meet the check constraints. This record cannot be inserted and the following error message is displayed:
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) violated
02290. 00000-"check constraint (% s. % s) violated"
* Cause: The values being inserted do not satisfy the named check

4. alter table defines CHECK Constraints
4.1 syntax
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 constraints of the CHECK constraint will not take effect after the CHECK constraint is created.

4.2 Sample Preparation
Drop table tb_supplier;
-- Create an instance table
Create table tb_supplier
(
Supplier_id number,
Supplier_name varchar2 (50 ),
Contact_name varchar2 (60)
);

4.3 create CHECK Constraints

-- Create check Constraints
Alter table tb_supplier
Add constraint check_tb_supplier
Check (supplier_name IN ('ibm ', 'lenovo', 'Microsoft '));

4.4 Verification

-- Supplier_name meets the check constraints. This record can be inserted successfully.
Insert 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 message is displayed:
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) violated
02290. 00000-"check constraint (% s. % s) violated"
* Cause: The values being inserted do not satisfy the named check

5. Enable CHECK Constraints

5.1 syntax
Alter table table_name
Enable constraint constraint_name;

5.2 example

Drop table tb_supplier;
-- Recreate the table and CHECK Constraints
Create table tb_supplier
(
Supplier_id number,
Supplier_name varchar2 (50 ),
Contact_name varchar2 (60 ),
/* Define the CHECK constraint, which will take effect after being enabled */
CONSTRAINT check_tb_supplier_id CHECK (supplier_id BETWEEN 100 and 9999) DISABLE
);

-- Enable constraints
Alter table tb_supplier enable constraint check_tb_supplier_id;

6. Disable CHECK Constraints
6.1 syntax

Alter table table_name
Disable constraint constraint_name;

6.2 Example

-- Disable Constraints
Alter table tb_supplier disable constraint check_tb_supplier_id;

7. View constraint details
Statement:

-- View detailed Constraints
Select
Constraint_name, -- constraint name
Constraint_type, -- constraint type
Table_name, -- the table where the constraint is located
Search_condition, -- constraint expression
Status -- enable or not
From user_constraints -- [all_constraints | dba_constraints]
Where constraint_name = 'check _ TB_SUPPLIER_ID ';

8. Delete CHECK Constraints

8.1 syntax
Alter table table_name
Drop constraint constraint_name;

8.2 example

Alter table tb_supplier
Drop constraint check_tb_supplier_id;

Author: David contact: david.louis.tian@outlook.com

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

PL/SQL Developer Practical Skills

-------------------------------------- Split line --------------------------------------