Day 36 – SQL UNIQUE constraint
SQL UNIQUE constraint is used to ensure that each value in a column or a combination of columns is unique across all rows in a table. When you define a column with the UNIQUE constraint, it means that the values in that column must be unique for every row in the table.
Here’s an example of how to create a table with a UNIQUE constraint on a column:
CREATE TABLE students ( id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, age INT );
In this example, the “email” column has the UNIQUE constraint, which means that every value in this column must be unique across all rows in the “students” table. The “id”, “name”, and “age” columns do not have a UNIQUE constraint and can contain duplicate values.
If you try to insert a row into the “students” table with a value that already exists in the “email” column, SQL Server will return an error. For example, the following statement would fail:
INSERT INTO students (id, name, email, age) VALUES (1, 'John', 'email@example.com', 25);
This is because the value “firstname.lastname@example.org” already exists in the “email” column, and it has a UNIQUE constraint. To avoid this error, you need to provide a unique value for the “email” column in the INSERT statement or update the existing row with a different value.