Check constraints are checking constraints that can specify the values that each column can enter to ensure the correctness of the data
For example, by restricting data types, checking constraints, entering formats, default values, non-null constraints, etc.
For example: Student name is greater than 0, student number cannot be empty, if request is not met, the input is invalid
(1) Add constraint:
"1" adds a check constraint when creating a table, after creating the table
Grammar:
CONSTRAINT constraint name CHECK (conditional)
Example:
-Add a CHECK constraint to the table while creating the Customer information table
CREATE TABLE Custominfo (
Customid VARCHAR2 (10),
NAME VARCHAR2 (10),
Age Number (2),
GENDER VARCHAR (2),
TEL VARCHAR (11),
ADDRESS VARCHAR2 (+), [CONSTRAINT chk_age] CHECK (age>=18 and AGE<=50)
);
"2" add constraint using ALTER
Grammar:
ALTER table name ADD CONSTRAINT constraint name CHECK (condition)
Example:
Add a check constraint to a Customer information table
ALTER TABLE custominfo ADD CONSTRAINT chk_gender CHECK (gender= ' male ' or gender= ' female ');
"3" Query Check Constraint
Grammar:
Select cu.* from User_cons_columns cu, user_constraints au
where cu.constraint_name = Au.constraint_name
and Au.constraint_type = ' C ' and au.table_name = ' table name ';
Example:
Select cu.* from User_cons_columns cu, user_constraints au
where cu.constraint_name = Au.constraint_name
and Au.constraint_type = ' C ' and au.table_name = ' custominfo ';
"4" Delete constraint
Grammar:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example:
-Remove gender constraints for customer information sheets
ALTER TABLE categoryinfo DROP CONSTRAINT chk_gender;
This article is from the "Loly_zhang" blog, make sure to keep this source http://lolyzhang.blog.51cto.com/10029387/1888968
Oracle database Add, query, delete check constraints