What is a primary key in SQL?

A primary key is a unique identifier for rows in a table. It ensures that no two rows have the same value in the primary key column(s). It cannot contain NULLs.

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies rows in its own table, while a foreign key is used to reference a primary key in another table to create a relationship.

Primary Key vs Foreign Key in SQL | FullStackPrep.Dev

💡 Concept Name

Primary Key uniquely identifies each row in a table. Foreign Key creates a relationship by referencing the primary key in another table.

📘 Quick Intro

In relational databases, primary and foreign keys are essential for enforcing uniqueness and maintaining data relationships. Primary keys prevent duplicate rows. Foreign keys enforce referential integrity between related tables.

🧠 Analogy / Short Story

Imagine a library: each book has a unique barcode—this is the primary key. Now, the borrowing record references this barcode to indicate which book was borrowed—that reference is a foreign key. The primary key ensures uniqueness, while the foreign key links related data across tables, just like library systems connect books and borrow logs.

🔧 Technical Explanation

🔑 A Primary Key ensures each row is uniquely identifiable. It must be unique and not null.
🔗 A Foreign Key creates a dependency between two tables by pointing to a primary key in another table.
🔒 Primary keys enforce entity integrity. Foreign keys enforce referential integrity.
🧭 Foreign keys can have duplicates and nulls (depending on constraints); primary keys cannot.
🔁 Foreign keys support cascading actions like DELETE or UPDATE based on the parent key's behavior.

🎯 Purpose & Use Case

✅ Use a primary key to uniquely identify records in a table (e.g., CustomerID).
✅ Use a foreign key to connect related data (e.g., CustomerID in Orders table).
✅ Ensure data consistency across multiple tables.
✅ Enable JOIN operations between relational entities.

💻 Real Code Example

-- Customers table with a primary key
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

-- Orders table with a foreign key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

❓ Interview Q&A

Q1: What is a primary key?
A: A column or set of columns that uniquely identifies each row in a table.

Q2: Can a table have more than one primary key?
A: No, but it can have a composite primary key (multiple columns).

Q3: What is a foreign key?
A: A column in one table that refers to the primary key in another table.

Q4: Can foreign keys be null?
A: Yes, unless explicitly declared as NOT NULL.

Q5: Do foreign keys need to be unique?
A: No, they can have duplicate values.

Q6: Can a foreign key reference a non-primary key column?
A: Only if that column has a UNIQUE constraint.

Q7: What is referential integrity?
A: It ensures that the foreign key value exists in the parent table.

Q8: What happens if you delete a row referenced by a foreign key?
A: It depends on the ON DELETE rule—RESTRICT, CASCADE, or SET NULL.

Q9: Can a primary key be NULL?
A: No, primary key columns must always be NOT NULL.

Q10: Can you create a foreign key across databases?
A: Not directly—you’d need to enforce it at the application level.

📝 MCQs

Q1. Which constraint uniquely identifies a row?

Foreign Key
Index
Primary Key
Unique Key

Q2. Can a foreign key have NULL values?

No
Yes
Only with index
Only with ON DELETE

Q3. Which key enforces referential integrity?

Primary Key
Foreign Key
Check
Trigger

Q4. What must a foreign key reference?

Any column
A primary key
A unique key in another table
A view

Q5. How many primary keys can a table have?

One
Unlimited
Zero
Two

Q6. Can foreign keys have duplicates?

No
Yes
Only once
Only with triggers

Q7. What is ON DELETE CASCADE used for?

Prevent deletion
Backup rows
To delete child rows automatically
Create index

Q8. Can a composite key be a primary key?

No
Yes
Only in MySQL
Only with index

Q9. What SQL keyword defines a foreign key?

REFERENCES
FOREIGN KEY
CONSTRAINT
JOIN

Q10. Which key ensures each row is unique?

Foreign Key
Index
Primary Key
Default Key

💡 Bonus Insight

Always define primary and foreign keys early in your database design. They not only prevent data corruption but also help query optimizers understand table relationships, leading to better performance and maintainability.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

📄 PDF Download Coming Soon

Primary Key vs Foreign Key in SQL