Oracle database Add, query, delete check constraints

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