UNIQUE

SQL UNIQUE Keyword

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are unique. This helps maintain data integrity by preventing duplicate values in specified columns.

SQL UNIQUE Constraint on CREATE TABLE Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL UNIQUE,
    column2 datatype,
    ...
);

CREATE TABLE table_name (

    column1 datatype NOT NULL UNIQUE,

    column2 datatype,

    ...

);

SQL UNIQUE Constraint on CREATE TABLE Example

This query creates a Rooms table with a room_id column that must contain unique values.

CREATE TABLE Rooms (
    room_id INTEGER NOT NULL UNIQUE,
    room_type TEXT,
    capacity INTEGER
);

CREATE TABLE Rooms (

    room_id INTEGER NOT NULL UNIQUE,

    room_type TEXT,

    capacity INTEGER

);

SQL UNIQUE Constraint on Multiple Columns Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype,
    CONSTRAINT constraint_name UNIQUE (column1, column2)
);

CREATE TABLE table_name (

    column1 datatype NOT NULL,

    column2 datatype NOT NULL,

    column3 datatype,

    CONSTRAINT constraint_name UNIQUE (column1, column2)

);

SQL UNIQUE Constraint on Multiple Columns Example

This query creates a LabBookings table where the combination of lab_id and booking_date must be unique.

CREATE TABLE LabBookings (
    lab_id INTEGER NOT NULL,
    booking_date TEXT NOT NULL,
    booked_by TEXT,
    CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date)
);

CREATE TABLE LabBookings (

    lab_id INTEGER NOT NULL,

    booking_date TEXT NOT NULL,

    booked_by TEXT,

    CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date)

);

SQL UNIQUE Constraint on ALTER TABLE Syntax

ALTER TABLE table_name
ADD UNIQUE (column_name);

ALTER TABLE table_name

ADD UNIQUE (column_name);

SQL UNIQUE Constraint on ALTER TABLE Example

ALTER TABLE Rooms
ADD UNIQUE (room_type);

ALTER TABLE Rooms

ADD UNIQUE (room_type);

Note: This example does not work in the TechKubo playground. In SQLite, UNIQUE constraints must be defined during CREATE TABLE, not added later with ALTER TABLE.

SQL UNIQUE Constraint on ALTER TABLE with Constraint Name Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

ALTER TABLE table_name

ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

SQL UNIQUE Constraint on ALTER TABLE with Constraint Name Example

ALTER TABLE LabBookings
ADD CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date);

ALTER TABLE LabBookings

ADD CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date);

Note: This example does not work in TechKubo playground. SQLite allows UNIQUE to be added only at table creation, not via ALTER TABLE.

SQL DROP UNIQUE Constraint Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

SQL DROP UNIQUE Constraint Example (MySQL)

ALTER TABLE LabBookings
DROP INDEX UC_LabBooking;

ALTER TABLE LabBookings

DROP INDEX UC_LabBooking;

Note: This example does not work in TechKubo playground. SQLite does not support dropping a named UNIQUE constraint directly. You would need to recreate the table without the UNIQUE constraint.

SQL UNIQUE Labs