SQL Day 7 :

Primary Key vs Foreign Key

Primary Key

• Uniquely identifies each record in a table.
• Cannot have NULL values.
• A table can have only one primary key.
• Example:

CREATE TABLE Students (
      StudentID INT PRIMARY KEY,
      Name VARCHAR(50)
  );

Foreign Key

• Establishes a relationship between two tables.
• References the primary key of another table.
• Can have duplicate and NULL values.
• Example:

CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      StudentID INT,
      FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
  );

Where to Use Primary and Foreign Keys?

• Primary Key: Used in main tables where each record needs to be unique.
• Foreign Key: Used in child tables to maintain referential integrity.

Indexes in SQL

• Improves search and query performance.
• Types:
  • Unique Index: Prevents duplicate values.
  • Clustered Index: Sorts rows physically in the table.
  • Non-Clustered Index: Creates a logical order but does not affect the physical order.
• Example:

CREATE INDEX idx_name ON Students(Name);

Transactions in SQL

• A sequence of operations executed as a single unit.
• Ensures data integrity.
• ACID Properties:
  • Atomicity: All operations succeed or none.
  • Consistency: Data remains valid before and after.
  • Isolation: Transactions are independent.
  • Durability: Changes are permanent after commit.

Rollback, Commit, and Begin Transaction

• BEGIN TRANSACTION: Starts a transaction.
• COMMIT: Saves changes permanently.
• ROLLBACK: Undoes changes if an error occurs.

Example:

BEGIN TRANSACTION;
UPDATE Students SET Name = 'John' WHERE StudentID = 1;
ROLLBACK; -- Reverts the update
COMMIT; -- Saves changes

Special SQL Operators

• =#: Used for assignment.
• =*#: Wildcard characters in special SQL conditions.

This blog covers primary keys, foreign keys, indexing, transactions, and rollback operations in SQL. 🚀