TOPICS (Click to Navigate)
Friday, 8 June 2018
CREATE TABLE statement in Oracle SQL with constraint names
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_Name VARCHAR(25) NOT NULL,
Manufacturer VARCHAR(25) NOT NULL,
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
SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...