Как сделать ограничение на нескольких столбцах

Ограничения на нескольких столбцах в БД

В SQL базах данных часто возникает необходимость создавать ограничения (constraints) на основе нескольких столбцов одновременно. Это может быть UNIQUE, PRIMARY KEY, FOREIGN KEY или CHECK ограничение.

1. Составной PRIMARY KEY (Composite Primary Key)

PRIMARY KEY на основе нескольких столбцов создается для таблиц, где уникальность требует комбинации нескольких полей:

// SQL: Создание таблицы с составным первичным ключом
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    semester INT,
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id, semester)
);

// Один студент может быть на одном курсе только один раз за семестр
INSERT INTO student_courses VALUES (1, 101, 1, 'A');
INSERT INTO student_courses VALUES (1, 101, 2, 'B'); // OK: другой семестр
INSERT INTO student_courses VALUES (1, 101, 1, 'A'); // ERROR: уже существует

Использование в JPA/Hibernate:

@Entity
@Table(name = "student_courses")
public class StudentCourse {
    
    @EmbeddedId
    private StudentCoursePK id;
    
    @Column(name = "grade")
    private String grade;
}

@Embeddable
public class StudentCoursePK implements Serializable {
    
    @Column(name = "student_id")
    private Integer studentId;
    
    @Column(name = "course_id")
    private Integer courseId;
    
    @Column(name = "semester")
    private Integer semester;
    
    @Override
    public boolean equals(Object o) { /* ... */ }
    
    @Override
    public int hashCode() { /* ... */ }
}

2. Составной UNIQUE KEY (Unique Index)

UNIQUE ограничение на несколько столбцов позволяет иметь NULL значения и не обязательно быть первичным ключом:

// SQL: Уникальность по комбинации email и organization
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255),
    organization_id INT,
    username VARCHAR(100),
    UNIQUE KEY unique_email_org (email, organization_id)
);

// В одной организации email должен быть уникален
INSERT INTO users VALUES (1, 'john@example.com', 1, 'john');
INSERT INTO users VALUES (2, 'john@example.com', 2, 'john'); // OK: другая организация
INSERT INTO users VALUES (3, 'john@example.com', 1, 'john2'); // ERROR: дубликат в org 1

В JPA/Hibernate используем аннотацию:

@Entity
@Table(
    name = "users",
    uniqueConstraints = {
        @UniqueConstraint(
            name = "uk_email_org",
            columnNames = {"email", "organization_id"}
        )
    }
)
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String email;
    
    @Column(name = "organization_id", nullable = false)
    private Long organizationId;
    
    @Column(nullable = false)
    private String username;
}

3. Составной FOREIGN KEY (Composite Foreign Key)

FOREIGN KEY на несколько столбцов используется для ссылок на составные ключи:

// Родительская таблица с составным ключом
CREATE TABLE departments (
    company_id INT,
    dept_id INT,
    dept_name VARCHAR(100),
    PRIMARY KEY (company_id, dept_id)
);

// Дочерняя таблица с составным внешним ключом
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    company_id INT,
    dept_id INT,
    FOREIGN KEY (company_id, dept_id) 
        REFERENCES departments(company_id, dept_id)
        ON DELETE CASCADE
);

// Сотрудник может существовать только в существующем отделе компании
INSERT INTO departments VALUES (1, 10, 'IT');
INSERT INTO employees VALUES (1, 'Alice', 1, 10);  // OK
INSERT INTO employees VALUES (2, 'Bob', 1, 20);    // ERROR: отдела нет

В JPA/Hibernate:

@Entity
@Table(name = "employees")
public class Employee {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long empId;
    
    @Column(nullable = false)
    private String name;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(name = "company_id", referencedColumnName = "company_id"),
        @JoinColumn(name = "dept_id", referencedColumnName = "dept_id")
    })
    private Department department;
}

@Entity
@Table(name = "departments")
@IdClass(DepartmentPK.class)
public class Department {
    
    @Id
    @Column(name = "company_id")
    private Integer companyId;
    
    @Id
    @Column(name = "dept_id")
    private Integer deptId;
    
    @Column(name = "dept_name")
    private String deptName;
}

public class DepartmentPK implements Serializable {
    private Integer companyId;
    private Integer deptId;
    // equals и hashCode
}

4. CHECK ограничение на несколько столбцов

CHECK ограничение проверяет условие, включающее несколько столбцов:

// SQL: Цена доставки не должна быть больше стоимости товара
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_price DECIMAL(10, 2),
    shipping_price DECIMAL(10, 2),
    total_price DECIMAL(10, 2),
    CHECK (total_price = product_price + shipping_price),
    CHECK (shipping_price <= product_price)
);

// PostgreSQL
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    CHECK (
        LENGTH(first_name) > 0 AND 
        LENGTH(last_name) > 0 AND 
        email LIKE '%@%.%'
    )
);

Проверка на уровне приложения в Java:

@Entity
public class Account {
    
    @Id
    private Long id;
    
    @Column(nullable = false)
    @NotBlank(message = "First name required")
    private String firstName;
    
    @Column(nullable = false)
    @NotBlank(message = "Last name required")
    private String lastName;
    
    @Column(nullable = false)
    @Email(message = "Invalid email")
    private String email;
    
    // Bean Validation аннотация для проверки нескольких полей
    @AssertTrue(message = "Email and name must be valid")
    public boolean isValid() {
        return firstName != null && !firstName.isEmpty() &&
               lastName != null && !lastName.isEmpty() &&
               email != null && email.contains("@");
    }
}

5. Практический пример:租賃 система

// SQL миграция
CREATE TABLE rental_properties (
    id INT PRIMARY KEY AUTO_INCREMENT,
    owner_id INT NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(50) NOT NULL,
    UNIQUE KEY uk_owner_address (owner_id, address, city),
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE rental_bookings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    property_id INT NOT NULL,
    tenant_id INT NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    FOREIGN KEY (property_id) REFERENCES rental_properties(id),
    FOREIGN KEY (tenant_id) REFERENCES users(id),
    UNIQUE KEY uk_property_dates (property_id, check_in_date, check_out_date),
    CHECK (check_out_date > check_in_date)
);

JPA Entities:

@Entity
@Table(
    name = "rental_properties",
    uniqueConstraints = {
        @UniqueConstraint(
            columnNames = {"owner_id", "address", "city"}
        )
    }
)
public class RentalProperty {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "owner_id", nullable = false)
    private User owner;
    
    @Column(nullable = false)
    private String address;
    
    @Column(nullable = false)
    private String city;
}

@Entity
@Table(
    name = "rental_bookings",
    uniqueConstraints = {
        @UniqueConstraint(
            columnNames = {"property_id", "check_in_date", "check_out_date"}
        )
    }
)
public class RentalBooking {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "property_id", nullable = false)
    private RentalProperty property;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "tenant_id", nullable = false)
    private User tenant;
    
    @Column(name = "check_in_date", nullable = false)
    private LocalDate checkInDate;
    
    @Column(name = "check_out_date", nullable = false)
    private LocalDate checkOutDate;
    
    @AssertTrue(message = "Check-out must be after check-in")
    public boolean isValidDateRange() {
        return checkOutDate.isAfter(checkInDate);
    }
}

Резюме

Лучшая практика: Комбинируйте SQL ограничения (на уровне БД) с Bean Validation (на уровне приложения) для полной защиты данных.