When we work with databases, one of the most common needs is to ensure that the information is unique and is not repeated where it should not be. This is crucial in systems where data integrity can make the difference between an efficient service and one that causes constant problems and confusion. MySQL, one of the most popular database management systems, uses something known as a UNIQUE constraint to help us with this task.
What is the UNIQUE Restriction?
The UNIQUE constraint is a tool that can be applied to one or more columns in a database table to ensure that each row in that table has a unique value in the column or set of columns where this constraint has been applied. This prevents unwanted duplicates and maintains data accuracy.
For example, in a user table, we would want each user to have a unique email. The UNIQUE restriction ensures that no two users can register with the same email.
Implementation of the UNIQUE Restriction
Creating a New Table with UNIQUE
When you create a new table, you can immediately define the UNIQUE constraint. Suppose we want to create a table to store customer information for an online store:
CREATE TABLE clients ( client_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, ID VARCHAR(255), UNIQUE(email), UNIQUE(id) );In this example, the table customers has a column e-mail and a column identification card, both with the UNIQUE constraint. This means that each value in these columns must be unique across the entire table.
Adding UNIQUE to an Existing Table
If you already have a table and decide that one of the columns needs the UNIQUE constraint, you can easily add it:
ALTER TABLE clients ADD UNIQUE (email);This command modifies the table customers to add the UNIQUE constraint to the column e-mail.
UNIQUE in Multiple Columns
You can apply the UNIQUE constraint to a set of columns, which is especially useful when the combination of data in these columns needs to be unique. For example, if a school system has students who could share both first and last names but must not have the same combination of first name, last name, and date of birth:
CREATE TABLE students ( student_id INT AUTO_INCREMENT PRIMARY KEY, first name VARCHAR(50), last name VARCHAR(50), date_of birth DATE, UNIQUE(first name, last name, date_of birth) );In this case, there cannot be two records with the same name, surname and date of birth.
Handling UNIQUE Constraint Errors
When attempting to insert or modify data that would violate a UNIQUE constraint, MySQL will reject the action and display an error. It is important to handle these errors in your application to ensure a good user experience. Here we use a simple exception handling block in a Python script using MySQL Connector:
import mysql.connector from mysql.connector import Error try: connection = mysql.connector.connect(host='localhost', database='school', user='user', password='your_password&# 039;) cursor = connection.cursor() insert_query = """INSERT INTO students (first name, last name, date_of_birth) VALUES ('Ana', 'Torres', '2001-09-15')""" cursor.execute(insert_query) connection.commit() except mysql.connector.Error as err: print("Something went wrong: {}".format(err)) finally: if connection.is_connected(): cursor.close() connection.close() print ("MySQL connection closed")This script attempts to insert a student into the table, and handles any errors that may arise if the UNIQUE constraint is violated.
English 
Spanish