Для чего нужен модификатор UNIQUE в SQL?

Для чего нужен модификатор UNIQUE в SQL

UNIQUE — это ограничение в SQL, которое гарантирует, что все значения в столбце (или комбинации столбцов) уникальны. Это предотвращает дублирование данных и обеспечивает целостность данных в базе.

Основная цель UNIQUE

UNIQUE гарантирует, что каждое значение в столбце появляется только один раз (или что комбинация значений нескольких столбцов уникальна).

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,  -- Каждый email должен быть уникальным
    username VARCHAR(50) UNIQUE,
    phone VARCHAR(15) UNIQUE
);

-- ПРАВИЛЬНО: разные email
INSERT INTO users (id, email, username) VALUES (1, 'john@example.com', 'john');
INSERT INTO users (id, email, username) VALUES (2, 'jane@example.com', 'jane');

-- ОШИБКА: повторяющийся email
INSERT INTO users (id, email, username) VALUES (3, 'john@example.com', 'john2');
-- Duplicate entry 'john@example.com' for key 'email'

UNIQUE vs PRIMARY KEY

-- PRIMARY KEY
CREATE TABLE users (
    id INT PRIMARY KEY  -- Только один, не NULL, автоматический индекс
);

-- UNIQUE
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE  -- Может быть NULL, несколько UNIQUE
);

Примеры использования UNIQUE

1. Единственный email

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,  -- UNIQUE и NOT NULL
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Правильно: разные email
INSERT INTO accounts (email, name) VALUES ('alice@example.com', 'Alice');
INSERT INTO accounts (email, name) VALUES ('bob@example.com', 'Bob');

-- Ошибка: повторяющийся email
INSERT INTO accounts (email, name) VALUES ('alice@example.com', 'Alice2');
-- Error: Duplicate entry

2. Уникальный номер документа

CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_number VARCHAR(50) UNIQUE,  -- Номер документа уникален
    title VARCHAR(200),
    user_id INT,
    created_date DATE
);

-- Правильно
INSERT INTO documents (document_number, title, user_id) 
VALUES ('DOC-001', 'Invoice', 1);
INSERT INTO documents (document_number, title, user_id) 
VALUES ('DOC-002', 'Receipt', 1);

-- Ошибка: DOC-001 уже существует
INSERT INTO documents (document_number, title, user_id) 
VALUES ('DOC-001', 'Another Invoice', 2);

3. Комбинированный UNIQUE (несколько столбцов)

CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrolled_date DATE,
    UNIQUE KEY unique_enrollment (student_id, course_id)  -- Уникальная комбинация
);

-- Правильно: один студент разные курсы
INSERT INTO enrollments (student_id, course_id, enrolled_date) 
VALUES (1, 101, '2024-01-15');
INSERT INTO enrollments (student_id, course_id, enrolled_date) 
VALUES (1, 102, '2024-01-15');
INSERT INTO enrollments (student_id, course_id, enrolled_date) 
VALUES (2, 101, '2024-01-15');

-- Ошибка: студент 1 уже записан на курс 101
INSERT INTO enrollments (student_id, course_id, enrolled_date) 
VALUES (1, 101, '2024-02-15');
-- Duplicate entry '1-101'

4. UNIQUE с NULL значениями

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,  -- Может быть NULL
    phone VARCHAR(15) UNIQUE,   -- Может быть NULL
    name VARCHAR(100) NOT NULL
);

-- Правильно: несколько NULL значений допускаются
INSERT INTO users (email, phone, name) VALUES (NULL, '555-1234', 'John');
INSERT INTO users (email, phone, name) VALUES (NULL, '555-5678', 'Jane');
INSERT INTO users (email, phone, name) VALUES ('alice@example.com', NULL, 'Alice');
INSERT INTO users (email, phone, name) VALUES ('bob@example.com', NULL, 'Bob');

-- Все вставки успешны (в каждом столбце только один NULL, остальные уникальны)

Важно: UNIQUE в SQL допускает несколько NULL значений (в отличие от PRIMARY KEY)

Java + UNIQUE через ORM

Hibernаte/JPA с аннотацией

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(unique = true, nullable = false)
    private String email;  // UNIQUE NOT NULL
    
    @Column(unique = true)
    private String username;  // UNIQUE (может быть NULL)
    
    @Column(name = "phone_number")
    private String phone;  // Без UNIQUE
    
    // Getters и setters
}

// При сохранении:
User user = new User();
user.setEmail("john@example.com");
user.setUsername("john_doe");
userRepository.save(user);  // Проверится уникальность

Обработка ошибки дублирования

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    public User createUser(String email, String username) 
            throws DuplicateEmailException {
        try {
            User user = new User();
            user.setEmail(email);
            user.setUsername(username);
            return userRepository.save(user);
        } catch (DataIntegrityViolationException e) {
            // Перехватываем ошибку нарушения UNIQUE
            if (e.getCause() instanceof ConstraintViolationException) {
                ConstraintViolationException cve = 
                    (ConstraintViolationException) e.getCause();
                
                if (cve.getConstraintName().contains("email")) {
                    throw new DuplicateEmailException(
                        "Email " + email + " already exists"
                    );
                }
            }
            throw e;
        }
    }
}

Создание UNIQUE индекса (альтернатива)

-- Способ 1: UNIQUE при создании таблицы
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- Способ 2: Создание UNIQUE индекса отдельно
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Способ 3: Добавление UNIQUE к существующей таблице
ALTER TABLE users ADD UNIQUE (email);

-- Способ 4: Комбинированный индекс
CREATE UNIQUE INDEX idx_unique_name_email 
ON users(first_name, last_name, email);

-- Удаление UNIQUE индекса
DROP INDEX idx_unique_email ON users;

Best Practices

-- 1. UNIQUE для альтернативных ключей (не PRIMARY KEY)
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,  -- PRIMARY KEY
    social_security_number VARCHAR(11) UNIQUE,  -- UNIQUE (альтернативный ключ)
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100)
);

-- 2. Комбинировать UNIQUE с NOT NULL для критичных полей
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,  -- Код товара, уникален и обязателен
    barcode VARCHAR(50) UNIQUE,       -- Штрихкод, уникален, может быть пусто
    name VARCHAR(100) NOT NULL
);

-- 3. Документировать причину UNIQUE в комментариях
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,  -- Каждый пользователь должен иметь уникальный email
    username VARCHAR(50) UNIQUE,  -- Username для логина
    api_key VARCHAR(255) UNIQUE  -- API ключ для аутентификации
);

-- 4. Использовать композитные UNIQUE для сложных сценариев
CREATE TABLE availability (
    id INT PRIMARY KEY,
    doctor_id INT,
    clinic_id INT,
    date_time TIMESTAMP,
    UNIQUE KEY unique_availability (doctor_id, clinic_id, date_time)
    -- Один врач не может работать в двух клиниках в одно время
);

Производительность

-- UNIQUE автоматически создаёт индекс
-- Это улучшает производительность при поиске

-- Быстро (использует индекс из UNIQUE)
SELECT * FROM users WHERE email = 'john@example.com';

-- Но имеет стоимость при INSERT/UPDATE
-- БД должна проверить уникальность перед каждой вставкой
INSERT INTO users (email, name) VALUES ('john@example.com', 'John');  -- Медленнее

-- И при обновлении
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;  -- Проверит уникальность

Итоговый ответ

UNIQUE в SQL нужен для:

1. Предотвращения дубликатов — каждое значение уникально
2. Целостности данных — гарантирует корректность
3. Альтернативных ключей — вместе с PRIMARY KEY
4. Валидации на уровне БД — не только на уровне приложения
5. Оптимизации поиска — автоматический индекс

Уникальные данные (email, username, номер документа, API ключ) должны быть помечены UNIQUE для предотвращения ошибок и обеспечения корректности базы данных.