DAY-7 PRIMARY KEY,FOREIGN KEY,INDEX,TRANSACTION

1. PRIMARY KEY A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It enforces uniqueness and not-null constraints. Example: CREATE TABLE users ( id SERIAL PRIMARY KEY, -- Auto-incrementing primary key name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL ); 2. FOREIGN KEY A foreign key establishes a relationship between two tables. It ensures referential integrity by linking a column in one table to a primary key in another. Example: CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, -- Foreign key total_amount DECIMAL(10,2) NOT NULL ); ON DELETE CASCADE: If a user is deleted, all their orders will be deleted too. 3. INDEX An index speeds up query performance by optimizing data retrieval. Example: CREATE INDEX idx_users_email ON users(email); -- Index on email column for faster lookups Primary keys and unique constraints automatically create indexes. You can create indexes on frequently queried columns. 4. TRANSACTION A transaction ensures that multiple SQL statements execute atomically (either all succeed or all fail). Example: BEGIN; -- Start transaction INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); INSERT INTO orders (user_id, total_amount) VALUES (1, 150.00); COMMIT; -- Save changes -- Or use ROLLBACK; to undo in case of an error Summary PRIMARY KEY: Uniquely identifies a row. FOREIGN KEY: Links tables and ensures referential integrity. INDEX: Optimizes query performance. TRANSACTION: Ensures atomicity and consistency of operations. TRUNCATE (Removes all rows from a table, resets identity if specified) TRUNCATE TABLE table_name; If you want to reset auto-incremented IDs: TRUNCATE TABLE table_name RESTART IDENTITY; To cascade delete foreign key-related rows: TRUNCATE TABLE table_name CASCADE; DELETE (Removes specific rows based on a condition) DELETE FROM table_name WHERE condition; Delete all rows (without resetting identity): DELETE FROM table_name; Example: DELETE FROM employees WHERE department = 'HR'; INSERT (Adds new rows into a table) INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3); Insert multiple rows: INSERT INTO table_name (column1, column2) VALUES (value1a, value2a), (value1b, value2b); Insert with default values: INSERT INTO table_name DEFAULT VALUES; UPDATE (Modifies existing rows) UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; Example: UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT';

Apr 1, 2025 - 17:16

DAY-7 PRIMARY KEY,FOREIGN KEY,INDEX,TRANSACTION

1. PRIMARY KEY

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It enforces uniqueness and not-null constraints.

Example:

CREATE TABLE users (
id SERIAL PRIMARY KEY, -- Auto-incrementing primary key
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);

2. FOREIGN KEY

A foreign key establishes a relationship between two tables. It ensures referential integrity by linking a column in one table to a primary key in another.

Example:

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE, -- Foreign key
total_amount DECIMAL(10,2) NOT NULL
);

ON DELETE CASCADE: If a user is deleted, all their orders will be deleted too.

3. INDEX

An index speeds up query performance by optimizing data retrieval.

Example:

CREATE INDEX idx_users_email ON users(email); -- Index on email column for faster lookups

Primary keys and unique constraints automatically create indexes.

You can create indexes on frequently queried columns.

4. TRANSACTION

A transaction ensures that multiple SQL statements execute atomically (either all succeed or all fail).

Example:

BEGIN; -- Start transaction

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO orders (user_id, total_amount) VALUES (1, 150.00);

COMMIT; -- Save changes
-- Or use ROLLBACK; to undo in case of an error

Summary

PRIMARY KEY: Uniquely identifies a row.

FOREIGN KEY: Links tables and ensures referential integrity.

INDEX: Optimizes query performance.

**TRANSACTION**: Ensures atomicity and consistency of operations.

** TRUNCATE (Removes all rows from a table, resets identity if specified)**

TRUNCATE TABLE table_name;

If you want to reset auto-incremented IDs:

TRUNCATE TABLE table_name RESTART IDENTITY;

To cascade delete foreign key-related rows:

TRUNCATE TABLE table_name CASCADE;

DELETE (Removes specific rows based on a condition)

DELETE FROM table_name WHERE condition;

Delete all rows (without resetting identity):

DELETE FROM table_name;

Example:

DELETE FROM employees WHERE department = 'HR';

INSERT (Adds new rows into a table)

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

Insert multiple rows:

INSERT INTO table_name (column1, column2) VALUES
(value1a, value2a),
(value1b, value2b);

Insert with default values:

INSERT INTO table_name DEFAULT VALUES;

UPDATE (Modifies existing rows)

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:

UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT';