Your go-to guide for SQL Unique Key constraints with real-world examples and detailed explanations. Learn how to implement Unique constraints effectively in your SQL database!
The SQL Unique Key (or Unique constraint) ensures that no duplicate values are entered in a specified column of a table. It is similar to the Primary Key, but with a few differences:
For instance, in a table that stores customer records for a bank, you can create a UNIQUE constraint on the MOBILE_NO column to avoid entering multiple records with the same mobile number.
The following are the key features of the SQL Unique Key:
You can create a Unique Key on a database table using the UNIQUE keyword. Here’s the syntax to define a Unique Key:
CREATE TABLE table_name(
column1 datatype UNIQUE KEY,
column2 datatype,
......
columnN datatype
);
The following SQL query creates a table named CUSTOMERS with a Unique Key on the ID column:
CREATE TABLE CUSTOMERS (
ID INT NOT NULL UNIQUE KEY,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25),
SALARY DECIMAL(18, 2)
);
Let’s try inserting duplicate records into the CUSTOMERS table:
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS VALUES
(1, 'Khilan', 25, 'Delhi', 1500.00 );
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘customers.ID’
You can create multiple unique keys on one or more columns of a table:
CREATE TABLE BUYERS (
ID INT NOT NULL UNIQUE KEY,
NAME VARCHAR(20) NOT NULL UNIQUE KEY,
AGE INT NOT NULL,
ADDRESS CHAR(25),
SALARY DECIMAL(18, 2)
);
If you try to insert duplicate records in the BUYERS table, you will get the following error:
INSERT INTO BUYERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO BUYERS VALUES
(1, 'Rajesh', 25, 'Delhi', 1500.00 );
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘buyers.ID’
To add a Unique Key to an existing column, use the ALTER TABLE command:
ALTER TABLE CUSTOMERS ADD CONSTRAINT UNIQUE_ADDRESS UNIQUE(ADDRESS);
To remove a Unique Key constraint, you can use the ALTER TABLE command:
ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_ADDRESS;
After dropping the Unique constraint, you can insert records with duplicate values in the ADDRESS column:
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Ahmedabad | 1500.00 |
Understanding the difference between SQL Unique Key and Primary Key constraints is essential for designing your database schema effectively. Both are used to enforce data integrity, but they have distinct characteristics.
– A Primary Key ensures that each row in a table is uniquely identifiable. No two rows can have the same value in the primary key column.
– A Unique Key also ensures that all values in a column are unique, but it allows a single NULL value to exist.
– A Primary Key does not allow NULL values, ensuring that every row has a valid identifier.
– A Unique Key allows NULL values, but only one NULL value is allowed per column.
– A table can have only one Primary Key, as it uniquely identifies each record in the table.
– A table can have multiple Unique Keys, and each Unique Key can enforce uniqueness on different columns.
– A Primary Key automatically creates a clustered index on the column to speed up search operations.
– A Unique Key also creates a unique index, but it is a non-clustered index, meaning it doesn’t affect the physical order of the data in the table.
– A Primary Key can be referenced by a foreign key in another table, ensuring referential integrity between tables.
– A Unique Key can also be used as a foreign key, but it doesn’t uniquely identify the rows in the table like the primary key does.
– A Primary Key constraint automatically has a default name (which can be changed if needed).
– A Unique Key constraint also has a default name, but it can also be explicitly named when defining the constraint in SQL.
Both Primary Keys and Unique Keys play important roles in ensuring data integrity and uniqueness in SQL tables. However, their differences in terms of NULL values, indexing, and constraint usage determine when and how they should be applied in your database schema.