Postgres ENUM 데이터 유형 또는 CHECK CONSTARINT?

Postgres ENUM 데이터 유형 또는 CHECK CONSTARINT?

MySQL DB를 Pg(9.1)로 마이그레이션하고 Pg에 새 데이터 유형을 만든 다음 열 정의로 사용하여 MySQL ENUM 데이터 유형을 에뮬레이트하고 있습니다.제 질문은 -- 제가 대신 제약 조건 확인을 사용하는 것이 더 나을까요?MySQL ENUM 유형은 행에 특정 값 항목을 적용하기 위해 구현됩니다.CHECK CONSTARTION을 사용하여 수행할 수 있습니까? 만약 그렇다면 더 나은(또는 더 나쁜) 방법이 있습니까?

여기에 있는 논평과 답변, 그리고 몇 가지 기초적인 연구를 바탕으로 포스트그레스 시대의 논평을 위해 다음과 같은 요약을 제공합니다.의견을 주시면 정말 감사하겠습니다.

Postgres 데이터베이스 테이블 열에서 항목을 제한하는 세 가지 방법이 있습니다.'빨간색', '녹색' 또는 '파란색'만 유효한 항목으로 사용할 경우 "색상"을 저장할 표를 고려합니다.

1. 열거된 데이터 유형

   CREATE TYPE valid_colors AS ENUM ('red', 'green', 'blue');
   
   CREATE TABLE t (
       color VALID_COLORS
   );
   

   장점은 유형을 한 번 정의한 다음 필요한 만큼의 테이블에서 재사용할 수 있다는 것입니다.표준 쿼리는 ENUM 유형의 모든 값을 나열할 수 있으며 응용프로그램 양식 위젯을 만드는 데 사용할 수 있습니다.

   SELECT  n.nspname AS enum_schema,  
           t.typname AS enum_name,  
           e.enumlabel AS enum_value
   FROM    pg_type t JOIN 
           pg_enum e ON t.oid = e.enumtypid JOIN 
           pg_catalog.pg_namespace n ON n.oid = t.typnamespace
   WHERE   t.typname = 'valid_colors'
   
    enum_schema | enum_name     | enum_value 
   -------------+---------------+------------
    public      | valid_colors  | red
    public      | valid_colors  | green
    public      | valid_colors  | blue
   

   단점은 ENUM 유형이 시스템 카탈로그에 저장되므로 정의를 보려면 위와 같은 쿼리가 필요하다는 것입니다.이러한 값은 테이블 정의를 볼 때 명확하게 나타나지 않습니다.또한 ENUM 유형은 실제로 기본 제공되는 NUMERICAL 및 TEXT 데이터 유형과 별개의 데이터 유형이므로 일반 숫자 및 문자열 연산자와 함수는 해당 유형에서 작동하지 않습니다.그래서 다음과 같은 질문을 할 수 없습니다.

   SELECT FROM t WHERE color LIKE 'bl%'; 
   

2. 제약 조건 확인

   CREATE TABLE t (
       colors TEXT CHECK (colors IN ('red', 'green', 'blue'))
   );
   

   두 가지 장점은 첫째, "보이는 것이 얻는 것이다"라는 것입니다. 즉, 열에 대한 유효한 값이 테이블 정의에 바로 기록되고 둘째, 모든 네이티브 문자열 또는 숫자 연산자가 작동한다는 것입니다.

3. 외부 키

   CREATE TABLE valid_colors (
       id SERIAL PRIMARY KEY NOT NULL,
       color TEXT
   );
   
   INSERT INTO valid_colors (color) VALUES 
       ('red'),
       ('green'),
       ('blue');
   
   CREATE TABLE t (
       color_id INTEGER REFERENCES valid_colors (id)
   );
   

   기본적으로 ENUM 유형을 만드는 것과 동일하지만 네이티브 숫자 또는 문자열 연산자가 작동하며 유효한 값을 검색하기 위해 시스템 카탈로그를 쿼리할 필요가 없습니다.연려면조필다니합요를 합니다.color_id원하는 텍스트 값까지 입력합니다.

다른 답변에서 알 수 있듯이, 제약 조건에 유연성 문제가 있지만 정수 ID에 외래 키를 설정하려면 조회 중에 참여해야 합니다.허용된 값을 참조 테이블의 자연 키로 사용하는 것이 어떻습니까?

펑크시의 답변에서 스키마를 수정하는 방법:

CREATE TABLE valid_colors (
    color TEXT PRIMARY KEY
);

INSERT INTO valid_colors (color) VALUES 
    ('red'),
    ('green'),
    ('blue');

CREATE TABLE t (
    color TEXT REFERENCES valid_colors (color) ON UPDATE CASCADE
);

조건의 조인이 값 추가할 수 값 할 수 .ON UPDATE CASCADE " "한다고, (예: "적색" "적색" "적색", "적색")valid_colors이에 따라 변경사항이 자동으로 전파됩니다.

외부 키와 확인 제약 조건의 큰 단점 중 하나는 모든 보고 또는 UI 디스플레이가 텍스트에 대한 ID를 확인하기 위해 조인을 수행해야 한다는 것입니다.

소규모 시스템에서는 큰 문제가 되지 않지만, 매우 많은 소규모 룩업 테이블이 있는 HR 또는 유사한 시스템에서 작업하는 경우 텍스트를 얻기 위해 많은 조인이 발생하므로 매우 큰 문제가 될 수 있습니다.

값이 적고 거의 변경되지 않는 경우 텍스트 필드에 제약 조건을 사용하거나 정수 ID 필드에 대해 룩업 테이블을 사용하는 것이 좋습니다.

PostgreSQL에는 열거형이 있으며 정상적으로 작동합니다.열거형이 제약 조건보다 "더 나은"지는 모르겠지만 둘 다 작동합니다.

내 관점에서 볼 때, 동일한 가치들의 집합이 주어집니다.

1. 여러 열에서 사용할 경우 Enum이 더 나은 솔루션입니다.
2. 응용 프로그램에서 열 값을 하나만 제한하려면 검사 제약 조건을 사용하는 것이 좋습니다.

물론 의사 결정 과정에서 슬금슬금 나타날 수 있는 다른 매개 변수들도 많이 있습니다(일반적으로 기본 제공 연산자를 사용할 수 없다는 사실). 하지만 저는 이 두 가지가 가장 보편적인 매개 변수라고 생각합니다.

저는 누군가가 포스트그레의 좋은 답변에 동의하기를 바랍니다.SQL 데이터베이스 측면에서는 둘 중 하나가 다른 하나보다 선호되는 이유에 대해 설명합니다.

소프트웨어 개발자의 관점에서 PostgreSQL enum은 SQL에서 다음과 같은 업데이트/삽입을 수행할 캐스트가 필요하기 때문에 체크 제약 조건을 사용하는 것을 약간 선호합니다.

INSERT INTO table1 (colA, colB) VALUES('foo', 'bar'::myenum)

여기서 "myenum"은 Postgre에서 지정한 열거형입니다.SQL.

따라서 SQL을 휴대할 수 없게 만들 뿐만 아니라 애플리케이션을 개발하는 동안 처리해야 하는 또 다른 문제이기도 합니다. 따라서 VARCHAR(또는 기타 일반적인 기본 요소)에 체크 제약이 있는 것을 선호합니다.

참고로 MySQL 열거에는 이러한 유형의 캐스트가 필요하지 않으므로 Postgre와 관련이 있습니다.제 경험으로는 SQL입니다.

언급URL : https://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint