ACID properties ensure database reliability:

• Atomicity: Transactions are all-or-nothing. If any part fails, the entire transaction rolls back
• Consistency: Database remains in a valid state before and after transactions
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed changes persist even after system failures

Example:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both operations succeed or both fail

PostgreSQL supports various data types:

• Numeric: INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION
• Character: CHAR, VARCHAR, TEXT
• Date/Time: DATE, TIME, TIMESTAMP, INTERVAL
• Boolean: BOOLEAN
• Binary: BYTEA
• JSON: JSON, JSONB
• Arrays: Any data type can be an array
• UUID: Universally Unique Identifiers
• Network: INET, CIDR for IP addresses

Both ensure uniqueness, but with key differences:

PRIMARY KEY:

• Cannot contain NULL values
• Only one per table
• Automatically creates a unique index
• Used for table relationships

UNIQUE:

• Can contain NULL values (multiple NULLs allowed)
• Multiple unique constraints per table
• Creates a unique index

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- Cannot be NULL
    email VARCHAR(255) UNIQUE,    -- Can have one NULL
    username VARCHAR(50) UNIQUE   -- Multiple unique constraints allowed
);

• DELETE: Removes specific rows based on conditions, can be rolled back, triggers fire
• TRUNCATE: Removes all rows quickly, can be rolled back, triggers don't fire
• DROP: Removes the entire table structure and data, cannot be rolled back

DELETE FROM users WHERE age < 18;  -- Removes specific rows
TRUNCATE TABLE users;              -- Removes all rows
DROP TABLE users;                  -- Removes table entirely

A foreign key establishes and enforces a link between data in two tables. It prevents actions that would destroy links between tables.

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INTEGER REFERENCES departments(dept_id)
);

Foreign key constraints ensure referential integrity by preventing:

• Insertion of records with non-existent foreign key values
• Deletion of referenced records (unless CASCADE is specified)