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

CREATE TABLE statement in Oracle SQL with constraint names

CREATE TABLE statement in Oracle SQL with constraint names

With constraint name

Syntax:

CREATE TABLE <tablename1> (

<column name1> <datatype>,

<column name2> <datatype>,

<conlumn name3> <datatype>,

CONSTRAINT < constraint name1 > PRIMARY KEY ( <column name1>),

CONSTRAINT <constraint name2> FOREIGN KEY (<column name2>)

REFERENCES <tablename2> (<column name1>)

CONSTRAINT < constraint name3 > UNIQUE ( <column name1>),

);

As shown in the syntax above, the constraints can be given a name using the keyword CONSTRAINT. This will help us in handling the constraints efficiently. For example, one may wants to remove a constraint, or one may wants to change the constraint etc. In simple words, the constraints with constraint names can be manipulated like any attribute or table.

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

CONSTRAINT pk PRIMARY KEY (Furniture_ID),

CONSTRAINT uq1 UNIQUE (Manufacturer),

CONSTRAINT uq2 UNIQUE (Design_No)

);

Use of CONSTRAINT keyword:

If you want to remove the UNIQUE constraint from Manufacturer attribute, you can easily remove by using its name (uq1). In case, if you have created the table without constraint name (ie, without CONSTRAINT constraint_name), then it is difficult because of two UNIQUE attributes.

In occasions where more than one similar type of constraints used, the use of constraint name is very very important.

*********

Go to CREATE TABLE page

Go to SQL home page

 

Create table statement in SQL simple explanation with examples

Create table statement in SQL simple explanation with examples

CREATE TABLE statements

• CREATE TABLE - simple table creation

• CREATE TABLE - with constraints and without constraints name

• CREATE TABLE - constraints with constraint names

• CREATE TABLE - with CHECK constraints

***********

CREATE TABLE in Oracle SQL Simple version

CREATE TABLE in Oracle SQL Simple version

Simple table creation in Oracle SQL

[Note: in the syntax below, the contents of angular brackets along with angular brackets to be replaced with user defined words. Refer example below]

Syntax:

CREATE TABLE <tablename> (

<column name1> <datatype>,

<column name 2> <datatype>,

<column name 3> < datatype>,

…

<column name n> < datatype>

);

• This DDL statement creates a table with n attributes. This is the simplest of table creation.

• Some of the basic data types are CHAR, VARCHAR, DATE, and NUMBER.

• Remember that DDL statement will update the Data Dictionary.

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25),

Manufacturer VARCHAR(25),

Quantity NUMBER(5)

);

• This statement will create the table FURNITURES with the following attributes;

• FURNITURE_ID - character attribute stores alpha-numeric characters,

• FURNITURE_NAME - variable length character attribute stores alpha-numeric characters,

• MANUFACTURER - variable length character attribute stores alpha-numeric characters and

• QUANTITY - number attribute stores numbers only.

• Each record that will be stored in this table will occupy maximum of 60 bytes (Furniture_id 5 bytes + Furniture_name 25 bytes + Manufacturer 25 bytes + Quantity 5 bytes = 60 bytes).

• The details regarding the names of the table and its attributes, types of the attributes and size of the attributes all will be stored in the data dictionary. [Note: for MySQL the data dictionary is called Information schema and for Oracle database it is Oracle metadata]

• This statement will not include any additional constraints other than data type and size.

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

Go to CREATE TABLE page

Go to SQL home page

 

 

CREATE TABLE in Oracle SQL with constraints and without constraint names

CREATE TABLE in Oracle SQL with constraints and without constraint names

With constraints and without constraint names

A table can be created with constraints [conditions/integrity constraints]. Some of the constraints are NULL, NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.

• An attribute with NULL constraint can have NULL values [or empty].

• An attribute with NOT NULL constraint must have a value otherwise the record cannot be stored.

• An attribute with PRIMARY KEY constraint is the primary key for the table.

• An attribute with UNIQUE constraint cannot have duplicate values.

• An attribute with FOREIGN KEY constraint is the foreign key attribute that refers some other table for permitted values.

• An attribute with CHECK constraint will check for the conditions given before accepting values.

All these above said constraints will be validated on insertion of each record into the table.

These constraints can be attached at the end of each column definition [one column definition is highlighted in green color] as given in syntax 1 or can be attached at the end of table definition as given in syntax 2 or both.

An attribute without constraints is by default NULL.

It is advisable not to have NULL attributes.

Syntax 1:

CREATE TABLE < tablename> (

<column name 1> < datatype> [constraint],

<column name 2> < datatype> [constraint],

<column name 3> < datatype> [constraint],

…

<column name n> < datatype> [constraint],

);

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5) PRIMARY KEY,

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5) UNIQUE

);

OR

Syntax 2:

CREATE TABLE < tablename> (

<column name 1> < datatype> [constraint],

<column name 2> < datatype> [constraint],

<column name 3> < datatype> [constraint],

…

<column name n> < datatype> [constraint],

[constraint],

[constraint],

[constraint],

…

);

Example:

CREATE TABLE Furnitures (

Furniture_ID CHAR(5),

Furniture_Name VARCHAR(25) NOT NULL,

Manufacturer VARCHAR(25) NOT NULL,

Quantity NUMBER(5),

Design_No NUMBER(5),

PRIMARY KEY (Furniture_ID),

UNIQUE (Design_No)

);

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

Go to CREATE TABLE page

Go to SQL home page