SQL UNIQUE Constraint

SQL UNIQUE CONSTRAINT example – Learn how to accept of unique values for columns in MySQL database with example and important points.

Unique constraint creates an index on the column indicating that a column or a set of columns should contain unique values.

An error occurs if trying to add a new row with a key value that already exist in that row.


SQL syntax to add the unique constraint to a column

CREATE TABLE [table_name](
column_name datatype  UNIQUE,
…….
);

SQL query to create a table and store unique values for columns in database table

Query example:

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT UNIQUE,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254)
);

the above query results in storing unique values for the column (ADDRBOOK_ID) in ADDRESSBOOK table by applying unique constraint.


SQL syntax to add the unique constraint on single column as the table constraint

CREATE TABLE [table_name]
(
column_name datatype
………………………..
,
unique( column_name)
);

SQL query to create a table and store unique values for columns in database table

Query example:

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
UNIQUE( ADDRBOOK_ID)

);


SQL syntax to add unique constraint on multiple columns as table constraint

CREATE TABLE table_name
(
column_name1 datatype,
column_name2  data type,

………………………..

,
unique( column_name1, column_name2)
);

SQL query   to create a table and store unique values for columns in database table

Query example:

CREATE TABLE ADDRESSBOOK
(ADDRBOOK_ID BIGINT,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
UNIQUE( ADDRBOOK_ID,MEMBER_ID)

);


SQL syntax for creating a table and assigning a specific name to a constraint

CREATE TABLE table_name
(column_name1 data_type,
column_name2 data_type,
………………………..

constraint contraint_name    unique( column_name1, column_name2)
);

SQL query to create a table and specify a unique constraint name for multiple columns

CREATE TABLE ADDRESSBOOK
(ADDRBOOK_ID BIGINT,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
constraint uc_add_mem_id   UNIQUE( ADDRBOOK_ID,MEMBER_ID)

);

The above query applies unique constrain on two columns that restricts the uniqueness of values in the ADDRBOOK_ID and \MEMBER_ID columns. i.e. these two columns have same ADDRBOOK_ID or MEMBER_ID but cannot have same ADDRBOOK_ID and MEMBER_ID.

Note: in unique constraint   null values are allowed.


SQL syntax to add a unique constraint for the existing the column

ALTER TABLE [table_name]
ADD CONSTRAINT constraint_name unique(column_list);

SQL query to add a unique constraint for the existing column

ALTER TABLE ADDRESSBOOK
ADD CONSTRAINT uc_add_mem_id   UNIQUE( ADDRBOOK_ID,MEMBER_ID)

Note:

The combination of values in the unique constraint must be unique.


SQL syntax to drop unique constraint

ALTER TABLE

DROP CONSTRAINT constraint_name;

SQL query to drop a unique constraint for existing column.

ALTER TABLE ADDRESSBOOK

DROP CONSTRAINT uc_add_mem_id;

Related Posts