Oracle database Add, query, delete check constraints

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.