MySQL - 조건부 외부 키 제약사항

MySQL - 조건부 외부 키 제약사항

팔로워가 있다comments내 앱의 테이블:

comments
--------
id           INT
foreign_id   INT
model        TEXT
comment_text TEXT
...

이 표의 아이디어는 내 앱의 다양한 부분에 대한 코멘트를 저장하는 것입니다. 블로그 투고에 대한 코멘트를 저장할 수 있습니다.

1|34|blogpost|lorem ipsum...

사용자 사진:

2|12|picture|lorem ipsum...

기타 등등.

이러한 데이터에 FORENAL KEY 제약을 가할 수 있는 방법이 있습니까?

즉, 코멘트 테이블에는 다음과 같은 것이 있습니다.

FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
-- but only when model='blogpost'

다형성 어소시에이션이라고 불리는 디자인을 시도하고 있습니다.즉, 외부 키는 관련된 여러 테이블의 행을 참조할 수 있습니다.

그러나 외부 키 제약 조건에서는 정확히 하나의 테이블을 참조해야 합니다.의 다른 열의 값에 따라 다른 테이블을 참조하는 외부 키를 선언할 수 없습니다.Comments테이블. 이것은 관계형 데이터베이스 설계의 몇 가지 규칙을 위반할 수 있습니다.

더 나은 해결책은 코멘트에 의해 참조되는 일종의 "슈퍼테이블"을 만드는 것입니다.

CREATE TABLE Commentable (
  id SERIAL PRIMARY KEY
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  foreign_id INT NOT NULL,
  ...
  FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);

각 콘텐츠 유형은 이 슈퍼테이블의 하위 유형으로 간주됩니다.이는 인터페이스의 객체 지향 개념과 유사합니다.

CREATE TABLE BlogPosts (
  blogpost_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);

CREATE TABLE UserPictures (
  userpicture_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);

행을 삽입하기 전에BlogPosts또는UserPictures새 행을 삽입해야 합니다.Commentable새로운 의사 ID를 생성합니다.그런 다음 각 하위 유형 테이블에 내용을 삽입할 때 생성된 ID를 사용할 수 있습니다.

이 모든 작업을 완료하면 참조 무결성 제약에 의존할 수 있습니다.

MySQL 5.7은 하나의 다형 테이블로 다형 외형키와 같은 것을 즐길 수 있습니다!

주의: 기술적으로는 복수의 컬럼(코멘트가 있는 각 엔티티당1개씩)에 복수의 FK로 실장할 필요가 있습니다만, 실장은 DB측으로 한정될 수 있습니다(즉, 코드의 이러한 컬럼에 대해서는 염려할 필요가 없습니다).

MySQL의 Generated Columns를 사용하는 것이 좋습니다.

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  foreign_id INT,
  model TEXT,
  commented_text TEXT,
  generated_blogpost_id INT AS (IF(model = 'blogpost', foreign_id, NULL)) STORED,
  generated_picture_id INT AS (IF(model = 'picture', foreign_id, NULL)) STORED,
  PRIMARY KEY (id) ,
  FOREIGN KEY (`generated_blogpost_id`) REFERENCES blogpost(id) ON DELETE CASCADE,
  FOREIGN KEY (`generated_picture_id`) REFERENCES picture(id) ON DELETE CASCADE
)

무시해도 됩니다.generated_*코멘트가 추가 또는 수정될 때 MySQL에 의해 자동으로 입력되며 코멘트에 대해 정의된 FK는 예상대로 데이터의 일관성을 보장합니다.

사이즈 요건과 퍼포먼스에 모두 영향을 미치는 것은 분명하지만, 일부(대부분?) 시스템에서는 무시할 수 있는 수준이며, 보다 심플한 설계로 데이터의 일관성을 실현하는 데 드는 대가는 상당합니다.

언급URL : https://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints