CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   email VARCHAR(255) UNIQUE,
   age INT CHECK (age >= 0),
   created_at TIMESTAMP DEFAULT NOW()
);

-- Добавить колонку
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Удалить колонку
ALTER TABLE users DROP COLUMN phone;

-- Изменить тип
ALTER TABLE users ALTER COLUMN name TYPE TEXT;

-- Добавить ограничение
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);

-- Переименовать колонку
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Переименовать таблицу
ALTER TABLE users RENAME TO customers;


Важно: ALTER на больших таблицах может заблокировать их надолго. В production делайте это осторожно!

DROP TABLE users;              -- ошибка если не существует
DROP TABLE IF EXISTS users;    -- безопасно
DROP TABLE users CASCADE;      -- удалить с зависимостями

// Junior developer создал таблицу без PRIMARY KEY
@Entity
@Table(name = "orders")
public class Order {
    // Нет @Id! Нет PRIMARY KEY!
    private String orderNumber;
    private BigDecimal amount;
}

// Через неделю в production:
INSERT INTO orders (order_number, amount) VALUES ('ORD-001', 100.00);
INSERT INTO orders (order_number, amount) VALUES ('ORD-001', 100.00);  // дубликат!
INSERT INTO orders (order_number, amount) VALUES ('ORD-001', 150.00);  // ещё один!

// Теперь:
SELECT * FROM orders WHERE order_number = 'ORD-001';
-- Результат: 3 строки! Какая правильная? Никто не знает!

// Попытка обновить:
UPDATE orders SET amount = 200 WHERE order_number = 'ORD-001';
-- Обновились ВСЕ 3 строки!

// Попытка удалить дубликаты:
DELETE FROM orders WHERE ??? -- По какому полю? Нет уникального ID!

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,         -- автоинкремент + уникальность + индекс
    order_number VARCHAR NOT NULL,
    amount NUMERIC(10,2)
);

-- Попытка вставить:
INSERT INTO orders (order_number, amount) VALUES ('ORD-001', 100.00);  -- id=1
INSERT INTO orders (order_number, amount) VALUES ('ORD-001', 100.00);  -- id=2 (разрешено!)

-- НО теперь можем различить:
SELECT * FROM orders WHERE id = 1;  -- конкретная запись

-- Обновление конкретной записи:
UPDATE orders SET amount = 200 WHERE id = 1;  -- только одна запись

-- Удаление конкретной записи:
DELETE FROM orders WHERE id = 2;  -- только одна запись

-- 1. SERIAL (автоинкремент) — самый частый
CREATE TABLE users (
    id SERIAL PRIMARY KEY  -- автоматически: 1, 2, 3, 4...
);

-- 2. UUID (глобально уникальный)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
-- Преимущество: уникален даже между БД (для репликации, микросервисов)

-- 3. Натуральный ключ (из данных)
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY  -- 'US', 'RU', 'GB'
);
-- ⚠️ Используй редко! Данные могут измениться (телефон, email)

-- 4. Составной ключ (комбинация полей)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- уникальна пара (order_id, product_id)
);
-- Нельзя: два раза один товар в одном заказе

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)  // SERIAL
    private Long id;

    private String email;
    private String name;
}

// Hibernate сгенерирует:
// CREATE TABLE users (
//     id BIGSERIAL PRIMARY KEY,
//     email VARCHAR(255),
//     name VARCHAR(255)
// );

-- Без PRIMARY KEY:
SELECT * FROM orders WHERE order_number = 'ORD-12345';
-- Seq Scan on orders (cost=0.00..1500.00 rows=100 width=40) (actual time=234.123..234.456 rows=1 loops=1)
-- Execution Time: 234.456 ms  ← медленно!

-- С PRIMARY KEY (индекс автоматически):
SELECT * FROM orders WHERE id = 12345;
-- Index Scan using orders_pkey on orders (cost=0.29..8.30 rows=1 width=40) (actual time=0.012..0.013 rows=1 loops=1)
-- Execution Time: 0.013 ms  ← в 18000x быстрее!

-- ❌ ОШИБКА: попытка вставить дубликат
INSERT INTO users (id, email) VALUES (1, 'test@test.com');
INSERT INTO users (id, email) VALUES (1, 'other@test.com');
-- ERROR: duplicate key value violates unique constraint "users_pkey"

-- ❌ ОШИБКА: попытка вставить NULL
INSERT INTO users (id, email) VALUES (NULL, 'test@test.com');
-- ERROR: null value in column "id" of relation "users" violates not-null constraint

-- ⚠️ Проблема: "прыгающие" ID после DELETE
INSERT INTO users (email) VALUES ('user1@test.com');  -- id=1
INSERT INTO users (email) VALUES ('user2@test.com');  -- id=2
DELETE FROM users WHERE id = 1;
INSERT INTO users (email) VALUES ('user3@test.com');  -- id=3 (не переиспользует 1!)
-- SERIAL не заполняет пропуски, это нормально

03:15 - Маркетолог удаляет тестовый аккаунт user_id=123
03:16 - В БД остаются "осиротевшие" заказы orders.user_id=123
08:00 - Бухгалтер формирует отчёт: "У 47 заказов на 2.3 млн руб. нет владельца!"
09:00 - Техподдержка не может связаться с клиентами по заказам
10:00 - Юристы паникуют: нарушение GDPR (заказы без пользователей = неудаляемые ПД)

-- ❌ БЕЗ FOREIGN KEY (катастрофа):
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,  -- просто число, без связи!
    total DECIMAL(10,2)
);

-- Можно вставить заказ с несуществующим пользователем:
INSERT INTO orders (user_id, total) VALUES (999999, 100.00);  -- OK! user_id=999999 не существует

-- Можно удалить пользователя, оставив его заказы:
DELETE FROM users WHERE id = 123;  -- OK! orders.user_id=123 остались "сиротами"

-- Поиск проблем:
SELECT COUNT(*)
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 47 осиротевших заказов!

-- ✅ С FOREIGN KEY (защита):
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),  -- строгая связь!
    total DECIMAL(10,2)
);

-- Теперь нельзя вставить заказ с несуществующим пользователем:
INSERT INTO orders (user_id, total) VALUES (999999, 100.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (user_id)=(999999) is not present in table "users".

-- Нельзя удалить пользователя, пока есть его заказы:
DELETE FROM users WHERE id = 123;
-- ERROR: update or delete on table "users" violates foreign key constraint
-- DETAIL: Key (id)=(123) is still referenced from table "orders".

ОДИН-КО-МНОГИМ (One-to-Many) — самая частая:
┌──────────────┐         ┌──────────────┐
│   users      │ 1     N │   orders     │
│──────────────│────────>│──────────────│
│ id (PK)      │         │ id (PK)      │
│ name         │         │ user_id (FK) │
└──────────────┘         │ total        │
                         └──────────────┘
Один пользователь → много заказов

МНОГИЕ-КО-МНОГИМ (Many-to-Many) — через связующую таблицу:
┌──────────────┐         ┌─────────────────┐         ┌──────────────┐
│   students   │ N     N │ student_courses │ N     1 │   courses    │
│──────────────│────────>│─────────────────│<────────│──────────────│
│ id (PK)      │         │ student_id (FK) │         │ id (PK)      │
│ name         │         │ course_id (FK)  │         │ title        │
└──────────────┘         └─────────────────┘         └──────────────┘
Студент учится на многих курсах, курс имеет многих студентов

ОДИН-К-ОДНОМУ (One-to-One) — редкая, для разделения данных:
┌──────────────┐         ┌──────────────────┐
│   users      │ 1     1 │  user_profiles   │
│──────────────│────────>│──────────────────│
│ id (PK)      │         │ user_id (PK, FK) │ ← PK и FK одновременно!
│ email        │         │ bio              │
└──────────────┘         │ avatar_url       │
                         └──────────────────┘
Один пользователь → один профиль

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // Один пользователь → много заказов
    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Order> orders = new ArrayList<>();
}

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)  // FOREIGN KEY
    private User user;

    private BigDecimal total;
}

// Hibernate сгенерирует:
// CREATE TABLE orders (
//     id BIGSERIAL PRIMARY KEY,
//     user_id BIGINT NOT NULL REFERENCES users(id),
//     total DECIMAL(10,2)
// );

@Entity
@Table(name = "students")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @ManyToMany
    @JoinTable(
        name = "student_courses",
        joinColumns = @JoinColumn(name = "student_id"),
        inverseJoinColumns = @JoinColumn(name = "course_id")
    )
    private Set<Course> courses = new HashSet<>();
}

@Entity
@Table(name = "courses")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @ManyToMany(mappedBy = "courses")
    private Set<Student> students = new HashSet<>();
}

// Hibernate сгенерирует:
// CREATE TABLE student_courses (
//     student_id BIGINT REFERENCES students(id),
//     course_id BIGINT REFERENCES courses(id),
//     PRIMARY KEY (student_id, course_id)
// );

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String email;

    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    private UserProfile profile;
}

@Entity
@Table(name = "user_profiles")
public class UserProfile {
    @Id
    private Long userId;  // тот же ID что у User

    @OneToOne
    @MapsId  // использует userId как PK и FK одновременно
    @JoinColumn(name = "user_id")
    private User user;

    private String bio;
    private String avatarUrl;
}

// Hibernate сгенерирует:
// CREATE TABLE user_profiles (
//     user_id BIGINT PRIMARY KEY REFERENCES users(id),
//     bio TEXT,
//     avatar_url VARCHAR(500)
// );

@Service
public class OrderService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void createOrder(Long userId, BigDecimal total) {
        // FOREIGN KEY автоматически проверит существование user_id:
        jdbcTemplate.update(
            "INSERT INTO orders (user_id, total, created_at) VALUES (?, ?, NOW())",
            userId, total
        );
        // Если userId не существует → SQLException: foreign key violation
    }

    public void deleteUser(Long userId) {
        try {
            jdbcTemplate.update("DELETE FROM users WHERE id = ?", userId);
        } catch (DataIntegrityViolationException e) {
            // FOREIGN KEY защитил от удаления пользователя с заказами!
            throw new BusinessException(
                "Невозможно удалить пользователя: у него есть заказы. " +
                "Сначала обработайте/удалите заказы."
            );
        }
    }
}

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INT NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id INT REFERENCES users(id) ON DELETE SET NULL,
    content TEXT NOT NULL
);

-- Удалили пост → комментарии автоматически удалятся (CASCADE)
-- Удалили автора комментария → комментарий остаётся, но author_id=NULL (SET NULL)

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,  -- автоматически создаётся индекс orders_pkey
    user_id INT REFERENCES users(id)  -- индекс НЕ создаётся!
);

-- ⚠️ БЕЗ индекса на user_id:
SELECT * FROM orders WHERE user_id = 123;
-- Seq Scan on orders (cost=0.00..1500.00 rows=100 width=40) (actual time=234.123..234.456 rows=10 loops=1)
-- Execution Time: 234.456 ms  ← медленно!

-- ✅ ДОБАВИТЬ индекс вручную:
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Теперь быстро:
SELECT * FROM orders WHERE user_id = 123;
-- Index Scan using idx_orders_user_id on orders (cost=0.29..8.30 rows=10 width=40) (actual time=0.012..0.023 rows=10 loops=1)
-- Execution Time: 0.023 ms  ← в 10000x быстрее!

INSERT INTO orders (user_id, total) VALUES (999999, 100.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
-- DETAIL: Key (user_id)=(999999) is not present in table "users".

-- Решение: проверяй существование user_id перед вставкой

public void createOrder(Long userId, BigDecimal total) {
    // Проверка существования пользователя:
    boolean userExists = jdbcTemplate.queryForObject(
        "SELECT EXISTS(SELECT 1 FROM users WHERE id = ?)",
        Boolean.class, userId
    );

    if (!userExists) {
        throw new NotFoundException("Пользователь с id=" + userId + " не найден");
    }

    jdbcTemplate.update(
        "INSERT INTO orders (user_id, total) VALUES (?, ?)",
        userId, total
    );
}

DELETE FROM users WHERE id = 123;
-- ERROR: update or delete on table "users" violates foreign key constraint "orders_user_id_fkey"
-- DETAIL: Key (id)=(123) is still referenced from table "orders".

-- Решение 1: Сначала удалить все заказы
DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;

-- Решение 2: Использовать ON DELETE CASCADE (см. следующий вопрос)

-- ❌ ОШИБКА: циклическая зависимость
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    manager_id INT REFERENCES employees(id)  -- ссылка на employees
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    department_id INT REFERENCES departments(id)  -- ссылка на departments
);
-- ERROR: relation "employees" does not exist

-- ✅ Решение: сначала создать таблицы без FK, потом добавить:
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    manager_id INT
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    department_id INT REFERENCES departments(id)
);

ALTER TABLE departments
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employees(id);

-- При загрузке 1 млн строк, каждая проверка FK занимает время
-- Временно отключить FK для bulk insert:
ALTER TABLE orders DISABLE TRIGGER ALL;  -- отключить все триггеры (включая FK)

COPY orders FROM '/tmp/orders.csv' WITH CSV;  -- быстрая загрузка

ALTER TABLE orders ENABLE TRIGGER ALL;  -- включить обратно

-- ⚠️ Опасно! Используй только если уверен в целостности данных

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT
);

CREATE TABLE likes (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

┌───────────────────────────────────────────────────────────┐
│ 10:00 - Админ удаляет спам-пользователя user_id=42        │
│         DELETE FROM users WHERE id = 42;                  │
└───────────────────────────────────────────────────────────┘
                           ↓ CASCADE
┌───────────────────────────────────────────────────────────┐
│ Автоматически удаляется:                                  │
│ • 347 постов пользователя (posts.user_id=42)              │
│ • 2,891 комментарий к этим постам (comments.post_id→post) │
│ • 1,204 комментария пользователя (comments.user_id=42)    │
│ • 18,456 лайков к этим постам (likes.post_id→post)        │
│ • 5,123 лайка самого пользователя (likes.user_id=42)      │
│                                                           │
│ ИТОГО: Одна команда → удалено 28,021 записей!             │
│                                                           │
│ Это может вызвать:                                        │
│ 1. Долгую блокировку таблиц                               │
│ 2. Нагрузку на БД                                         │
└───────────────────────────────────────────────────────────┘

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void deleteUserWithCascade(Long userId) {
        // 1. Сначала посчитать, что удалится:
        Map<String, Integer> affectedData = jdbcTemplate.queryForObject("""
            SELECT
                COUNT(DISTINCT p.id) AS posts,
                COUNT(DISTINCT c.id) AS comments,
                COUNT(DISTINCT l.id) AS likes
            FROM users u
            LEFT JOIN posts p ON p.user_id = u.id
            LEFT JOIN comments c ON c.post_id = p.id
            LEFT JOIN likes l ON l.post_id = p.id OR l.user_id = u.id
            WHERE u.id = ?
            """,
            (rs, rowNum) -> Map.of(
                "posts", rs.getInt("posts"),
                "comments", rs.getInt("comments"),
                "likes", rs.getInt("likes")
            ),
            userId
        );

        int totalAffected = affectedData.values().stream().mapToInt(Integer::intValue).sum();

        // 2. Если удаляется больше 100 записей → требовать подтверждение:
        if (totalAffected > 100) {
            throw new BusinessException(
                String.format("Удаление пользователя приведёт к удалению %d связанных записей. " +
                    "Требуется подтверждение администратора.", totalAffected)
            );
        }

        // 3. Логировать перед удалением:
        log.warn("Deleting user_id={} with CASCADE: {} posts, {} comments, {} likes",
            userId, affectedData.get("posts"), affectedData.get("comments"), affectedData.get("likes"));

        // 4. Удалить (CASCADE сработает автоматически):
        jdbcTemplate.update("DELETE FROM users WHERE id = ?", userId);
    }
}

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,  -- удалили пост → удалить комментарий
    author_id INT REFERENCES users(id) ON DELETE SET NULL,        -- удалили автора → оставить комментарий
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

До удаления:
┌────┬─────────┬───────────┬──────────────────────────────┐
│ id │ post_id │ author_id │ content                      │
├────┼─────────┼───────────┼──────────────────────────────┤
│ 1  │ 10      │ 42        │ "Отличная статья!"           │
│ 2  │ 10      │ 42        │ "Спасибо за информацию"      │
│ 3  │ 10      │ 99        │ "Не согласен с автором"      │
└────┴─────────┴───────────┴──────────────────────────────┘

DELETE FROM users WHERE id = 42;
                   ↓ ON DELETE SET NULL

После удаления:
┌────┬─────────┬───────────┬──────────────────────────────┐
│ id │ post_id │ author_id │ content                      │
├────┼─────────┼───────────┼──────────────────────────────┤
│ 1  │ 10      │ NULL ←────┼ "Отличная статья!"           │
│ 2  │ 10      │ NULL ←────┼ "Спасибо за информацию"      │
│ 3  │ 10      │ 99        │ "Не согласен с автором"      │
└────┴─────────┴───────────┴──────────────────────────────┘

В UI отображается: "Удалённый пользователь" вместо username

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,  -- или просто REFERENCES (RESTRICT по умолчанию)
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2)
);

┌──────────────────────┬───────────────────────────────────────┐
│ categories           │ products                              │
├────┬─────────────────┼────┬─────────────┬──────────────┬─────┤
│ id │ name            │ id │ category_id │ name         │price│
├────┼─────────────────┼────┼─────────────┼──────────────┼─────┤
│ 1  │ Электроника     │ 10 │ 1           │ iPhone 15    │ 999 │
│ 2  │ Одежда          │ 11 │ 1           │ MacBook Pro  │2499 │
│ 3  │ Книги           │ 12 │ 1           │ AirPods Pro  │ 249 │
└────┴─────────────────┴────┴─────────────┴──────────────┴─────┘

           DELETE FROM categories WHERE id = 1;
                            ↓
┌──────────────────────────────────────────────────────────────────┐
│ ❌ ERROR: update or delete on table "categories"                 │
│    violates foreign key constraint "products_category_id_fkey"   │
│ DETAIL: Key (id)=(1) is still referenced from table "products"   │
└──────────────────────────────────────────────────────────────────┘

Защита сработала! Нельзя удалить категорию, пока есть товары.

┌──────────────────────┬───────────────────────────────────────┐
│ categories           │ products                              │
├────┬─────────────────┼────┬─────────────┬──────────────┬─────┤
│ id │ name            │ id │ category_id │ name         │price│
├────┼─────────────────┼────┼─────────────┼──────────────┼─────┤
│ 1  │ Электроника     │ 10 │ 1           │ iPhone 15    │ 999 │
│ 2  │ Одежда          │ 11 │ 1           │ MacBook Pro  │2499 │
│ 3  │ Книги           │ 12 │ 1           │ AirPods Pro  │ 249 │
└────┴─────────────────┴────┴─────────────┴──────────────┴─────┘

           DELETE FROM categories WHERE id = 1;
                            ↓
┌──────────────────────────────────────────────────────────────────┐
│ ❌ ERROR: update or delete on table "categories"                 │
│    violates foreign key constraint "products_category_id_fkey"   │
│ DETAIL: Key (id)=(1) is still referenced from table "products"   │
└──────────────────────────────────────────────────────────────────┘

Защита сработала! Нельзя удалить категорию, пока есть товары.

@Service
public class CategoryService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void deleteCategory(Long categoryId) {
        // 1. Проверить, есть ли товары в категории:
        Integer productCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM products WHERE category_id = ?",
            Integer.class,
            categoryId
        );

        if (productCount > 0) {
            // 2. Вернуть понятную ошибку ПЕРЕД попыткой удаления:
            throw new BusinessException(
                String.format("Невозможно удалить категорию: в ней %d товаров. " +
                    "Сначала переместите товары в другую категорию или удалите их.", productCount)
            );
        }

        // 3. Удалить категорию (безопасно, т.к. нет товаров):
        jdbcTemplate.update("DELETE FROM categories WHERE id = ?", categoryId);
    }

    @Transactional
    public void moveCategoryProducts(Long fromCategoryId, Long toCategoryId) {
        // Переместить все товары в другую категорию перед удалением:
        jdbcTemplate.update(
            "UPDATE products SET category_id = ? WHERE category_id = ?",
            toCategoryId, fromCategoryId
        );

        // Теперь можно безопасно удалить категорию:
        jdbcTemplate.update("DELETE FROM categories WHERE id = ?", fromCategoryId);
    }
}

-- 1. Создать категорию "Без категории":
INSERT INTO categories (id, name) VALUES (0, 'Без категории');

-- 2. Использовать её как DEFAULT:
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INT DEFAULT 0 REFERENCES categories(id) ON DELETE SET DEFAULT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2)
);

До удаления:
┌────┬─────────────┬────────────────┬─────────┐
│ id │ category_id │ name           │ price   │
├────┼─────────────┼────────────────┼─────────┤
│ 10 │ 5           │ iPhone 15      │  999.00 │
│ 11 │ 5           │ Samsung S24    │  899.00 │
│ 12 │ 3           │ MacBook Pro    │ 2499.00 │
└────┴─────────────┴────────────────┴─────────┘

DELETE FROM categories WHERE id = 5;
                   ↓ ON DELETE SET DEFAULT (category_id = 0)

После удаления:
┌────┬─────────────┬────────────────┬─────────┐
│ id │ category_id │ name           │ price   │
├────┼─────────────┼────────────────┼─────────┤
│ 10 │ 0 ←─────────│ iPhone 15      │  999.00 │ (была 5)
│ 11 │ 0 ←─────────│ Samsung S24    │  899.00 │ (была 5)
│ 12 │ 3           │ MacBook Pro    │ 2499.00 │
└────┴─────────────┴────────────────┴─────────┘

Товары переместились в категорию "Без категории"

-- Проблема: departments.manager_id → employees.id
--           employees.department_id → departments.id

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id) DEFERRABLE INITIALLY DEFERRED
);

ALTER TABLE departments
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id) REFERENCES employees(id)
DEFERRABLE INITIALLY DEFERRED;

-- Теперь можно вставить взаимосвязанные записи в одной транзакции:
BEGIN;
    INSERT INTO departments (id, name, manager_id) VALUES (1, 'IT', 100);      -- manager_id=100 пока не существует
    INSERT INTO employees (id, name, department_id) VALUES (100, 'John', 1);  -- но FK проверка отложена
COMMIT;  -- проверка FK здесь, и всё ОК

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON UPDATE CASCADE  -- если изменится users.id → изменится orders.user_id
);

-- Изменение PRIMARY KEY (плохая практика, но CASCADE поможет):
UPDATE users SET id = 999 WHERE id = 123;
-- Автоматически обновится orders.user_id: 123 → 999

-- ❌ ОПАСНО: удалили всех неактивных пользователей
DELETE FROM users WHERE last_login < '2023-01-01';
-- CASCADE удалил ВСЕ заказы, комментарии, лайки этих пользователей!

-- ✅ ПРАВИЛЬНО: сначала посчитать затронутые записи
SELECT
    COUNT(DISTINCT u.id) AS users,
    COUNT(DISTINCT o.id) AS orders,
    COUNT(DISTINCT c.id) AS comments
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN comments c ON c.author_id = u.id
WHERE u.last_login < '2023-01-01';

-- Если удаляется слишком много → использовать soft delete
UPDATE users SET deleted_at = NOW() WHERE last_login < '2023-01-01';

-- ❌ ОШИБКА: SET NULL на обязательном поле
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE SET NULL  -- противоречие!
);
-- При удалении пользователя → ERROR: null value in column "user_id" violates not-null constraint

-- ✅ ПРАВИЛЬНО: либо CASCADE, либо убрать NOT NULL
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE SET NULL  -- без NOT NULL
);

-- Удаление пользователя с 1 млн заказов:
DELETE FROM users WHERE id = 123;
-- CASCADE удаляет 1,000,000 заказов → долго, блокирует таблицу!

-- ✅ ПРАВИЛЬНО: удалять батчами
DO $$
DECLARE
    deleted_count INT;
BEGIN
    LOOP
        DELETE FROM orders
        WHERE id IN (
            SELECT id FROM orders WHERE user_id = 123 LIMIT 10000
        );
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        EXIT WHEN deleted_count = 0;
        PERFORM pg_sleep(0.1);  -- даём БД "подышать"
        RAISE NOTICE 'Deleted % orders', deleted_count;
    END LOOP;
END $$;

-- Теперь можно безопасно удалить пользователя:
DELETE FROM users WHERE id = 123;

Задайте себе вопрос: "Что должно произойти с дочерними записями?"

┌─────────────────────────────────────────────────────────────┐
│ Дочерние записи НЕ имеют смысла без родителя?               │
│ (заказы без пользователя, товары корзины без корзины)       │
└──────────────────┬──────────────────────────────────────────┘
                   ↓ ДА
         ON DELETE CASCADE
         ⚠️ Осторожно: массовое удаление!

┌─────────────────────────────────────────────────────────────┐
│ Дочерние записи могут существовать без родителя?            │
│ (комментарий без автора, история изменений без пользователя)│
└──────────────────┬──────────────────────────────────────────┘
                   ↓ ДА
         ON DELETE SET NULL
         (FK колонка БЕЗ NOT NULL)

┌─────────────────────────────────────────────────────────────┐
│ Нельзя удалять родителя, пока есть дочерние?                │
│ (категория товаров, роль пользователя, валюта)              │
└──────────────────┬──────────────────────────────────────────┘
                   ↓ ДА
         ON DELETE RESTRICT / NO ACTION (по умолчанию)
         Безопасно: БД не даст удалить

┌─────────────────────────────────────────────────────────────┐
│ Есть дефолтная "безопасная" категория?                      │
│ (категория "Без категории", статус "Неизвестно")            │
└──────────────────┬──────────────────────────────────────────┘
                   ↓ ДА (редко)
         ON DELETE SET DEFAULT
         Требует наличия default-записи

03:00 - Разработчик забыл проверить email в коде:
        INSERT INTO users (email, name) VALUES ('', '');  -- OK! ❌

03:15 - Два пользователя с одинаковым email:
        INSERT INTO users (email, name) VALUES ('test@test.com', 'User1');  -- OK! ❌
        INSERT INTO users (email, name) VALUES ('test@test.com', 'User2');  -- OK! ❌

03:30 - Отрицательная цена товара (баг в коде):
        UPDATE products SET price = -100 WHERE id = 123;  -- OK! ❌

08:00 - Техподдержка: "Пользователи не могут войти (дубли email)"
        "В каталоге товары с ценой -100 руб!"
        "Заказы без имени покупателя (name = '')"

Итог: 6 часов исправления плохих данных вручную, потеря денег

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                                -- PRIMARY KEY: уникальный ID
    email VARCHAR(255) UNIQUE NOT NULL,                   -- UNIQUE + NOT NULL: email обязателен и уникален
    name VARCHAR(100) NOT NULL,                           -- NOT NULL: имя обязательно
    age INT CHECK (age >= 18 AND age <= 120),            -- CHECK: возраст от 18 до 120
    balance DECIMAL(10,2) DEFAULT 0.00 CHECK (balance >= 0),  -- DEFAULT + CHECK: баланс ≥ 0
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'blocked', 'deleted')),  -- CHECK: только допустимые статусы
    created_at TIMESTAMP DEFAULT NOW() NOT NULL           -- DEFAULT: автоматическая дата создания
);

@Service
public class UserRegistrationService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void registerUser(String email, String name, Integer age) {
        try {
            jdbcTemplate.update("""
                INSERT INTO users (email, name, age)
                VALUES (?, ?, ?)
                """,
                email, name, age
            );
        } catch (DuplicateKeyException e) {
            // UNIQUE constraint нарушен:
            throw new BusinessException("Email " + email + " уже зарегистрирован");
        } catch (DataIntegrityViolationException e) {
            // NOT NULL или CHECK constraint нарушен:
            String message = e.getMessage();
            if (message.contains("age")) {
                throw new BusinessException("Возраст должен быть от 18 до 120 лет");
            } else if (message.contains("email")) {
                throw new BusinessException("Email обязателен");
            } else if (message.contains("name")) {
                throw new BusinessException("Имя обязательно");
            }
            throw new BusinessException("Некорректные данные пользователя");
        }
    }

    public void updateBalance(Long userId, BigDecimal amount) {
        try {
            jdbcTemplate.update(
                "UPDATE users SET balance = balance + ? WHERE id = ?",
                amount, userId
            );
        } catch (DataIntegrityViolationException e) {
            // CHECK (balance >= 0) нарушен:
            throw new BusinessException("Недостаточно средств на счёте");
        }
    }
}

-- ❌ Попытка вставить дубликат email (UNIQUE):
INSERT INTO users (email, name, age) VALUES ('test@test.com', 'User1', 25);  -- OK
INSERT INTO users (email, name, age) VALUES ('test@test.com', 'User2', 30);  -- ERROR
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(test@test.com) already exists.

-- ❌ Попытка вставить NULL в обязательное поле (NOT NULL):
INSERT INTO users (email, name, age) VALUES (NULL, 'User1', 25);  -- ERROR
-- ERROR: null value in column "email" of relation "users" violates not-null constraint

-- ❌ Попытка вставить некорректный возраст (CHECK):
INSERT INTO users (email, name, age) VALUES ('kid@test.com', 'Kid', 12);  -- ERROR
-- ERROR: new row for relation "users" violates check constraint "users_age_check"
-- DETAIL: Failing row contains (kid@test.com, Kid, 12, ...).

-- ❌ Попытка установить отрицательный баланс (CHECK):
UPDATE users SET balance = -100 WHERE id = 1;  -- ERROR
-- ERROR: new row for relation "users" violates check constraint "users_balance_check"

-- ✅ Использование DEFAULT:
INSERT INTO users (email, name, age) VALUES ('new@test.com', 'New User', 25);
-- Автоматически: balance = 0.00, status = 'active', created_at = NOW()

CREATE EXTENSION btree_gist;  -- требуется для EXCLUDE с обычными типами

CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    guest_name VARCHAR(100) NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    -- EXCLUDE: комната не может быть забронирована двумя людьми одновременно
    EXCLUDE USING gist (
        room_id WITH =,
        daterange(check_in, check_out, '[]') WITH &&
    )
);

✅ Бронирование 1:
INSERT INTO room_bookings (room_id, guest_name, check_in, check_out)
VALUES (101, 'Иван', '2024-03-10', '2024-03-15');
-- OK! Комната 101 свободна

❌ Бронирование 2 (пересечение):
INSERT INTO room_bookings (room_id, guest_name, check_in, check_out)
VALUES (101, 'Мария', '2024-03-12', '2024-03-17');  -- пересекается с 10-15!
-- ERROR: conflicting key value violates exclusion constraint

✅ Бронирование 3 (нет пересечения):
INSERT INTO room_bookings (room_id, guest_name, check_in, check_out)
VALUES (101, 'Пётр', '2024-03-15', '2024-03-20');  -- после 10-15, ОК!
-- OK! Даты не пересекаются (15 - день выезда Ивана, день заезда Петра)

@Service
public class BookingService {
    public void bookRoom(Integer roomId, String guestName, LocalDate checkIn, LocalDate checkOut) {
        try {
            jdbcTemplate.update("""
                INSERT INTO room_bookings (room_id, guest_name, check_in, check_out)
                VALUES (?, ?, ?, ?)
                """,
                roomId, guestName, checkIn, checkOut
            );
        } catch (DataIntegrityViolationException e) {
            // EXCLUDE constraint: даты пересекаются
            throw new BusinessException(
                "Комната " + roomId + " уже забронирована на эти даты"
            );
        }
    }
}

CREATE TABLE flights (
    id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    departure_date DATE NOT NULL,
    departure_time TIME NOT NULL,
    -- UNIQUE: уникальная комбинация (рейс может быть в разные дни, но не в один день дважды)
    UNIQUE (flight_number, departure_date),
    -- CHECK: время вылета раньше времени прилёта
    CHECK (departure_time < arrival_time)
);

-- Измерение времени вставки 100,000 записей:

-- БЕЗ constraints:
CREATE TABLE test_no_constraints (id INT, value INT);
INSERT INTO test_no_constraints SELECT generate_series(1, 100000), random() * 1000;
-- Time: 523 ms

-- С PRIMARY KEY + UNIQUE + CHECK:
CREATE TABLE test_with_constraints (
    id INT PRIMARY KEY,
    value INT UNIQUE CHECK (value >= 0)
);
INSERT INTO test_with_constraints SELECT generate_series(1, 100000), generate_series(1, 100000);
-- Time: 1247 ms (в 2.4x медленнее)

-- Причина: каждый constraint проверяется при вставке
-- Но это защищает от плохих данных!

-- 1. Временно отключить constraints:
ALTER TABLE users DISABLE TRIGGER ALL;

-- 2. Загрузить данные (быстро):
COPY users FROM '/tmp/users.csv' WITH CSV;

-- 3. Включить обратно (проверит все данные):
ALTER TABLE users ENABLE TRIGGER ALL;

-- ⚠️ Если есть нарушения → ERROR, откат

// ❌ ПЛОХО: проверка только в Java
public void createProduct(String name, BigDecimal price) {
    if (price.compareTo(BigDecimal.ZERO) <= 0) {
        throw new ValidationException("Цена должна быть положительной");
    }
    jdbcTemplate.update("INSERT INTO products (name, price) VALUES (?, ?)", name, price);
}
// Проблема: другой разработчик может обойти валидацию, записав напрямую в БД!

// ✅ ХОРОШО: constraint в БД + проверка в коде
CREATE TABLE products (
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0)  -- защита на уровне БД!
);

// ❌ ПЛОХО: общая обработка
try {
    jdbcTemplate.update("INSERT INTO users ...");
} catch (Exception e) {
    log.error("Ошибка", e);  // что именно не так?
}

// ✅ ХОРОШО: специфичная обработка
try {
    jdbcTemplate.update("INSERT INTO users (email, name) VALUES (?, ?)", email, name);
} catch (DuplicateKeyException e) {
    throw new BusinessException("Email уже зарегистрирован");
} catch (DataIntegrityViolationException e) {
    if (e.getMessage().contains("age_check")) {
        throw new BusinessException("Возраст должен быть от 18 до 120");
    }
    throw new BusinessException("Некорректные данные");
}

-- ❌ БЕЗ UNIQUE constraint:
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,  -- БЕЗ UNIQUE!
    username VARCHAR(50) NOT NULL  -- БЕЗ UNIQUE!
);

-- Можно зарегистрировать дубликаты:
INSERT INTO users (email, username) VALUES ('test@test.com', 'john');  -- OK
INSERT INTO users (email, username) VALUES ('test@test.com', 'john');  -- OK! ❌ Дубликат!

-- Проблемы:
SELECT * FROM users WHERE email = 'test@test.com';
-- Вернёт 2 записи! С каким пользователем работать?

-- Вход в систему сломан:
SELECT id FROM users WHERE email = 'test@test.com' AND password = '...';
-- Несколько ID! Который правильный?

-- ✅ С UNIQUE constraints:
CREATE TABLE users (
    id SERIAL PRIMARY KEY,                    -- PRIMARY KEY: идентификатор
    email VARCHAR(255) UNIQUE NOT NULL,       -- UNIQUE: один email на систему
    username VARCHAR(50) UNIQUE NOT NULL,     -- UNIQUE: один username на систему
    phone VARCHAR(20) UNIQUE                  -- UNIQUE: может быть NULL, но если есть — уникален
);

-- Попытка дубликата:
INSERT INTO users (email, username) VALUES ('test@test.com', 'john');  -- OK
INSERT INTO users (email, username) VALUES ('test@test.com', 'jane');  -- ERROR!
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(test@test.com) already exists.

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Long registerUser(String email, String username, String password) {
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO users (email, username, password_hash) VALUES (?, ?, ?)",
                    Statement.RETURN_GENERATED_KEYS
                );
                ps.setString(1, email);
                ps.setString(2, username);
                ps.setString(3, hashPassword(password));
                return ps;
            }, keyHolder);

            return keyHolder.getKey().longValue();  // вернуть сгенерированный PRIMARY KEY

        } catch (DuplicateKeyException e) {
            String message = e.getMessage();
            if (message.contains("users_email_key")) {
                throw new BusinessException("Email " + email + " уже зарегистрирован");
            } else if (message.contains("users_username_key")) {
                throw new BusinessException("Username " + username + " уже занят");
            }
            throw new BusinessException("Пользователь с такими данными уже существует");
        }
    }

    public User findByEmailOrUsername(String login) {
        // Используем UNIQUE колонки для поиска:
        return jdbcTemplate.queryForObject("""
            SELECT * FROM users
            WHERE email = ? OR username = ?
            """,
            (rs, rowNum) -> mapUser(rs),
            login, login
        );
        // Вернёт РОВНО одного пользователя, т.к. email и username — UNIQUE
    }
}

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),        -- ✅ Ссылка на PRIMARY KEY (стандарт)
    -- user_email VARCHAR REFERENCES users(email)    -- ⚠️ Можно, но НЕ рекомендуется
);

-- Почему ссылаться на PRIMARY KEY лучше:
-- 1. INT (4 байта) быстрее чем VARCHAR (до 255 байт)
-- 2. PRIMARY KEY не меняется, email может измениться
-- 3. PRIMARY KEY всегда проиндексирован оптимально

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,   -- NULL запрещён
    phone VARCHAR(20) UNIQUE              -- NULL разрешён
);

-- ✅ Несколько NULL в phone (ОК):
INSERT INTO users (email, phone) VALUES ('user1@test.com', NULL);  -- OK
INSERT INTO users (email, phone) VALUES ('user2@test.com', NULL);  -- OK! Несколько NULL разрешено
INSERT INTO users (email, phone) VALUES ('user3@test.com', '+1234567890');  -- OK

-- ❌ Дубликат НЕ-NULL значения:
INSERT INTO users (email, phone) VALUES ('user4@test.com', '+1234567890');  -- ERROR!
-- ERROR: duplicate key value violates unique constraint "users_phone_key"

Таблица users:
┌────┬─────────────────┬───────────────┐
│ id │ email           │ phone         │
├────┼─────────────────┼───────────────┤
│ 1  │ user1@test.com  │ NULL          │ ← NULL #1 (OK)
│ 2  │ user2@test.com  │ NULL          │ ← NULL #2 (OK, UNIQUE позволяет!)
│ 3  │ user3@test.com  │ +1234567890   │ ← уникальный телефон
│ 4  │ user4@test.com  │ +1234567890   │ ← ❌ дубликат → ERROR
└────┴─────────────────┴───────────────┘

PRIMARY KEY (id):   всегда NOT NULL, один на таблицу
UNIQUE (email):     NOT NULL, несколько на таблицу
UNIQUE (phone):     может быть NULL (много NULL), несколько на таблицу

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- создаёт индекс "users_pkey"
    email VARCHAR UNIQUE          -- создаёт индекс "users_email_key"
);

-- Проверка индексов:
\d users

-- Indexes:
--     "users_pkey" PRIMARY KEY, btree (id)          ← кластерный (данные хранятся в порядке ID)
--     "users_email_key" UNIQUE CONSTRAINT, btree (email)  ← обычный индекс

-- Поиск по PRIMARY KEY:
EXPLAIN SELECT * FROM users WHERE id = 123;
-- Index Scan using users_pkey on users  (cost=0.29..8.30 rows=1 width=40)
-- (очень быстро, O(log N))

-- Поиск по UNIQUE:
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
-- Index Scan using users_email_key on users  (cost=0.29..8.30 rows=1 width=40)
-- (тоже очень быстро, O(log N))

-- Таблица с 1,000,000 пользователей

-- Поиск по PRIMARY KEY:
SELECT * FROM users WHERE id = 500000;
-- Execution Time: 0.034 ms

-- Поиск по UNIQUE email:
SELECT * FROM users WHERE email = 'user500000@test.com';
-- Execution Time: 0.041 ms

-- Разница минимальна! Оба индекса работают одинаково быстро.

-- ❌ НЕПРАВИЛЬНО: невозможно, т.к. PRIMARY KEY только один
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) PRIMARY KEY,    -- ERROR! PRIMARY KEY уже есть
    username VARCHAR(50) PRIMARY KEY   -- ERROR!
);

-- ✅ ПРАВИЛЬНО: используй UNIQUE
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL
);

-- ⚠️ ПРОБЛЕМА: UNIQUE без NOT NULL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE  -- NULL разрешён!
);

INSERT INTO users (email) VALUES (NULL);  -- OK
INSERT INTO users (email) VALUES (NULL);  -- OK! Несколько пользователей БЕЗ email

-- ✅ ПРАВИЛЬНО: добавить NOT NULL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL  -- обязателен И уникален
);

-- ⚠️ ПЛОХАЯ ПРАКТИКА:
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_email VARCHAR REFERENCES users(email)  -- ссылка на UNIQUE
);
-- Проблемы:
-- • email может измениться → нужен ON UPDATE CASCADE
-- • VARCHAR медленнее INT для JOIN
-- • Занимает больше места (255 байт vs 4 байта)

-- ✅ ПРАВИЛЬНО:
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id)  -- ссылка на PRIMARY KEY
);

┌──────────────────────────────────────────────────────────┐
│ Нужен уникальный идентификатор строки?                   │
└──────────────────┬───────────────────────────────────────┘
                   ↓ ДА
          PRIMARY KEY (только один на таблицу)
          Примеры: id, order_id, user_id

┌──────────────────────────────────────────────────────────┐
│ Нужна уникальность бизнес-данных?                        │
└──────────────────┬───────────────────────────────────────┘
                   ↓ ДА
          UNIQUE (сколько угодно на таблицу)
          Примеры: email, username, phone, passport_number

┌──────────────────────────────────────────────────────────┐
│ Поле может быть пустым?                                  │
└──────────────────┬───────────────────────────────────────┘
                   ↓ ДА
          UNIQUE (без NOT NULL)
          Пример: phone (необязателен, но если есть — уникален)
                   ↓ НЕТ
          UNIQUE NOT NULL
          Пример: email (обязателен и уникален)

02:30 - Баг в коде: цена товара установлена в -100 руб. вместо 100 руб.
        UPDATE products SET price = -100 WHERE id = 123;  -- OK! ❌

03:00 - Покупатель оформляет заказ:
        Товар A: -100 руб.
        Товар B: 50 руб.
        ИТОГО: -50 руб. → система ДОПЛАЧИВАЕТ клиенту 50 руб! 💸

08:00 - Бухгалтерия: "За ночь 47 заказов с отрицательной суммой!"
        Потеря: 127,450 руб.

09:00 - Поиск причины: нашли 15 товаров с отрицательной ценой
        Откуда? Баг в коде скидок: discount = 100% + 10% (110%) → price = -10 руб.

Итог: Финансовые потери, репутационный ущерб, неделя на исправление данных

-- ✅ С CHECK constraint:
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),  -- цена > 0
    quantity INT NOT NULL CHECK (quantity >= 0)      -- количество >= 0
);

-- Попытка установить отрицательную цену:
UPDATE products SET price = -100 WHERE id = 123;
-- ERROR: new row for relation "products" violates check constraint "products_price_check"
-- DETAIL: Failing row contains (123, "iPhone 15", -100, 10).

-- БД ЗАЩИТИЛА от плохих данных! Баг в коде не повлиял на данные.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    discount_percent INT DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
    quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    weight_kg DECIMAL(8,3) CHECK (weight_kg > 0),
    -- CHECK на несколько колонок:
    CONSTRAINT valid_discount CHECK (
        discount_percent = 0 OR
        (price * (100 - discount_percent) / 100) >= 1.00  -- цена после скидки >= 1 руб.
    )
);

@Service
public class ProductService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void updatePrice(Long productId, BigDecimal newPrice) {
        try {
            jdbcTemplate.update(
                "UPDATE products SET price = ? WHERE id = ?",
                newPrice, productId
            );
        } catch (DataIntegrityViolationException e) {
            // CHECK constraint нарушен:
            throw new BusinessException("Цена должна быть положительной");
        }
    }

    public void applyDiscount(Long productId, Integer discountPercent) {
        try {
            jdbcTemplate.update(
                "UPDATE products SET discount_percent = ? WHERE id = ?",
                discountPercent, productId
            );
        } catch (DataIntegrityViolationException e) {
            String message = e.getMessage();
            if (message.contains("discount_percent_check")) {
                throw new BusinessException("Скидка должна быть от 0% до 100%");
            } else if (message.contains("valid_discount")) {
                throw new BusinessException(
                    "Цена после скидки слишком мала (минимум 1 руб.)"
                );
            }
            throw new BusinessException("Некорректная скидка");
        }
    }

    public void sellProduct(Long productId, Integer quantity) {
        try {
            int updated = jdbcTemplate.update(
                "UPDATE products SET quantity = quantity - ? WHERE id = ?",
                quantity, productId
            );

            if (updated == 0) {
                throw new NotFoundException("Товар не найден");
            }
        } catch (DataIntegrityViolationException e) {
            // CHECK (quantity >= 0) нарушен:
            throw new BusinessException("Недостаточно товара на складе");
        }
    }
}

-- ❌ Попытка установить отрицательную цену:
UPDATE products SET price = -100 WHERE id = 1;
-- ERROR: new row violates check constraint "products_price_check"

-- ❌ Скидка 150%:
UPDATE products SET discount_percent = 150 WHERE id = 1;
-- ERROR: new row violates check constraint "products_discount_percent_check"

-- ❌ Продать больше, чем есть на складе:
UPDATE products SET quantity = quantity - 100 WHERE id = 1;  -- было quantity=10
-- ERROR: new row violates check constraint "products_quantity_check"
-- (10 - 100 = -90, нарушает quantity >= 0)

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,
    -- CHECK: дата окончания после даты начала
    CHECK (end_date > start_date),
    -- CHECK: событие не может длиться больше 30 дней
    CHECK (end_date - start_date <= INTERVAL '30 days')
);

public void createEvent(String name, LocalDateTime start, LocalDateTime end) {
    try {
        jdbcTemplate.update("""
            INSERT INTO events (name, start_date, end_date)
            VALUES (?, ?, ?)
            """,
            name, start, end
        );
    } catch (DataIntegrityViolationException e) {
        if (e.getMessage().contains("end_date")) {
            throw new BusinessException(
                "Дата окончания должна быть позже даты начала и не больше 30 дней"
            );
        }
        throw new BusinessException("Некорректные даты события");
    }
}

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL CHECK (
        status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')
    ),
    created_at TIMESTAMP DEFAULT NOW(),
    shipped_at TIMESTAMP,
    -- CHECK: shipped_at должен быть после created_at
    CHECK (shipped_at IS NULL OR shipped_at >= created_at)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    hire_date DATE NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    -- CHECK: сотруднику на дату найма должно быть минимум 18 лет
    CHECK (hire_date >= birth_date + INTERVAL '18 years'),
    -- CHECK: зарплата от 20,000 до 500,000 руб.
    CHECK (salary BETWEEN 20000 AND 500000)
);

✅ Попытка 1:
INSERT INTO employees (name, birth_date, hire_date, salary)
VALUES ('Иван', '2000-01-01', '2020-06-15', 50000);
-- birth_date: 2000-01-01
-- hire_date:  2020-06-15 (через 20 лет 5 месяцев) → OK!
-- salary: 50000 (в диапазоне 20000-500000) → OK!

❌ Попытка 2:
INSERT INTO employees (name, birth_date, hire_date, salary)
VALUES ('Петя', '2010-01-01', '2024-01-01', 30000);
-- birth_date: 2010-01-01
-- hire_date:  2024-01-01 (через 14 лет) → ❌ меньше 18 лет!
-- ERROR: new row violates check constraint

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL CHECK (
        email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
    ),
    phone VARCHAR(20) CHECK (
        phone IS NULL OR phone ~ '^\+?[0-9]{10,15}$'
    )
);

-- ✅ Валидные данные:
INSERT INTO users (email, phone) VALUES ('test@example.com', '+1234567890');  -- OK

-- ❌ Невалидный email:
INSERT INTO users (email, phone) VALUES ('invalid-email', '+1234567890');  -- ERROR!
-- ERROR: new row violates check constraint "users_email_check"

-- ❌ Невалидный телефон:
INSERT INTO users (email, phone) VALUES ('test@example.com', 'abc');  -- ERROR!
-- ERROR: new row violates check constraint "users_phone_check"

CREATE TABLE shipments (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered')),
    tracking_number VARCHAR(50),
    delivered_at TIMESTAMP,
    -- Если статус 'shipped' → tracking_number обязателен
    CHECK (status != 'shipped' OR tracking_number IS NOT NULL),
    -- Если статус 'delivered' → delivered_at обязателен
    CHECK (status != 'delivered' OR delivered_at IS NOT NULL)
);

-- ❌ Статус 'shipped' без tracking_number:
INSERT INTO shipments (status, tracking_number) VALUES ('shipped', NULL);
-- ERROR: new row violates check constraint

-- ✅ Статус 'pending' без tracking_number (ОК):
INSERT INTO shipments (status, tracking_number) VALUES ('pending', NULL);  -- OK!

-- Измерение overhead:

-- БЕЗ CHECK:
CREATE TABLE test_no_check (
    id SERIAL PRIMARY KEY,
    value INT
);
INSERT INTO test_no_check SELECT generate_series(1, 100000), random() * 1000;
-- Time: 523 ms

-- С простым CHECK:
CREATE TABLE test_simple_check (
    id SERIAL PRIMARY KEY,
    value INT CHECK (value >= 0)
);
INSERT INTO test_simple_check SELECT generate_series(1, 100000), random() * 1000;
-- Time: 612 ms (+17% overhead)

-- С сложным CHECK (regex):
CREATE TABLE test_complex_check (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
INSERT INTO test_complex_check SELECT generate_series(1, 100000), 'user' || generate_series || '@test.com';
-- Time: 1847 ms (+253% overhead из-за regex)

-- ❌ ОШИБКА: CHECK НЕ может использовать подзапросы
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10,2),
    CHECK (
        total <= (SELECT balance FROM users WHERE id = user_id)  -- НЕ РАБОТАЕТ!
    )
);
-- ERROR: cannot use subquery in check constraint

-- ✅ РЕШЕНИЕ: использовать TRIGGER
CREATE OR REPLACE FUNCTION check_user_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total > (SELECT balance FROM users WHERE id = NEW.user_id) THEN
        RAISE EXCEPTION 'Недостаточно средств на счёте';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_balance
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION check_user_balance();

-- ⚠️ ПЛОХО: автоматическое имя "products_price_check"
CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0)
);

-- ✅ ХОРОШО: явное имя
CREATE TABLE products (
    price DECIMAL(10,2),
    CONSTRAINT positive_price CHECK (price > 0)
);

-- Преимущества:
-- • Понятные сообщения об ошибках
-- • Легко найти в документации
-- • Можно удалить: ALTER TABLE products DROP CONSTRAINT positive_price;

-- ⚠️ ПЛОХО: сложная логика в CHECK
CREATE TABLE orders (
    status VARCHAR(20),
    CHECK (
        (status = 'pending' AND payment_status IS NULL) OR
        (status = 'paid' AND payment_status = 'completed') OR
        (status = 'shipped' AND tracking_number IS NOT NULL) OR
        (status = 'delivered' AND delivered_at IS NOT NULL)
    )
);
-- Тяжело читать, поддерживать и дебажить

-- ✅ ХОРОШО: простые CHECK + триггер для сложной логики
CREATE TABLE orders (
    status VARCHAR(20) CHECK (status IN ('pending', 'paid', 'shipped', 'delivered'))
);

CREATE TRIGGER trg_validate_order_state
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION validate_order_state();

-- ❌ НЕ 1NF: телефоны в одном поле через запятую
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phones VARCHAR(255)  -- "123-456, 789-012, 345-678"
);

-- Невозможно:
-- - Найти всех с телефоном 123-456
-- - Проверить формат каждого телефона
-- - Посчитать количество телефонов у пользователя

-- ✅ 1NF: телефоны в отдельной таблице
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_phones (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    phone VARCHAR(20)
);

-- Теперь можно:
SELECT * FROM user_phones WHERE phone = '123-456';
SELECT user_id, COUNT(*) FROM user_phones GROUP BY user_id;

-- ❌ НЕ 1NF: фиксированное количество телефонов
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone1 VARCHAR(20),
    phone2 VARCHAR(20),
    phone3 VARCHAR(20)
);
-- Что делать, если нужен 4-й телефон? ALTER TABLE?

-- ✅ 1NF: используем отдельную таблицу user_phones

-- ❌ НЕ 2NF: product_name зависит только от product_id, а не от всего ключа
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- зависит только от product_id!
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Проблемы:
-- 1. Дублирование: product_name повторяется в каждом заказе
-- 2. Аномалия обновления: переименовали товар → нужно обновлять везде
-- 3. Аномалия вставки: нельзя добавить товар без заказа

-- ✅ 2NF: product_name в отдельной таблице
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT REFERENCES products(id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- Теперь product_name хранится один раз
-- Переименование товара = одно UPDATE в products

-- ❌ НЕ 3NF: city_name зависит от zip_code, а не напрямую от id
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city_name VARCHAR(100)  -- зависит от zip_code, а не от id!
);

-- Проблемы:
-- 1. Дублирование: одинаковый город для одного zip_code повторяется
-- 2. Аномалия обновления: переименовали город → нужно обновить все записи
-- 3. Противоречия: один zip_code может иметь разные city_name в разных записях

-- ✅ 3NF: города в отдельной таблице
CREATE TABLE cities (
    zip_code VARCHAR(10) PRIMARY KEY,
    city_name VARCHAR(100)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10) REFERENCES cities(zip_code)
);

-- Теперь город хранится один раз для каждого zip_code

-- ❌ НЕ 3NF
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100),      -- зависит от department_id
    department_budget NUMERIC           -- зависит от department_id
);

-- ✅ 3NF
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    budget NUMERIC
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id)
);

-- Таблица: преподаватели ведут курсы в аудиториях
CREATE TABLE schedule (
    student_id INT,
    course VARCHAR(100),
    instructor VARCHAR(100),
    PRIMARY KEY (student_id, course)
);

-- Допустим:
-- - Один курс ведёт только один преподаватель (course → instructor)
-- - Один преподаватель может вести несколько курсов

-- Проблема: instructor зависит от course, но course не является суперключом
-- Аномалия: нельзя добавить преподавателя без студента

CREATE TABLE courses (
    course VARCHAR(100) PRIMARY KEY,
    instructor VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course VARCHAR(100) REFERENCES courses(course),
    PRIMARY KEY (student_id, course)
);

-- Вместо JOIN каждый раз
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    user_name VARCHAR(100),  -- денормализация!
    user_email VARCHAR(100)  -- денормализация!
);

-- Теперь не нужен JOIN с users для отображения заказов
SELECT id, user_name, user_email FROM orders;

-- ❌ Медленно на больших таблицах:
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- ✅ Денормализация: храним счётчик
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    orders_count INT DEFAULT 0  -- денормализация!
);

-- Обновляем через триггер:
CREATE OR REPLACE FUNCTION update_orders_count() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET orders_count = orders_count + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET orders_count = orders_count - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_count_trigger
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_orders_count();

-- ❌ Медленно на больших таблицах:
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- ✅ Денормализация: храним счётчик
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    orders_count INT DEFAULT 0  -- денормализация!
);

-- Обновляем через триггер:
CREATE OR REPLACE FUNCTION update_orders_count() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET orders_count = orders_count + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET orders_count = orders_count - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_count_trigger
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_orders_count();

-- Вместо сложного JOIN каждый раз
CREATE MATERIALIZED VIEW user_stats AS
SELECT
    u.id,
    u.name,
    COUNT(o.id) as orders_count,
    SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Обновление периодически:
REFRESH MATERIALIZED VIEW user_stats;