Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/sql-unique-constraint/ below:

SQL | UNIQUE Constraint - GeeksforGeeks

In SQL, constraints play a vital role in maintaining the integrity and accuracy of the data stored in a database. One such constraint is the UNIQUE constraint, which ensures that all values in a column (or a combination of columns) are distinct, preventing duplicate entries.

This constraint is especially useful for fields where duplicates are not allowed, such as user IDs or email addresses. In this article, we will explore the SQL UNIQUE Constraint in detail, including its syntax, usage, and examples.

The SQL UNIQUE constraint ensures that all values in a column or a set of columns are different from one another. It can be applied to one or more columns in a table. When applied, the database will reject any insert or update operation that would create a duplicate value in the specified column(s).

The UNIQUE constraint allows NULL values, and multiple NULL values are allowed in a column with a UNIQUE constraint because NULL is considered a distinct value in SQL. However, this behavior is different from the PRIMARY KEY constraint, where NULL values are not allowed, as a primary key must uniquely identify each row and cannot have missing or undefined values.

• Evaluates to true on an empty subquery.
• Returns true only if there are unique tuples present as the output of the sub-query (two tuples are unique if the value of any attribute of the two tuples differs).
• Returns true if the sub-query has two duplicate rows with at least one attribute as NULL.

Syntax:

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

Let's create a Customers table where the Email column must be unique.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100) UNIQUE,
    Country VARCHAR(50)
);

In this case, each customer must have a unique email address. If you try to insert a duplicate email, SQL will raise an error.

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (1, 'John Doe', 'john.doe@example.com', 'USA');
INSERT INTO Customers (CustomerID, Name, Email, Country)

VALUES (2, 'Jane Smith', 'jane.smith@example.com', 'Canada');
-- This will fail because 'john.doe@example.com' already exists

INSERT INTO Customers (CustomerID, Name, Email, Country)
VALUES (3, 'Alice Johnson', 'john.doe@example.com', 'UK');

The third insert will fail because the Email john.doe@example.com already exists in the Customers table.

We can also apply the UNIQUE constraint to multiple columns to ensure that the combination of those columns is unique.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    UNIQUE (CustomerID, ProductID)
);

In this example, the combination of CustomerID and ProductID must be unique, meaning a customer cannot order the same product more than once.

SQL allows you to check for uniqueness in subqueries. You can use the UNIQUE keyword in a subquery to ensure that the results do not contain duplicate values.

SELECT CustomerID
FROM Orders
WHERE UNIQUE (
    SELECT OrderID
    FROM OrderDetails
    WHERE Orders.CustomerID = OrderDetails.CustomerID
);

In this example, we check if there are any duplicate OrderID values for each customer in the Orders table. If the subquery returns unique values, the CustomerID will be selected.

• Prevents Duplicate Values: The UNIQUE constraint ensures that no two rows have the same value in the specified column(s).
  
• Works with NULL Values: Unlike the PRIMARY KEY, the UNIQUE constraint allows multiple NULL values, as SQL treats NULL as a distinct value.
  
• Can Be Applied to One or More Columns: The UNIQUE constraint can be applied to a single column or a combination of columns.
  
• Does Not Automatically Create an Index: Although UNIQUE guarantees no duplicate values, it doesn't automatically create an index (but most databases create an index behind the scenes for performance reasons).
  
• Can Be Applied Using ALTER TABLE: You can add or remove the UNIQUE constraint on an existing table using ALTER TABLE.

The SQL UNIQUE constraint is an essential tool for ensuring data integrity and consistency within a database. By enforcing uniqueness in specific columns or combinations of columns, the UNIQUE constraint prevents duplicate data entries, making your database more reliable and efficient. Use the UNIQUE constraint when you need to guarantee that certain data remains distinct, such as email addresses, usernames, or product IDs. This constraint can be applied both during table creation and modification, providing flexibility and control over your database design.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4