Friday, 8 June 2018

CREATE TABLE statement in Oracle SQL with constraint names

CREATE TABLE statement in Oracle SQL with constraint names

With constraint name

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.

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 uq1 UNIQUE (Manufacturer),

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.



No comments:

Post a Comment

Three fundamental problems of HMM

Three fundamental problems of HMM that are to be solved, Evaluation, Decoding and Learning problems of HMM, How these HMM fundamental pro...