SQL Day 7: Foreign Key, Primary Key, Index, Transactions, and Rollback

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.

Apr 1, 2025 - 18:23

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

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

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);

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.

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

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