SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "id" column when the "student" table is created:
Example
SQL Server / Oracle / MS Access:
CREATE TABLE student(
id int NOT NULL UNIQUE ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
Age int
);
Example
MySQL:
CREATE TABLE student(
id int NOT NULL ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
Age int,
UNIQUE (id)
);
Example
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE student(
id int NOT NULL ,
Last_name varchar( 50 ) NOT NULL ,
First_name varchar( 50 ) ,
Age int,
CONSTRAINT UC_student UNIQUE (id, Last_name)
);
SQL UNIQUE Constraint on ALTER TABLE
The following SQL creates a UNIQUE constraint on the "id" column when the table is already created, use the following SQL:
Example
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE student
ADD UNIQUE (id);
Example
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE student
ADD CONSTRAINT UC_student UNIQUE (id,Last_name);