SQL-Unique Key - Fullstackadda

A unique key in SQL is a constraint that ensures that the values in a specific column or set of columns are distinct across all rows in a table. This means that no two rows can have the same value in the specified column(s). An example of a unique key in SQL is as follows: SQL-Unique Key

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    CONSTRAINT employee_id_pk PRIMARY KEY (employee_id)
);

In the example above, the employee_id column is designated as the primary key and is also a unique key, meaning that no two employees can have the same employee_id value

Here is an another example of how to create a table with a unique key constraint in SQL:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

In this example, we are creating a table called “example_table” with three columns: “id”, “name”, and “email”. The “id” column is defined as the primary key, and the “name” column is defined as NOT NULL, which means it cannot contain a null value. The “email” column is defined as UNIQUE, which means it must have unique values across all rows in the table.

To insert data in the table

INSERT INTO example_table (id, name, email)
VALUES (1, 'Surya Singam', 'suryasingam@example.com');

If we try to insert duplicate email

INSERT INTO example_table (id, name, email)
VALUES (2, 'Shiva Singam', 'Suryasingam@example.com');

This query will return an error because the “email” column is defined as UNIQUE and the value “suryasingam@example.com” already exists in the table.

Please note that the syntax for creating tables and inserting data may vary depending on the specific database management system you are using.

When to use

A unique key is used to enforce the uniqueness of the data in a column or set of columns. It ensures that no two rows in a table have the same data in the unique key columns.

A primary key is used to uniquely identify each row in a table. It is used to enforce the integrity of the data and to create relationships with other tables through foreign keys.

A foreign key is used to establish a link between the data in two tables. It is used to enforce referential integrity, which means that the data in one table must match the data in the related table.

In summary:

Unique key is used to ensure that the data in a column or set of columns is unique across the table.
Primary key is used to identify a unique row in a table
Foreign key is used to create a relationship between two tables

Here is a table that summarizes the main differences between primary key, foreign key, and unique key constraints in SQL:

Constraint	Definition	Number of allowed in table	NULL allowed	Link with other tables
Primary Key	A column or set of columns that uniquely identifies each row in a table	1	No	N/A
Foreign Key	A column or set of columns that references a primary key in another table	Multiple	No	Yes
Unique Key	A column or set of columns that must have unique values across all rows in a table	Multiple	Yes	N/A

SQL-Unique Key SQL-Unique Key SQL-Unique Key SQL-Unique Key SQL-Unique Key