Syntax For Creating and Deleting Unique Key
Unique Key for Creation
CREATE TABLE student(
student_id INT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
gender ENUM(‘Male’, ‘Female’, ‘Other’),
address VARCHAR(255),
);
In the above implementation we have created student table with student_id, name, email, gender, address as columns and email is unique key.
Let’s see another example where we will use two columns as unique key
CREATE TABLE employee(
employee_id INT,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
employee_code VARCHAR(255) UNIQUE,
gender ENUM(‘Male’, ‘Female’, ‘Other’),
address VARCHAR(255),
);
In the above table employee, employee_code and email are two columns which are acting as unique key.
Unique Key for Deletion
In the above table student, we can delete unique key email as follows
ALTER TABLE student,
DROP INDEX email;
Now email is not unique key in student table so duplicate values may be inserted in this column.
So like this we can declare and delete the unique key in any table.
Unique Key in DBMS
A column or set of columns in a database system that uniquely identifies each tuple in the table is called a unique key. A unique key ensures that no two rows in the table have the same combination of values, so it enforces data integrity by preventing duplicity of the values in the table. In this article, we are going to see about the unique keys in DBMS along with their implementation in the table. Let’s start with the definition of a unique key.