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