SQL Unique Constraints
Introduction to SQL Unique Constraints:
Structured Query Language (SQL) is a domain-specific language used for managing and manipulating relational databases. It provides a standardized way of interacting with databases and is widely used in the field of database management.
In a relational database, data is organized into tables, and relationships between tables are established based on common fields. SQL is used to perform various operations on these tables, such as retrieving data, updating records, and defining the structure of the database.
SQL Unique Constraints
A unique constraint in SQL is a type of constraint that ensures that the values in a column (or a group of columns) are unique across the rows in a table. In other words, it ensures that no two rows in the table have the same values in the specified column or columns. Unique constraints are used to enforce data integrity and prevent duplicate entries in a database.
Here’s the basic syntax for creating a unique constraint in SQL:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, ...)
);
Let’s break down the syntax:
CREATE TABLE table_name: This part is used to create a new table with the specified name.(column1 datatype, column2 datatype, ...): Here, you define the columns of the table along with their data types.CONSTRAINT constraint_name UNIQUE (column1, column2, ...): This is where the unique constraint is defined. TheUNIQUEkeyword indicates that the values in the specified columns must be unique across all rows in the table.
In SQL, the syntax for creating a unique constraint depends on whether you are adding the constraint at the time of table creation or altering an existing table. Here are the basic syntax structures for both scenarios:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, ...)
);
In this syntax:
CREATE TABLE table_name: Specifies the creation of a new table with the given name.(column1 datatype, column2 datatype, ...): Lists the columns in the table along with their data types.CONSTRAINT constraint_name UNIQUE (column1, column2, ...): Defines a unique constraint on the specified column(s). TheUNIQUEkeyword indicates that the values in these columns must be unique across all rows in the table.
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);
In this syntax:
ALTER TABLE table_name: Indicates that you are altering an existing table namedtable_name.ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...): Adds a unique constraint to the specified column(s) in the existing table. TheUNIQUEkeyword is used to define the uniqueness requirement.
Click below to access free SQL quizzes which will be helpful in your placement exams
Unique Constraints error in SQL
In SQL, a unique constraint violation error occurs when you attempt to insert or update data in a table, and the operation would result in duplicate values in a column or a combination of columns that are subject to a unique constraint.
Here’s an example to illustrate how a unique constraint error can occur:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
email VARCHAR(100) UNIQUE
);
In this example, the email column has a unique constraint, meaning that each email address in the employees table must be unique.
Now, suppose you try to insert a new employee with an email address that already exists in the table:
-- This will result in a unique constraint violation error INSERT INTO employees (employee_id, employee_name, email) VALUES (1, 'John Doe', 'john.doe@example.com');
When you execute this query, you will likely receive a unique constraint violation error. The exact error message may vary depending on the database system you are using. However, it typically indicates that the insertion violates the unique constraint on the email column.
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);
Let’s break down the syntax:
- ALTER TABLE table_name: Specifies that you are modifying an existing table named
table_name. - ADD CONSTRAINT constraint_name UNIQUE: Indicates that you are adding a new unique constraint, and you need to provide a unique name for the constraint.
- (column_name): Specifies the column or columns on which the unique constraint will be applied.
How to use unique in SQL queries?
Using the UNIQUE constraint in SQL primarily involves creating it during table creation or altering an existing table, as discussed in the previous responses.
However, when it comes to querying data, the UNIQUE constraint itself doesn’t have a direct impact on queries. Instead, it affects the behavior of data modification operations (e.g., inserts and updates) to ensure the uniqueness of values in specified columns.
Here are some scenarios where you might encounter the use of unique constraints in SQL queries:
Inserting Data:
When inserting data into a table with a unique constraint, you must ensure that the values in the constrained columns are unique. If you attempt to insert a duplicate value, the database will raise a constraint violation error.
Updating Data:
When updating data in a table with a unique constraint, you need to be careful not to violate the uniqueness requirement. If an update would result in duplicate values in the constrained columns, the database will raise an error.
Querying for Unique Values:
While the UNIQUE constraint itself doesn’t impact SELECT queries directly, you might use the DISTINCT keyword to retrieve unique values from a specific column or combination of columns.
Handling Errors:
When dealing with queries that involve data modification (inserts, updates), it’s essential to handle constraint violation errors appropriately. This could involve using error-handling mechanisms provided by your database management system or checking for constraint violations in your application code.
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
UPDATE table_name SET column1 = new_value WHERE condition;
SELECT DISTINCT column1, column2, ... FROM table_name;
Remember, the UNIQUE constraint is enforced at the time of data modification, not during SELECT queries.
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
