Difference between primary key and foreign key with example

 Difference between primary key and foreign key with example


The Primary and foreign keys are essential concepts in relational databases that establish relationships between tables. Let's learn primary and foreign keys with examples step by step:

Primary Key:


A primary key is a column or a group of columns in a table that uniquely identifies each row. Its purpose is to ensure data integrity and uniqueness within the table. Here are some key characteristics of primary keys:


Data Integrity is used to restrict the invalid data into a table (filter invalid data). It can be achieved by providing the data type and constraints.


Uniqueness means, does not accept duplicated values.

Below is the syntax used to create a primary key on the table: 

CREATE TABLE <table_name> (
    id INT PRIMARY KEY,
    name VARCHAR(60),
    location VARCHAR(100)
);

Foreign Key: 


A foreign key is a column or a group of columns in one table that refers to the primary key of another table. Its purpose is to establish relationships and enable data linkage between tables. 

Below is the syntax used to create a foreign key on the table: 

CREATE TABLE  <table_name>(
    order_id INT PRIMARY KEY,
    cust_id INT,
    order_date DATE,
    total_amount_order DECIMAL(10, 2),
    FOREIGN KEY (cust_id) REFERENCES <primaryKey_table_name> (primaryKey_cust_id)
);


In the above example, we established the relationship between two tables using the primary key and the foreign key.



Difference between primary key and foreign key with example




The below Tables represents the difference between the primary key and the foreign key:

 

Primary Key Foreign Key
It is a column that is used to uniquely identified records. The creation of a primary key is not mandatory but it is highly recommended. It is a column that is used to establish a relationship between two tables. It is also called Referential Integrity constraints.
It is a combination of not null and unique constraints. So it can not accept null and duplicate values. It accepts null and duplicate values.
In a table, we can have only one primary key. In a table, we can have more than one foreign key.
It creates the primary table(master table). It creates on the child table. If we have a foreign key in the child table then that column must be the primary key for the master table.



Example:

create table orders(order_id int PRIMARY KEY, name VARCHAR(30), person_id int FOREIGN KEY REFERENCE PERSON(id));



In conclusion, primary keys uniquely identify rows within a table, while foreign keys establish relationships between tables by referencing the primary key of another table. These concepts are fundamental to relational databases, ensuring data integrity, and uniqueness, and facilitating efficient data retrieval and manipulation.


Post a Comment

0 Comments