Primary and Foreign Key in SQL with Examples
In this tutorial, we will look at the concept of Primary and foreign key. Both the Primary and the foreign key are SQL constraints.
Constraints in SQL help us to manage the data and avoid any invalid transactions on it.
The primary key is limited to a single table and is put to uniquely identify the corresponding rows of a table.
When we talk about Foreign key, we can have as many Foreign keys as we want. A foreign key comes to use when we need to link tables to one another and have data spread over multiple tables.
Let us now dive into the concept of Primary and Foreign keys by understanding the definition, syntax, and examples of both keys.
What is a Primary Key in SQL?
A Primary key is a unique column we set in a table to easily identify and locate data in queries. A table can have only one primary key.
The primary key column has a unique value and doesn’t store repeating values. A Primary key can never take NULL values.
For example, in the case of a student when identification needs to be done in the class, the roll number of the student plays the role of Primary key.
Similarly, when we talk about employees in a company, the employee ID is functioning as the Primary key for identification.
Let us now understand the Syntax of creating the table with the Primary key specified.
CREATE TABLE tableName( col1int NOT NULL, col2varchar(50) NOT NULL, col3int, ……………. PRIMARY KEY(col1) );
What is a Foreign key in SQL?
A Foreign key is beneficial when we connect two or more tables so that data from both can be put to use parallelly.
A foreign key is a field or collection of fields in a table that refers to the Primary key of the other table. It is responsible for managing the relationship between the tables.
The table which contains the foreign key is often called the child table, and the table whose primary key is being referred by the foreign key is called the Parent Table.
When we talk about students and the courses they have enrolled in, now if we try to store all the data in a single table, the problem of redundancy arises.
To solve this table, we make two tables, one the student detail table and the other department table. In the student table, we store the details of students and the courses they have enrolled in.
And in the department table, we store all the details of the department. Here the courseId acts as the Primary key for the department table whereas it acts as the Foreign key in the student table.
Let us now look at the syntax of creating a table with a foreign key.
CREATE TABLE childTable( col1int NOT NULL, col2int NOT NULL, col3int, ………... PRIMARY KEY(col1), FOREIGN KEY(col3) REFERENCES parentTable(parent_Primary_key) );
Difference between Primary and Foreign Key
|Sr.No||Primary Key||Foreign Key|
|1||Used to maintain the unique identification of data in the table.||Used to maintain the relationship between two or more relational tables.|
|2||Helps us to identify data in a database table.||Helps to identify the data in another table using the connection with the foreign key.|
|3||A table can have only one Primary Key.||A table can have any number of Foreign Keys.|
|4||The primary key is unique and Not Null.||A foreign key can contain duplicate values also.|
|5||Primary key can’t take Null as a value.||A foreign key can take NULL entries also.|
|6||Primary Key can’t be modified once entered.||A foreign key can be modified at any instance of time.|
|7||We can have Primary keys for temporary tables as well.||We can’t have Foreign keys for the temporary tables.|
|8||A Primary key can be defined on its own.||For defining a Foreign key, we need a parent table with a Primary Key.|
|9||Primary key creates clustered indexes on the table.||Foreign key does not create indexes on the table neither clustered nor unclustered.|