[sql] PostgreSQL에서 다 대다 관계를 구현하는 방법은 무엇입니까?

Program / 글쓴이

제목은 자명하다고 생각합니다. 다 대다 관계를 만들기 위해 PostgreSQL에서 테이블 구조를 어떻게 생성합니까?

내 예 :

Product(name, price);
Bill(name, date, Products);

답변

SQL DDL (데이터 정의 언어) 문은 다음과 같습니다.

CREATE TABLE product (
  product_id serial PRIMARY KEY  -- implicit primary key constraint
, product    text NOT NULL
, price      numeric NOT NULL DEFAULT 0
);

CREATE TABLE bill (
  bill_id  serial PRIMARY KEY
, bill     text NOT NULL
, billdate date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE bill_product (
  bill_id    int REFERENCES bill (bill_id) ON UPDATE CASCADE ON DELETE CASCADE
, product_id int REFERENCES product (product_id) ON UPDATE CASCADE
, amount     numeric NOT NULL DEFAULT 1
, CONSTRAINT bill_product_pkey PRIMARY KEY (bill_id, product_id)  -- explicit pk
);

몇 가지 조정을했습니다.

  • N : m 관계는 일반적으로 별도의 테이블로 구현한다 – bill_product이 경우.

  • serial열을 대리 기본 키로 추가했습니다 . Postgres 10 이상에서는 대신 IDENTITY열을 고려하십시오 . 보다:

    제품 이름이 거의 고유하지 않기 때문에 강력하게 추천합니다 (좋은 “자연스러운 키”가 아님). 또한 고유성을 적용하고 외래 키에서 열을 참조하는 것은 또는로 저장된 문자열보다 4 바이트 integer(또는 8 바이트 bigint)에서 일반적으로 더 저렴 합니다.textvarchar

  • 같은 기본 데이터 유형의 이름을 사용하지 마십시오 date으로 식별자 . 이것이 가능하지만 잘못된 스타일이며 혼란스러운 오류 및 오류 메시지로 이어집니다. 사용 법률, 소문자, 인용 부호로 둘러싸이지 않은 식별자 . 예약어를 사용하지 말고 가능하면 큰 따옴표로 묶인 대소 문자 구분 식별자를 사용하지 마십시오 .

  • “이름”은 좋은 이름이 아닙니다. 나는 테이블의 열 이름을 변경 product수 product(또는 product_name유사한). 더 나은 명명 규칙 입니다. 그렇지 않으면 관계형 데이터베이스에서 많은 작업을 수행하는 쿼리에서 두 개의 테이블을 조인 할 때 “name”이라는 이름의 여러 열이 생성되고 열 별칭을 사용하여 엉망을 정리해야합니다. 도움이되지 않습니다. 널리 퍼져있는 또 다른 안티 패턴은 열 이름으로 “id”입니다.
    의 이름이 무엇인지 잘 모르겠습니다 bill. bill_id이 경우에는 충분할 것입니다.

  • price입력 된대로 정확하게 분수를 저장 하는 데이터 유형 입니다 (부동 소수점 유형 대신 임의 정밀도 유형). 정수만 다루면 . 예를 들어 가격을 Cents로 저장할 수 있습니다.numericinteger

  • amount( "Products"귀하의 질문에)이 연결 테이블로 전환 bill_product및 유형입니다 numeric뿐만 아니라. 다시 말하지만, integer정수만 다루면.

  • 당신은 볼 외래 키 에를 bill_product? 계단식 변경을 위해 두 가지를 모두 만들었습니다 ON UPDATE CASCADE. A는 경우 product_id또는 bill_id변경해야 변경 내용이 모든 따라 항목에 직렬로 연결된 bill_product아무것도 중단합니다. 그것들은 그 자체의 의미가없는 참조 일뿐입니다.
    또한 사용 ON DELETE CASCADE을 위해 bill_id: 법안이 삭제됩니다 경우, 그 세부 사항은 그것으로 죽는다.
    그렇지 않은 제품 : 청구서에 사용 된 제품을 삭제하고 싶지 않습니다. 이 작업을 시도하면 Postgres에서 오류가 발생합니다. product대신 사용되지 않는 행을 표시 하기 위해 에 다른 열을 추가 합니다 ( “소프트 삭제”).

  • 이 기본 예의 모든 열은 결국 NOT NULL이므로 NULL값이 허용되지 않습니다. (예, 모든 열-기본 키 열은 UNIQUE NOT NULL자동으로 정의 됩니다.) 이는 NULL값이 어떤 열에서도 의미가 없기 때문 입니다. 초보자의 삶을 더 쉽게 만듭니다. 그러나 당신은 그렇게 쉽게 벗어나지 않을 것 입니다. 어쨌든 NULL취급 을 이해해야 합니다. 추가 열은 NULL값, 함수 및 조인을 허용 할 수 NULL있으며 쿼리 등에 값을 도입 할 수 있습니다 .

  • CREATE TABLE설명서 의 장을 읽으십시오 .

  • 기본 키는 키 열에 대한 고유 인덱스 로 구현되어 PK 열에 대한 조건이있는 쿼리를 빠르게 수행합니다. 그러나 키 열의 순서는 다중 열 키와 관련이 있습니다. 약동학에 대한이 때문에 bill_product켜져 (bill_id, product_id)내 예를 들어, 당신은 단지의 다른 인덱스를 추가 할 수 있습니다 product_id또는 (product_id, bill_id)당신은 쿼리가 주어진 찾고있는 경우 product_id도없고 bill_id. 보다:

  • 설명서의 색인에 대한 장을 읽으십시오 .

답변