SQL Unique

The UNIQUE constraint ensures that all values in a column are different. It’s useful when you want to avoid duplicate entries in a specific field, such as email addresses or usernames. Unlike PRIMARY KEY, you can have multiple UNIQUE columns in a table.

SQL Unique Syntax

On Create Table:

CREATE TABLE table_name (
  column1 datatype UNIQUE,
  column2 datatype
);

CREATE TABLE table_name (

  column1 datatype UNIQUE,

  column2 datatype

);

On Alter Table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column_name);

Note: SQLite allows UNIQUE directly on the column during table creation. Named constraints (like ADD CONSTRAINT) work differently, and SQLite doesn’t fully support dropping constraints by name — more on that later.

SQL Unique Example

SQL Unique on Create Table

This query shows how to create a table where the student_number must be unique for each student. This prevents two students from being assigned the same student number.

CREATE TABLE Student_Registry (
  id INTEGER PRIMARY KEY,
  student_number TEXT UNIQUE,
  full_name TEXT
);

CREATE TABLE Student_Registry (

  id INTEGER PRIMARY KEY,

  student_number TEXT UNIQUE,

  full_name TEXT

);

SQL Unique on Alter Table

This query adds a unique constraint to the phone_number column of the Doctors table to ensure no two doctors share the same number. This ensures all doctors have different phone numbers.

ALTER TABLE Doctors
ADD UNIQUE (phone_number);

ALTER TABLE Doctors

ADD UNIQUE (phone_number);

SQL Unique Drop a Unique Constraint Example

This query is not supported in SQLite. Instead, you’d need to recreate the table without the unique constraint if necessary. However, the general SQL syntax looks like this:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

SQL Unique Labs