CodeJackpot - SQL Unique

SQL Unique

Structured Query Language (SQL) is a programming language used to manage and manipulate relational databases. One of the key features of SQL is the ability to enforce data integrity through the use of constraints. One such constraint is the UNIQUE constraint, which ensures that a column or set of columns in a table contains only unique values.

Brief Explanation of SQL Unique

The UNIQUE constraint is used to ensure that the values in a column or set of columns are unique across all rows in a table. This means that no two rows in the table can have the same value(s) in the specified column(s). The UNIQUE constraint can be applied to a single column or to a combination of columns, known as a composite key.

When a UNIQUE constraint is applied to a column or set of columns, the database engine automatically creates an index on the column(s) to enforce the constraint. This index allows the database to quickly check for duplicate values when inserting or updating data in the table.

Code Examples

Here are some examples of how to use the UNIQUE constraint in SQL:

Example 1: Creating a table with a UNIQUE constraint


CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  name VARCHAR(255)
);

In this example, we create a table called "employees" with three columns: "id", "email", and "name". The "id" column is the primary key, and the "email" column has a UNIQUE constraint applied to it. This ensures that each email address in the table is unique.

Example 2: Adding a UNIQUE constraint to an existing table


ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

In this example, we add a UNIQUE constraint to the "email" column in the "employees" table. The constraint is named "unique_email", and it applies to the "email" column only.

Example 3: Creating a composite key with a UNIQUE constraint


CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  UNIQUE (order_id, customer_id)
);

In this example, we create a table called "orders" with three columns: "order_id", "customer_id", and "order_date". The "order_id" column is the primary key, and we create a composite key using the "order_id" and "customer_id" columns. The UNIQUE constraint ensures that each combination of "order_id" and "customer_id" is unique.

Conclusion

The UNIQUE constraint is a powerful tool for enforcing data integrity in SQL databases. By ensuring that columns or sets of columns contain only unique values, the constraint helps to prevent data duplication and maintain the accuracy and consistency of the database. Whether you are creating a new table or modifying an existing one, the UNIQUE constraint is a valuable tool to have in your SQL toolkit.

SQL