Unique could be a constraint in SQL that guarantees the values stored in a column or a group of columns are distinct from each other. It is frequently utilized to anticipate duplicate entries from being entered into a table. Interesting constraints can moreover be utilized to guarantee that no null values are stored in a column. When a Unique constraint is specified, SQL will automatically check each row within the table to guarantee that the esteem put away within the column or group of columns is one of a kind. On the off chance that a duplicate value is found, an mistake will be tossed.
SQL UNIQUE KEY constraint on CREATE TABLE:
A retail company was facing a unique challenge. They needed to extend their client dependability program but required a way to guarantee that clients couldn't sign up for the program different times and take advantage of the rewards. To do this, they chosen to utilize a Unique constraint in their SQL database. The Unique constraint guaranteed that no client might sign up for the dependability program more than once. In case a client endeavored to sign up once more, the database would return an error message and not permit the client to enlist. This permitted the company to secure their dependability program from extortion, whereas still giving their clients with the rewards they merited. Lets help them with it further.
Defining a unique key constraint on single column
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ..., columnN datatype constraint, CONSTRAINT key_name UNIQUE (column1) );
The SQL UNIQUE KEY constraint ensures that all values in a column are unique. It prevents duplicate values from being stored in the table. It is usually used in conjunction with the PRIMARY KEY constraint to create a composite key. The UNIQUE KEY constraint is an alternative to the PRIMARY KEY constraint.
CREATE TABLE products ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL, price DECIMAL(10,2) NOT NULL, UNIQUE KEY (product_name) );
This SQL statement creates a table called "products" with four columns: id, product_name, price, and a UNIQUE KEY constraint on the product_name column. The id column is set to auto-increment and the UNIQUE KEY constraint ensures that the product_name column values are unique, meaning no two rows in the table can have the same product_name value.
Defining a unique key constraint on multiple columns
CREATE TABLE table_name( col1 datatype, col2 datatype, col3 datatype, ... CONSTRAINT constraint_name UNIQUE(col1, col2, col3, ...) );
This statement creates a new table with the specified columns, data types, and constraint. The CONSTRAINT clause defines a unique key constraint on the specified columns, which ensures that all values in the columns are unique.
CREATE TABLE students ( first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, CONSTRAINT pk_students UNIQUE (first_name, last_name, email) );
The above SQL statement creates a table called 'students' with three columns: first_name, last_name, and email. It then defines a unique key constraint, named 'pk_students', which ensures that the combination of values in the first_name, last_name, and email columns must be unique across the table. This means that no two students can have the same first name, last name, and email address.
SQL UNIQUE KEY constraint on ALTER TABLE:
Defining a unique key constraint on single column
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column);
This statement adds a unique constraint to the specified table. This means that any combination of values across the specified column must be unique; no two rows can have the same values for those columns. The constraint_name is an optional name given to the constraint.
|Table Name||Column Name||Constraint|
ALTER TABLE people ADD CONSTRAINT unique_name UNIQUE (first_name);
This statement adds a unique constraint to the people table so that the combination of values across the first_name must be unique. No two rows in the people table can have the same first name. The constraint is given the name unique_name.
Defining a unique key constraint on multiple column
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);
This statement is used to add a unique constraint to a table. It specifies the name of the constraint and the columns to be included in the constraint. The UNIQUE keyword indicates that each combination of values in the specified columns must be unique. This ensures that no duplicate records can be inserted into the table.
ALTER TABLE student ADD CONSTRAINT UK_student UNIQUE (regno, name);
This statement will add a unique key constraint on the student table with the combination of the regno and name columns. This means that the combination of these two columns must be unique for every row in the table.
DROP SYNTAX FOR A FOREIGN KEY constraint:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
This statement is used to drop an existing foreign key constraint from a table. The table_name is the name of the table where the foreign key constraint is located and the constraint_name is the name of the foreign key constraint that should be dropped.
ALTER TABLE table_name ADD CONSTRAINT FK_constraint_name FOREIGN KEY (column_name) REFERENCES other_table (column_name);
This statement changes the table named "table_name" and adds a foreign key constraint named "FK_constraint_name." The foreign key is placed on the column named "column_name" and it is referencing the column named "column_name" within the table named "other_table.”
The UNIQUE constraint was a great success and allowed the company to increase the participation in their loyalty program. It also provided customers with the assurance that their loyalty rewards were safe and secure.
1. What is the keyword used to uniquely identify a row in a database table?
Answer: a. Primary Key
2. How can a Primary Key be composed?
Answer: d. Any of the Above
3. What type of integrity is enforced by a Unique Key?
Answer: a. Entity Integrity
4. What happens if a duplicate value is entered for a Unique Key?
Answer: a. An error is raised
Related Tutorials to watch
Top Articles toRead