{"id":675,"date":"2024-09-04T06:56:10","date_gmt":"2024-09-04T06:56:10","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=675"},"modified":"2025-06-07T06:00:33","modified_gmt":"2025-06-07T06:00:33","slug":"unique","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/unique\/","title":{"rendered":"UNIQUE"},"content":{"rendered":"\n

SQL UNIQUE Keyword<\/h2>\n\n\n\n

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

<\/p>\n\n\n\n

SQL UNIQUE Constraint on CREATE TABLE Syntax<\/h2>\n\n\n\n
CREATE TABLE table_name (\n    column1 datatype NOT NULL UNIQUE,\n    column2 datatype,\n    ...\n);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on CREATE TABLE Example<\/h2>\n\n\n\n
This query creates a Rooms<\/code> table with a room_id<\/code> column that must contain unique values.<\/p>\n\n\n\n
CREATE TABLE Rooms (\n    room_id INTEGER NOT NULL UNIQUE,\n    room_type TEXT,\n    capacity INTEGER\n);<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Try It Out!<\/a><\/div>\n<\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on Multiple Columns Syntax<\/h2>\n\n\n\n
CREATE TABLE table_name (\n    column1 datatype NOT NULL,\n    column2 datatype NOT NULL,\n    column3 datatype,\n    CONSTRAINT constraint_name UNIQUE (column1, column2)\n);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on Multiple Columns Example<\/h2>\n\n\n\n
This query creates a LabBookings<\/code> table where the combination of lab_id<\/code> and booking_date<\/code> must be unique.<\/p>\n\n\n\n
CREATE TABLE LabBookings (\n    lab_id INTEGER NOT NULL,\n    booking_date TEXT NOT NULL,\n    booked_by TEXT,\n    CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date)\n);<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on ALTER TABLE Syntax<\/h2>\n\n\n\n
ALTER TABLE table_name\nADD UNIQUE (column_name);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on ALTER TABLE Example<\/h2>\n\n\n\n
ALTER TABLE Rooms\nADD UNIQUE (room_type);<\/pre><\/div>\n\n\n\n
\n
Note:<\/strong> This example does not<\/strong> work in the TechKubo playground. In SQLite, UNIQUE<\/code> constraints must be defined during CREATE TABLE<\/code>, not added later with ALTER TABLE<\/code>.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on ALTER TABLE with Constraint Name Syntax<\/h2>\n\n\n\n
ALTER TABLE table_name\nADD CONSTRAINT constraint_name UNIQUE (column1, column2);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Constraint on ALTER TABLE with Constraint Name Example<\/h2>\n\n\n\n
ALTER TABLE LabBookings\nADD CONSTRAINT UC_LabBooking UNIQUE (lab_id, booking_date);<\/pre><\/div>\n\n\n\n
\n
Note:<\/strong> This example does not work in TechKubo playground. SQLite allows UNIQUE<\/code> to be added only at table creation, not via ALTER TABLE<\/code>.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP UNIQUE Constraint Syntax<\/h2>\n\n\n\n
ALTER TABLE table_name\nDROP CONSTRAINT constraint_name;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL DROP UNIQUE Constraint Example (MySQL)<\/h2>\n\n\n\n
ALTER TABLE LabBookings\nDROP INDEX UC_LabBooking;<\/pre><\/div>\n\n\n\n
\n
Note:<\/strong> 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.<\/p>\n<\/blockquote>\n\n\n\n
<\/p>\n\n\n\n
SQL UNIQUE Labs<\/h2>\n\n\n\n