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