SQL Keys - Primary, Composite, Foreign, Alternate

Keys in SQL table help to identify each row.

There are the following types of Keys defined:

• Primary Key
• Composite Key
• Foreign Key
• Alternate Key

1. Primary Key

         In a table, when one or more columns helps to uniquely identify a tuple/row is called Primary Key.

             - To define a primary key in sql of a table use PRIMARY KEY constraint on the column or field name.

        Example:

                CREATE TABLE Employee(

                            emp_id INT NOT NULL PRIMARY KEY,

                            emp_name,

                            --other columns            

                );

                        OR 

                CREATE TABLE Employee(

                            emp_id INT,

                            emp_name,

                            --other columns,

                            PRIMARY KEY(emp_id)

                );

 Note: 

            - Primary Key is introduced to maintain the Integrity of the database.

            - The value of Primary Key should be unique and it cannot have a null value

            - Table cannot contain duplicate value of Primary Key.

            - A table can have only one Primary Key.

    

            To remove primary key contraint from the table use DROP command as:

                    ALTER TABLE table_name DROP PRIMARY KEY;

2. Composite Key:

            A composite key is the combination of two or more column which uniquely identifies each row in a table. It is introduced because sometimes a single column cannot define the each row uniquely.

Hence we can say that a primary key made by combining one or more columns is called composite key.

            

            To define a composite key:

                    Syntax:

                            CREATE TABLE Employee(

                            emp_id INT,

                            emp_name,

                            --other columns,

                            PRIMARY KEY(emp_id, emp_name)

                            );

                                    OR

                           CREATE TABLE Employee(

                            emp_id INT,

                            emp_name,

                            --other columns,

                            CONSTRAINT primary_key_var PRIMARY KEY(emp_id, emp_name)

                );

                Here primary_key_var is the variable name which is a primary key/composite key and primary key is made by using two fields.

3. Foreign Key:

            Foreign key in a relational database is used to refer to a different table or used to link two tables together.

            Example:

                    Consider the following Employee and Department table

            Employee Table

 emp_id   emp_fname   emp_lname  dept _id        city           salary  

1	Aman	Singh	101	UK	65000
2	Pratik	Thapar	102	Delhi	62000
3	Anup	Mittal	101	UK	60000
4	Namita	Thankur	103	Delhi	55000
5	John	Doe	102	Bangalore	50000

            Department Table

dept_id	department_name
101	Mechanical
102	Computer Science
103	DevOps

                        

            In employee table the dept_id column is a foreign key because it is referring to the primary key of another table that is Department table.

            Syntax: To define a foreign key in sql

                   CREATE TABLE Employee(

                          emp_id INT NOT NULL PRIMARY KEY,

                          emp_fname VARCHAR(10),

                          emp_lname VARCHAR(10),

                          dept_id INT,

                          city VARCHAR(15),

                          salary FLOAT,

                          FOREIGN KEY(dept_id) REFERENCES Department(dept_id)

                   );

                   CREATE TABLE Department(

                            dept_id INT NOT NULL PRIMARY KEY,

                            department_name VARCHAR(30)

                    );

  Note:

        - Foreign Key can be null and column can contain duplicate value of foreign key as well.

        - There could be multiple foreign key in a table

4. Alternate Key:

            An alternate key is just a condidate key which is not selected as a Primary Key.

Continue Learning: Joins in SQL