외부 키에 기반한 SQL 제약 조건 검사

외부 키에 기반한 SQL 제약 조건 검사

데이터베이스에는 여러 개의 테이블이 있으며, 그 중 일부에 대해 약간 복잡한 제약 조건을 적용하려고 합니다.

이 문제에는 주로 4개의 테이블이 포함됩니다.

• 기본 키가 있는 "메인" 테이블id참조할 수 있습니다.
• 2개의 중간 테이블('a'와 'b'라고 부름)이 있으며, 각 테이블에는 각각 다른 테이블이 있습니다.id및 각각을 참조하는 컬럼이 있습니다(이 컬럼은foreign key)에 대해서main.id.
• 자체 테이블이 있는 "최종류id, 및 를 참조하는2개의 컬럼이 있습니다.a그리고.b각각 다음과 같다.

"최종" 표에서 "a" 표와 "b" 표에서 참조되는 요소는 "main"에서 동일한 항목을 참조해야 합니다.

테이블의 간단한 정의:

CREATE TABLE `main_table` (
    `id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
    -- other fields, indexes, etc
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `table_a` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `main_ref` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `main` (`main_ref`),
    FOREIGN KEY (`main_ref`) REFERENCES `main_table` (`id`)
    -- other fields, indexes, etc
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `table_b` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `main_ref` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `main` (`main_ref`),
    FOREIGN KEY (`main_ref`) REFERENCES `main_table` (`id`)
    -- other fields, indexes, etc
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `final` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `ref_a` INT NOT NULL,
    `ref_b` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `a` (`ref_a`),
    FOREIGN KEY (`ref_a`) REFERENCES `table_a` (`id`),
    INDEX `b` (`ref_b`),
    FOREIGN KEY (`ref_b`) REFERENCES `table_b` (`id`)
    -- other fields, indexes, etc
    -- The `main_ref` value in the entries referenced by `ref_a` and `ref_b` should match!!
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UI와 백엔드 코드 모두 무효 엔트리를 발생시키는 것은 불가능하지만, 이 프로젝트에서는 결국 같은 데이터베이스에 접속하는 추가 앱이 필요하기 때문에 가능한 한 DB엔진이 데이터 무결성 검사를 처리해 주었으면 합니다.

또, 현시점에서는, 새로운 컬럼이나 인덱스를 추가하는 것으로도 충분히 문제가 없습니다.

정보는 여기 있고, 제가 놓치고 있는 것은 엔진에 점검해야 할 사항을 알려주는 방법뿐입니다.생성된 가상 열과CHECK제약이 도움이 될 수 있지만, 몇 가지 소스(MariaDB 및 MySQL 문서 및 일부 웹 검색 포함)를 살펴본 결과 방법을 찾을 수 없습니다.

Fedora 25(x86_64 아치)에서 MariaDB 10.2.5를 실행하고 있습니다.관련성이 있는 경우 백엔드 코드는 Perl(v5.24.1)로 작성되며,DBI모듈.

이 (비즈니스 로직)은, 다음의 장소에서는 처리할 수 없습니다.SCHEMA제약 수준, 단, 둘 중 하나를 통해TRIGGERS또는STORED PROCEDURES.

저장 프로시저는 (다시) 검증하여 INSERT/UPDATE/DELETE를 허용합니다.

Business Logic을 따르지 않으면 트리거가 비활성화됩니다.

개인의 취향, 비즈니스 로직은 디스플레이와 백엔드에 맡깁니다.이를 DB에서 유지 관리하는 것은 매우 어렵고 오류가 발생하기 쉽고 변경에 유연하지 않습니다.하지만, 외부 앱에 대해서는, 일을 망칠 것 같아요.따라서 직접적인 DB 조작이 아닌 API를 제공합니다.

언급URL : https://stackoverflow.com/questions/46226819/sql-constraint-check-based-on-foreign-keys