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', '[e-mail]');
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';