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;