Check Constraint in Oracle | Check - oracle tutorial - sql tutorial
Learn oracle - oracle tutorial - Oracle check constraint - oracle examples - oracle programs
What is check constraint in oracle ?
- A check constraint allows you to specify a condition on each row in a table.
Oracle check constraints
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Syntax:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);click below button to copy the code. By - oracle tutorial - team
Note:
- The check constraint defined on a table must refer to only columns in that table. It cannot refer to columns in other tables.
- A check constraint cannot include a Subquery.
- A check constraint can be defined in either a CREATE TABLE statement or a ALTER TABLE statement.
Example:
CREATE TABLE emp_detl
(
emp_id number(4),
emp_name varchar(20),
emp_dbt number(4)
)
ALTER TABLE emp_detl ADD CONSTRAINT det_l CHECK (emp_dbt between 1 and 5);click below button to copy the code. By - oracle tutorial - team
CHECK:
- CREATE TABLE statement is used to create a new table in oracle database and ‘emp_detl’ is the created table name.
- After that click on the RUN button for executing the queries
- After clicking the run button the table has been created successfully
- SELECT statement is used to select all the data from created table
- After clicking the run button the table has been selected successfully
- ALTER TABLE statement is used to modify the table in oracle database and ‘emp_detl’ is the created table name. In this statement CHECK constraint is used for specifying the condition (employee department Id must be between 1 and 5) in the emp_dbt column.
- After clicking the run button the table has been modified successfully
- INSERT statement is used for inserting the required values in the created columns in the table
- After clicking the run button the check constraint provides a statement ’check constraint violated’ because the user has inserted the employee department Id more than 5. Hence the check constraint has been executed successfully.