Monday , September 23 2019
Home / SQL / Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint:

The UNIQUE constraint is used to ensure unique or distinct values in a column. The UNIQUE constraint column will never allow duplicate values. Each data of the UNIQUE column is distinct from another data in that column. If you attempt to insert duplicate value in the UNIQUE column, the oracle will generate error.

An oracle table can have single or multiple UNIQUE constraints.

The oracle database creates automatically UNIQUE index on UNIQUE constraint column when you create UNIQUE constraint on a table column.

Note: A UNIQUE constraint can be defined on single column or combination of columns. A UNIQUE constraint can hold up to 32 columns.

N.B: The UNIQUE constraint can be set by either CREATE TABLE statement or ALTER TABLE statement.

Restrictions:

  • You can not define UNIQUE constraint for the column which are containing data type LOB,LONG,LONG RAW, OBJECT, NESTED TABLE, VARRAY,REF and User-Defined-Type.
  • A UNIQUE constraint can contain up to 32 columns.
  • You cannot create both PRIMARY KEY and UNIQUE constraint on the same single column or same combination of columns.

Set UNIQUE constraint by CREATE TABLE statement:

You can define UNIQUE constraint, when you create table by CREATE TABLE statement.

Practice:

This practice will describe how to set UNIQUE constraint when you create a table by CREATE TABLE statement.

Syntax:

CREATE   TABLE   table_name

(

column_name    datatype (length)  CONSTRAINT  constraint_name  UNIQUE

);

Here,

CONSTRAINT keyword is used to define a constraint.

constraint_name is the meaningful name of UNIQUE constraint. The name can be started with the first two letter of UNIQUE constraint uk. Then use underscore(_) and then the brief of column name such as app_id for Applicant_Id. So, if you set UNIQUE constraint on APPLICANT_ID, the name of the constraint can be uk_app_id. You should use small letter in the name.

UNIQUE keyword has been used to define the constraint type as UNIQUE.

Example:    

Follow and execute the steps to complete the practice-

Step-1:

Drop the table first if already exists.

DROP TABLE Recruitment;

Step-2:

Create the Recruitment table containg 5 columns.

CREATE   TABLE   Recruitment

(

Applicant_Id         Varchar2 (10)  CONSTRAINT  uk_app_id  UNIQUE,

Applicant_Name  Varchar2(50),

Post_Name           Varchar2(50),

Qualification        Varchar2(30),

Mobile_NO          Varchar2(15)

);

In the example, a UNIQUE constraint has been defined on APPLICANT_ID column during RECRUITMENT table creation by CREATE TABLE statement.

Step-3:

Now try to insert row:

INSERT   INTO  Recruitment (Applicant_Id,Applicant_Name,Post_Name,Qualification, Mobile_NO)   VALUES  (‘01214′,’MD. ABDUR RAHIM’,’SOFTWARE DEVELOPER’,’MSC in COMPUTER SCIENCE’,’01722589977′);

Step-4:

Commit the data and query to confirm.

COMMIT;

SELECT * FROM Recruitment;

Step-5:

In the step, try to insert again the same record that already inserted.

INSERT   INTO   Recruitment (Applicant_Id,Applicant_Name,Post_Name,Qualification, Mobile_NO)   VALUES (‘01214′,’MD. ABDUR RAHIM’,’SOFTWARE DEVELOPER’,’MSC in COMPUTER SCIENCE’,’01722589977′);

When you attempt to insert the record whose APPLICANT_ID is ‘01214’ already exists, the following error will be generated by system and the row will not be inserted for unique constraint (ADMIN.UK_APP_ID) violation.

INSERT   INTO   Recruitment (Applicant_Id,Applicant_Name,Post_Name,Qualification, Mobile_NO)   VALUES (‘01214′,’MD. ABDUR RAHIM’,’SOFTWARE DEVELOPER’,’MSC in COMPUTER SCIENCE’,’01722589977′)

Error at line 1

ORA-00001: unique constraint (ADMIN.UK_APP_ID) violated

In the circumstance, you have to provide the unique value of APPLICANT_ID column that has not been inserted yet.

Step-6:

Change the value of APPLICANT_ID to ‘03255’ intead of ‘01214’ and execute INSERT the statement.

INSERT   INTO  Recruitment (Applicant_Id,Applicant_Name,Post_Name,Qualification, Mobile_NO)  VALUES  (‘03255′,’MD. ABDUR RAHMAN’,’SOFTWARE DEVELOPER’,’BSC in COMPUTER SCIENCE’,’01722666977′);

Step-7:

Commit the changes and query to confirm whether row has been inserted successfully.

COMMIT;

SELECT   Applicant_Id,Applicant_Name,Post_Name,Qualification FROM  Recruitment;

The Result of SELECT statement:

APPLICANT_ID   APPLICANT_NAME       POST_NAME                   QUALIFICATION

01214               MD. ABDUR RAHIM      SOFTWARE DEVELOPER   MSC in COMPUTER SCIENCE

03255               MD. ABDUR RAHMAN  SOFTWARE DEVELOPER   BSC in COMPUTER SCIENCE

From the above result, we see that the record for APPLICANT_ID ‘03255’ has been successfully inserted.

Check Also

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Set NOT NULL constraint by CREATE TABLE statement in Oracle

Definition of NOT NULL constraint: The NOT NULL constraint is used to restrict null values …

One comment

  1. I?¦m no longer positive where you are getting your information, however great topic. I needs to spend a while learning more or working out more. Thank you for great information I was on the lookout for this info for my mission.

Leave a Reply

Your email address will not be published. Required fields are marked *