Oracle check constraints and oraclecheck Constraints

Oracle check constraints and oraclecheck Constraints
A check constraint is a check constraint. When a check constraint is used, each input data is checked. Only records that meet the conditions are saved in the table, this ensures Data Validity and integrity.
Check constraints have the following four features:
In the expression of the check constraint, one or more columns in the table must be referenced, and the operation result of the expression is a Boolean value.
Multiple check constraints can be defined in one column type.
You can define both the check constraint and the not null constraint for the same column.
Check constraints can be defined at the column level or table level.
Here, we have created a p3 table as an example:
SQL> create table p3 (
2 pid number (4) not null unique,
3 pname varchar2 (10 ),
4 page number (3) constraint check_p3age check (page> 0) -- specify check constraints for page, where page must be greater than 0
5 );
The table has been created.
SQL> insert into p3 values (1, 'aaa', 20 );
One row has been created.
SQL> insert into p3 values (1, 'aaa', 0 );
Insert into p3 values (1, 'aaa', 0)
*
Row 3 has an error:
ORA-02290: violation of check constraints (SYSTEM. CHECK_P3AGE)


SQL> alter table p3 add constraint check2_p3age check (page <100); -- add constraints to the table. Yes, the page value must be less than 100.
The table has been changed.
SQL> insert into p3 values (1, 'aaa', 100); -- an error is returned when the inserted value is greater than or equal to 100.
Insert into p3 values (1, 'aaa', 100)
*
Row 3 has an error:
ORA-02290: violation of check constraints (SYSTEM. CHECK2_P3AGE)


SQL> alter table p3 drop constraint check2_p3age; -- delete the check constraint in the table

The table has been changed.

The above content comes from the Learning Record.