SQL Unique Constraint With Example

Constraints Definition:

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.

3. UNIQE Constraints

The UNIQUE Constraint prevents having of two identical values in a column records.

A unique constraint is similar to Primary key except that it can have null values unless specified not null.

Ensures that all values in a column are different

Difference Between Primary Key and Unique Key

Primary Key Unique Key
Primary Key does not allow NULL(blank) values. Whereas Unique key allow NULL(blank) values.
PRIMARY KEY = UNIQUE + NOT NULL UNIQUE KEY = UNIQUE + NULL

Syntax:


CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE,
    column_name datatype[(size)] [ NULL | NOT NULL ] UNIQUE,
    ....
);

Example: defined at Column level


SQL> CREATE TABLE std_info(
    no NUMBER(3,0) PRIMARY KEY,
    name VARCHAR(30) UNIQUE,
    address VARCHAR(70),
    contact_no VARCHAR(12)
);

---------------------------
Table created.

Read Also: