CREATE TABLE statement in Oracle SQL with CHECK constraints

CREATE TABLE statement in Oracle SQL with CHECK constraints

with CHECK constraint

Syntax:

CREATE TABLE < tablename> (

<column_name1> <datatype>,

<column_name2> <datatype>,

CHECK ( <column_name> in ( val1, val2, val3, …, valn) )

CHECK ( <column_name> between <val1> and <val2> )

CHECK (<column_name> comparison_operator <value>))

There may be many constraints that are to be considered in modeling a real world entity. Some of them are just the type and size. Others may include various conditions for an attribute like set of permitted values, set of permitted range of values, values that are less than or greater than. The beauty is we can model all of the above said constraints as part of table creation so that the DBMS itself can take care if anything violated. We are using CHECK keyword to model these constraints.

Let us discuss this with an example below;

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Furniture_Type VARCHAR(20) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

CONSTRAINT pk PRIMARY KEY (Furniture_ID),

CONSTRAINT ch1 CHECK (Quantity > 10),

CONSTRAINT ch2 CHECK (Furniture_Type in (‘Chair’, ‘Table’, ‘Cot’)

);

In the example given above, the CHECK constraint ch1 make sure that the value inserted for quantity attribute must be greater than 10. While you insert a record, like other basic constraints (type and size) the CHECK constraint also validated. Hence, no one can insert values that are not permitted for Quantity attribute.

Also, the CHECK constraint ch2 make sure that the permitted values for Furniture_Type are either ‘Chair’, or ‘Table’, or ‘Cot’. Other than these three values, no other values are permitted for Furniture_Type column.

****************

Go to CREATE TABLE page

Go to SQL home page