Difference Between Primary Key And Foreign Key
Summary: You will learn in this article, the difference between primary key and foreign key with examples. there are two keys – primary key and foreign key which seem identical, but actually, both are different in features and behaviors, Let’s understand the Difference Between Primary Key and Foreign Key.
Difference Between Primary Key And Foreign Key
| Primary Key (PK) | Foreign Key (FK) |
|---|---|
| The primary key is used to uniquely identify every row in a database table | A foreign key is used to connect two tables or maintain the relationship between them |
| A table can have only have one PK-primary key | A table can have multiple foreign keys |
| The primary key can’t store the null values | A foreign key can have multiple null values |
| The primary key can not be deleted from the table unless all the references are deleted from other tables. | Foreign keys can be deleted |
What Are keys?
As you know that for any type of database, all data is stored in the form of rows and columns. however, let us say you need to find a unique row in a DB. If you want to connect two tables, however, would you be doing that?
There are several types of keys a database contains.
Each key has its uses and properties.
Let’s discuss this with an example of a customer. We have a customer table here with the following columns.
Customer Table
| Customer_ID | Customer_Name | Customer_Mobile | Customer_City |
|---|
In this customer table if you want to find the details of a particular customer how would you do that well you will say I can find by customer_name but many customers can have similar names then how would you do that.
Therefore, Here the concept of keys becomes very important. In this article, we will together see the use of Primary Key and Foreign keys so let’s dive right into it.
What is Primary Key?
A PK-primary key refers to a particular choice of columns that uniquely determines the set of rows in a table. Furthermore, a primary key is a single attribute that has a unique ID and is also considered a candidate key.
There are two types of primary keys
Simple Primary Key: A simple primary key is a database table made of only one(1) column
Composite Primary Key: The composite primary key is in a database table with more than one column.
The PK-primary key can’t contain a null value in that table. Additionally, no two rows will have an identical primary key as this would not result in the database being unique. Therefore, there can be only one(1) primary key for every row of data in a table.
The Primary key is cluster-indexed means that all the rows in the table are sorted. The PK-primary key is mainly defined on a temporary table. Hence, When one is deleting rows from a table they must be careful that the deleted value is not present in the FK-foreign key column.
Syntax for creation of Primary Key
|
1 2 3 4 5 6 |
CREATE TABLE table_name( column1 datatype, column2 datatype, ..., PRIMARY KEY(column_list) ); |
Syntax for Dropping Primary key
|
1 2 |
ALTER TABLE table_name DROP PRIMARY KEY; |
A table can have only one(1) primary key (PK).
The PK-primary key is often denoted as.
Guess what you can make the primary key by combining two or more columns this is also called the composite key.
The primary key cannot be null.
Furthermore, any value or column which can uniquely identify a row in a table is known as a Primary key.
What is Foreign Key?
An FK-foreign key refers to a column in a database table that provides a link or connection between two tables. Furthermore, foreign keys can contain null values as it doesn’t help in identifying a difference in the relation.
Thus, Foreign keys can also accept duplicate values unlike the primary key and can have several or multiple foreign keys in a database as foreign keys can have different attributes. However, foreign keys can’t have a clustered index.
FK can’t be defined on a temporary table. whereas adding a value into the FK-foreign key column, one must make sure the value is present in the PK-primary key and there’s no constraint when deleting values from a foreign key.
Therefore, the Foreign tables help or facilitate in maintaining the integrity of tables in the database. Furthermore, the accidental deletion of any value can be prevented by the database management system.
Syntax for creation of Foreign Key
|
1 2 3 4 5 |
CREATE TABLE <Table Name>( column1 datatype, column2 datatype, FOREIGN KEY [column1, column2...] REFERENCES [primary key table name] (List of primary key table column) ...); |
Syntax for Dropping Foreign Key
|
1 2 |
ALTER TABLE table_name DROP FOREIGN KEY; |
The foreign key is denoted by FK.
A table with a foreign key is called a child table and a table with a primary key is called a parent table or a referenced table.
Foreign keys can have multiple null values.
What Is The Relation Between Primary Key And Foreign Key?
An FK-foreign key is a column or a set of columns or a collection of columns in one table that references the primary key columns in another table. The PK-primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table.
| Why use Primary Key? | Why use Foreign Key? |
|---|---|
| The primary key is used to find unique records in the table. | You can use a foreign key when you want to connect two tables or maintain a relationship between them |
| You can use a Primary key when you want to store a non-null value for a particular column and it should be unique for example a roll no of a student Student table. | FK-foreign keys can use when you want to connect two or more tables. |
| You don’t want duplicate rows. | Foreign Keys help to maintain referential integrity. |
What’s The Benefit Of Having Primary And Foreign Keys?
- Primary keys don’t permit two rows in a database table to have duplicate values. Whereas FK-Foreign keys allow two rows in a database table to have duplicate values.
- In the attribute of the PK-primary keys, values can be inserted in the table even if the foreign key doesn’t have that value in its column. In the foreign keys attribute, values can’t be inserted in the table if the values aren’t present in the primary key database.
- A primary key (PK) attribute can have only one(1) range of primary keys in its table. Whereas a foreign key attribute can have a wide range of FK-foreign keys in its table.
- Primary keys have a clustered index, which implies that all the rows in the database relation are sorted. Whereas FK-Foreign keys don’t have an automatic clustered index however they can be done manually.
- Under primary keys, a value can be deleted from the referencing table by ensuring that the value is not present in the FK-foreign key reference table. Whereas the under foreign keys, the values can be deleted with no glitches since it doesn’t matter whether or not the value is still present in the PK-primary keys reference table or not.
- PK can be determined on a temporary table. Whereas an FK-Foreign key can’t be determined or enforced on a temporary table.
Conclusion:
In this article, we looked at the use and difference between primary keys and foreign keys. and how you can use them to make your database design. Furthermore, in the database management system, keys play the most important role in establishing relationships inside a table and among different tables similarly. however, to try and do this, we must make sure that the areas we use to maintain relationships b/w different tables must have comparable values and the table should comprise unique rows.
This tutorial article talks about the two very essential and common keys that create links or connections among tables and help the relational database management systems work efficiently.
About Author
