Course
Unique Key
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Unique Key
A Unique Key in MySQL, when applied on a certain field of a database table, does not allow duplicate values to be inserted in that column, i.e. it is used to uniquely identify a record in a table.
Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.
This Unique Key works as an alternative to the Primary Key constraint; as both unique and primary keys assure uniqueness in a column of a database table.
Creating MySQL Unique Key
We can create a Unique Key on a MySQL table column using the UNIQUE keyword, and it holds the following features −
- Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key.
- It cannot have duplicate values.
- It can also be used as a foreign key in another table.
- A table can have more than one Unique column.
Syntax
Following is the syntax to create a UNIQUE key constraint on a column in a table −
CREATE TABLE table_name( column_name1 datatype UNIQUE, column_name2 datatype, ...);
As you observe, we just need to specify the keyword UNIQUE after the name of the desired column while creating a table using CREATE TABLE statement.
Example
In this example, let us create a table named CUSTOMERS and define a UNIQUE Key on one of its fields, ADDRESS. Look at the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2));
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
As you can see in the table definition, the Unique Key is created on the ADDRESS field.
Creating Multiple Unique Keys
We can create one or more Unique Key constraints on a column in a single MySQL table. When this constraint is applied in multiple fields, one cannot insert duplicate values in those fields.
Syntax
Following is the syntax to create unique key constraints on multiple columns in a table −
CREATE TABLE table_name(column_name1 UNIQUE, column_name2 UNIQUE,...)
Example
Assume we have created another table with the name CUSTOMERS in the MySQL database using CREATE TABLE statement.
Here we are creating a UNIQUE constraint on columns NAME and ADDRESS using the UNIQUE keyword as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL UNIQUE, AGE INT NOT NULL, ADDRESS CHAR (25) UNIQUE, SALARY DECIMAL (18, 2));
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Creating Unique Key on Existing Columns
We can add a unique key constraint on an existing column of a table using the ALTER TABLE... ADD CONSTRAINT statement.
Syntax
Following is the syntax to create a UNIQUE Key on existing columns of a table −
ALTER TABLE table_name ADD CONSTRAINT unique_key_name UNIQUE (column_name);
Note − Here the UNIQUE_KEY_NAME is just the name of the Unique Key. It is optional to specify the name while creating a unique key. It is used to drop the constraint from the column in a table.
Example
Using the ALTER TABLE statement, you can add a UNIQUE constraint on any existing column in the CUSTOMERS table created previously. In the following example, we are applying the UNIQUE constraint on the NAME column as shown below −
ALTER TABLE CUSTOMERS ADD CONSTRAINT UNIQUE_NAME UNIQUE (NAME);
Output
The table structure displayed will contain a UNI index on the ADDRESS column as shown −
Dropping MySQL Unique Key
If there is an unique constraint on a column already, you can drop it whenever it is not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement again.
Syntax
Following is the SQL query to drop the UNIQUE constraint from the column of a table −
ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;
Example
In this example, we will drop the constraint named UNIQUE_NAME from the column NAME of the CUSTOMERS table using the following MySQL query −
ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_NAME;
Output
The table structure displayed will contain a UNI index only on the ADDRESS column, referring that the index on NAME column is removed.
Creating Unique Key Using Client Program
In addition to use a key on a column to identify uniquely using the MySQL query We can also apply a Unique Key constraint on a Field using a client program.
Syntax
PHPNodeJSJavaPython
To apply unique key on a table field through a PHP program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the mysqli function query() as follows −
$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))';$mysqli->query($sql);
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';$dbuser = 'root';$dbpass = 'password';$dbname = 'TUTORIALS';$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit();}// printf('Connected successfully.');
$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))';if ($mysqli->query($sql)) { echo "Unique column created successfully in customers table \n";}if ($mysqli->errno) { printf("Table could not be created!.", $mysqli->error);}$mysqli->close();
Output
The output obtained is as follows −
Unique column created successfully in customers table