SQL에서 두 테이블이 서로 참조해도 괜찮습니까?

SQL에서 두 테이블이 서로 참조해도 괜찮습니까?

이 시스템에서는 제품, 제품 이미지(제품에는 여러 이미지가 있을 수 있음) 및 제품의 기본 이미지를 저장합니다.데이터베이스:

CREATE TABLE  `products` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  `DESCRIPTION` text NOT NULL,
  `ENABLED` tinyint(1) NOT NULL DEFAULT '1',
  `DATEADDED` datetime NOT NULL,
  `DEFAULT_PICTURE_ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index_2` (`DATEADDED`),
  KEY `FK_products_1` (`DEFAULT_PICTURE_ID`),
  CONSTRAINT `FK_products_1` FOREIGN KEY (`DEFAULT_PICTURE_ID`) REFERENCES `products_pictures` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;


CREATE TABLE  `products_pictures` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMG_PATH` varchar(255) NOT NULL,
  `PRODUCT_ID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_products_pictures_1` (`PRODUCT_ID`),
  CONSTRAINT `FK_products_pictures_1` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `products` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

당신이 볼 수 있듯이.products_pictures.PRODUCT_ID -> products.ID그리고.products.DEFAULT_PICTURE_ID -> products_pictures.ID주기 참조입니다.그것은 괜찮나요?

아니, 그것은 좋지 않습니다.테이블 사이의 순환 참조가 지저분합니다.다음(10년 전) 기사를 참조하십시오.SQL 설계 기준: 순환 참조

일부 DBMS는 이러한 문제를 특별히 주의하여 처리할 수 있지만 MySQL에는 문제가 있습니다.

---

옵션 1

두 개의 FK 중 하나를 무효로 만드는 것이 당신의 설계입니다.이렇게 하면 닭과 달걀 문제를 해결할 수 있습니다(어떤 표에 먼저 삽입해야 합니까?).

하지만 당신의 코드에 문제가 있습니다.그러면 제품이 기본 사진을 가질 수 있고 해당 사진은 다른 제품을 참조할 수 있습니다!

이러한 오류를 허용하지 않으려면 FK 제약 조건이 다음과 같아야 합니다.

CONSTRAINT FK_products_1 
  FOREIGN KEY (id, default_picture_id) 
  REFERENCES products_pictures (product_id, id)
  ON DELETE RESTRICT                            --- the SET NULL options would 
  ON UPDATE RESTRICT                            --- lead to other issues

이 작업에는 다음이 필요합니다.UNIQUE테이블의 조건products_pictures(product_id, id)위의 FK가 정의되고 제대로 작동하기 위해.

---

옵션 2

또 다른 접근 방식은 다음을 제거하는 것입니다.Default_Picture_ID은 다음과 같습니다.product를 합니다.IsDefault BIT 열picturetable.이 한 비트를 입니다. 이. 솔 션 의 문 제 는 비 해 켜 를 트 고 허 사 모 꺼 진 은 다 니 방 표 허 입 는 하 법 용 록 도 지 든 루 다 하 른 록 용 도 당 만 품 진 사 제 당 의 장 한 ▁table ▁the ▁is ▁per ▁solution ▁allow ▁to ▁on SQL-Server(및 Postgres의 경우)에서는 부분 인덱스를 사용하여 이 작업을 수행할 수 있습니다.

CREATE UNIQUE INDEX is_DefaultPicture 
  ON products_pictures (Product_ID)
  WHERE IsDefault = 1 ;

그러나 MySQL에는 이러한 기능이 없습니다.

---

옵션 3

은 두 개의 을 모두 FK 열로 할 수 해줍니다.NOT NULL연기 가능한 제약 조건을 사용하는 것입니다.이것은 Postgre에서 작동합니다.SQL과 저는 Oracle에서 생각합니다.이 질문과 @Erwin: SQLChemy의 복합 외부 키 제약 조건(All key column NOT NULL Part)의 답변을 확인합니다.

MySQL의 제약 조건은 연기할 수 없습니다.

---

옵션 4

(은 (가가명다생고각는하제것다입니제는거하다음접을근은확장하법▁the▁(▁the제▁(다것니est▁remove▁is▁to입which▁approach)▁clean)를 제거하는 것입니다.Default_Picture_ID열을 열고 다른 테이블을 추가합니다. 조건에 은 FK 제약원없로으모 FK .NOT NULL다음 솔루션을 사용합니다.

product_default_picture
----------------------
product_id          NOT NULL
default_picture_id  NOT NULL
PRIMARY KEY (product_id)
FOREIGN KEY (product_id, default_picture_id)
  REFERENCES products_pictures (product_id, id)

이를 위해서는 다음이 필요합니다.UNIQUE테이블의 조건products_pictures(product_id, id)용액 1에서와 같이.

---

요약하면 MySQL에는 두 가지 옵션이 있습니다.

• 옵션 1(nullable FK 열)과 위의 수정 사항을 사용하여 무결성을 올바르게 적용합니다.

• 옵션 4(nullable FK 열 없음)

삽입을 수행할 때 발생하는 유일한 문제입니다.어떤 것을 먼저 삽입합니까?

이를 통해 다음과 같은 작업을 수행해야 합니다.

• null 기본 그림이 있는 제품 삽입
• 새로 만든 제품 ID로 사진 삽입
• 제품을 업데이트하여 기본 사진을 방금 삽입한 사진으로 설정합니다.

다시 말하지만 삭제하는 것은 재미가 없습니다.

이것은 단지 제안일 뿐이지만 가능하다면 이 테이블 사이에 조인 테이블 하나를 만드는 것이 추적에 도움이 될 수 있습니다.

product_productcat_join
------------------------
ID(PK)
ProductID(FK)- product table primary key
PictureID(FK) - category table primary key

다른 표에서는 외부 키 제약 조건 없이 해당 필드를 유지할 수 있습니다.더 작은 테이블로 처리하지만 프로세스의 결과로 더 큰 테이블에 연결하려는 경우에 유용합니다.

예를 들어 국가, 지역, 도시, 주소, 경도 및 위도 정보가 들어 있는 product_location 테이블을 추가하는 경우.당신이 지도에서 원 안에 있는 제품을 보여주고 싶은 경우가 있을 수 있습니다.

John이 하는 일은 나쁘지 않지만 PK-FK를 사용하면 중복 반복 데이터를 제거하여 데이터를 정규화하는 데 도움이 됩니다.로부터의 환상적인 장점들이 있습니다.

• 동일한 데이터에 대한 중복 스토리지 위치가 제거되어 데이터 무결성 향상
• 잠금 경합 감소 및 다중 사용자 동시성 향상
• 더 작은 파일

그것은 순환 참조가 아니며, 그것은 pk-fk입니다.

언급URL : https://stackoverflow.com/questions/10446641/in-sql-is-it-ok-for-two-tables-to-refer-to-each-other