You are currently viewing SQL 1.47 SQL UNIQUE

SQL 1.47 SQL UNIQUE

Understanding the UNIQUE Constraint


SQL (Structured Query Language) is a powerful tool for managing and manipulating data within relational databases. One essential feature of SQL is the ability to enforce data integrity through constraints. In this tutorial, we’ll focus on the UNIQUE constraint, which ensures that the values in a column or a combination of columns are unique across all rows in a table.

What is the UNIQUE Constraint


The UNIQUE constraint in SQL ensures that all values in a column or a combination of columns are distinct or unique. It prevents duplicate entries from being inserted into the table. This constraint can be applied to one or multiple columns in a table.

Syntax


The syntax for adding a UNIQUE constraint to a column during table creation is as follows:

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

To add a UNIQUE constraint to an existing table, you can use the ALTER TABLE statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

Scenario 1: Applying UNIQUE Constraint to a Single Column
Let’s create a table named students with a column for student IDs (student_id) and apply a UNIQUE constraint to it.

CREATE TABLE students (
    student_id INT UNIQUE,
    name VARCHAR(50),
    age INT
);

Explanation:

  • We create a table named students with three columns: student_id, name, and age.
  • The student_id column is defined with the UNIQUE constraint, ensuring that each student ID is unique.

Scenario 2: Applying UNIQUE Constraint to Multiple Columns
Now, let’s create another table named employees with two columns: employee_id and email. We’ll apply a UNIQUE constraint to both columns to ensure that each employee has a unique ID and email address.

CREATE TABLE employees (
    employee_id INT,
    email VARCHAR(50) UNIQUE
);

Explanation:

  • We create a table named employees with two columns: employee_id and email.
  • The email column is defined with the UNIQUE constraint, ensuring that each email address is unique across all rows.

Scenario 3: Handling Violation of UNIQUE Constraint
If an attempt is made to insert a duplicate value into a column with a UNIQUE constraint, an error will occur. Let’s try to insert duplicate student IDs into the students table we created earlier:

INSERT INTO students (student_id, name, age) VALUES (101, 'John Doe', 20);
INSERT INTO students (student_id, name, age) VALUES (101, 'Jane Smith', 22); -- Duplicate entry

Explanation:

  • The first INSERT statement successfully adds a new student with ID 101.
  • The second INSERT statement attempts to insert another student with the same ID (101), violating the UNIQUE constraint, and will result in an error.

Conclusion


In this tutorial, you’ve learned about the UNIQUE constraint in SQL and how it ensures the uniqueness of values in one or more columns within a table. By applying this constraint, you can maintain data integrity and prevent duplicate entries, thereby improving the quality and reliability of your database.

Leave a Reply