Describe the relationship between the Primary key and the Foreign key with an example in SQL.

Question

Aryan Kumar · Answer

In SQL, a primary key is a unique identifier for each record in a table, and a foreign key is a field in one table that refers to the primary key in another table. The relationship between a primary key and a foreign key is used to establish a relationship between two tables.
Let's take an example of two tables, customers and orders. The
customers table has a primary key column called customer_id, and the
orders table has a foreign key column called customer_id that refers to the
customer_id column in the customers table.
Here's an example of how the two tables might look:
-- Customers table
CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 name VARCHAR(50),
 email VARCHAR(50)
);
-- Orders table
CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 total_price DECIMAL(10,2),
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
In this example, the customer_id column in the customers table is the primary key, and the
customer_id column in the orders table is a foreign key that references the
customer_id column in the customers table.
This relationship between the two tables ensures that each order is associated with a valid customer, and it helps maintain data integrity by preventing the creation of orders for non-existent customers.
Whenever a new order is added to the orders table, the value in the
customer_id column must already exist in the customers table. This is because the foreign key constraint prevents the creation of orders for non-existent customers.
In summary, the relationship between a primary key and a foreign key in SQL is used to establish a relationship between two tables, ensuring that data is consistent and accurate.

Krishnapriya Rajeev · Answer

The primary key acts as a unique identifier which helps us to identify each row in the table uniquely.and 
A foreign key is a column found in a table which utilizes the values present in the primary key of a separate table. The values in this column can either be an already existing primary key from the referenced table or null, and cannot be anything else.
//Creating table 1 
CREATE TABLE EMPLOYEES(emp_id int PRIMARY KEY, name varchar(20));
//Creating table 2 with a FOREIGN KEY
CREATE TABLE DEPENDANTS(dep_id int PRIMARY KEY, name varchar(20), emp_id int, FOREIGN KEY(emp_id) REFERENCES EMPLOYEE(emp_id));

forum

Describe the relationship between the Primary key and the Foreign key with an example in SQL.

Revati S Misra

2 Answers

Liked By