Sql create table with check constraint

<<PreviousNext>>

Description

The SQL CHECK CONSTRAINT ensures that a value for a specific column or columns have satisfied a specified condition.

The job of CHECK constraint is, to limit the values for a column of a table.

Note :

• The SQL CHECK CONSTRAINT can not be used on a VIEW.
• The SQL CHECK CONSTRAINT can not be used in a subquery.
• The SQL CHECK CONSTRAINT can also be used in ALTER TABLE and DROP TABLE statement.

Example

The following example creates a table. The table contain a CHECK CONSTRAINT on commission column.The constraint ensures that the 'commission' must be less than 1. Here is the field name and data types :

the following sql statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) NOT NULL ,
commission decimal CHECK( commission<1));

To see the structure of the created table :

DESCRIBE mytest; 

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.