10. Unique Key Constraint

• Type of database constraint that ensures that values in a column or combination of column 
• unique across all the rows in a table.
• Prevent duplicated data from entering into the table.

Syntax

While Creating the table 

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

    E.g.

        CREATE TABLE users (

        id INT PRIMARY KEY,

        name VARCHAR(50),

        email VARCHAR(50) UNIQUE,

        age INT

        );

For multiple columns

        CREATE TABLE table_name (

        column1 datatype,

        column2 datatype,

          ...

         CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

       );

    E.g.

        CREATE TABLE employees (

        id INT PRIMARY KEY,

        employee_id INT UNIQUE,

        department VARCHAR(50),

        salary DECIMAL(10, 2),

        CONSTRAINT unique_employee_dept UNIQUE (employee_id, department)

        );

//combination of employee id and department must be unique across all rows in the table

After Creating the table

        ALTER TABLE table_name

        ADD CONSTRAINT constraint_name UNIQUE (column_name);

        E.g. 

            ALTER TABLE employees

            ADD CONSTRAINT unique_employee_id UNIQUE (employee_id);

For Combination of Columns

        ALTER TABLE table_name

        ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

        E.g.

            ALTER TABLE employees

            ADD CONSTRAINT unique_employee_dept UNIQUE (employee_id, department);

• Difference between Primary key and Unique key 

• S.N.	Primary Key	Unique Key
  1.	Uniquely identifies each row in a table	Ensures that values in one or more columns are unique
  2.	Cannot contain null values	Can contain Null value but only one per column.
  3.	Can be used to create foreign key relationship with another table.	Cannot be used to create foreign key relationships with another table.
  4.	Each table can only have one primary key	A table can have more than one unique key