CREATE | ALTER TABLE … CONSTRAINT

CREATE | ALTER TABLE … CONSTRAINT

이 주제는 CREATE TABLE, CREATE HYBRID TABLE, 또는 ALTER TABLE 문에서 CONSTRAINT 절을 지정해 제약 조건(constraint)을 만드는 방법을 설명해요.

출처: CREATE | ALTER TABLE … CONSTRAINT

본문

이 주제는 CREATE TABLE, CREATE HYBRID TABLE, 또는 ALTER TABLE 문에서 CONSTRAINT 절을 지정해 제약 조건을 만드는 방법을 설명합니다. 자세한 내용은 Constraints 문서를 참고하세요. 하이브리드 테이블을 만들거나 변경할 때 제약 조건 정의의 구문은 같지만, 규칙과 요구 사항은 다릅니다.

Syntax for inline constraints (인라인 제약 조건 구문)

CREATE TABLE <name> (
  <col1_name> <col1_type>  [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }
  [ , <col2_name> <col2_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ADD COLUMN
  <col_name> <col_type> [ NOT NULL ] { inlineUniquePK | inlineFK | inlineCH }

Where:

inlineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY }
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineFK ::=
  [ CONSTRAINT <constraint_name> ]
  [ FOREIGN KEY ]
  REFERENCES <ref_table_name> [ ( <ref_col_name> ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]

inlineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Syntax for out-of-line constraints (아웃-오브-라인 제약 조건 구문)

CREATE TABLE <name> ... (
  <col1_name> <col1_type>
  [ , <col2_name> <col2_type> , ... ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , { outoflineUniquePK | outoflineFK | outoflineCH } ]
  [ , ... ]
)

ALTER TABLE <name> ... ADD { outoflineUniquePK | outoflineFK | outoflineCH }

Where:

outoflineUniquePK ::=
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY } ( <col_name> [ , <col_name> , ... ] )
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineFK ::=
  [ CONSTRAINT <constraint_name> ]
  FOREIGN KEY ( <col_name> [ , <col_name> , ... ] )
  REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> , ... ] ) ]
  [ MATCH { FULL | SIMPLE | PARTIAL } ]
  [ ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
       [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ] ]
  [ [ NOT ] ENFORCED ]
  [ [ NOT ] DEFERRABLE ]
  [ INITIALLY { DEFERRED | IMMEDIATE } ]
  [ { ENABLE | DISABLE } ]
  [ { VALIDATE | NOVALIDATE } ]
  [ { RELY | NORELY } ]
  [ COMMENT '<string_literal>' ]

outoflineCH ::=
  [ CONSTRAINT <constraint_name> ] CHECK ( <expr> )
  [ ENABLE { VALIDATE | NOVALIDATE } ]

Constraint properties (제약 조건 속성)

다른 데이터베이스와의 호환성과 하이브리드 테이블 사용을 위해 Snowflake는 제약 조건 속성을 제공해요. 제약 조건에 지정할 수 있는 속성은 유형에 따라 다릅니다.

중요 표준 Snowflake 테이블의 경우 이 속성들은 다른 데이터베이스에서 마이그레이션을 용이하게 하기 위해 제공됩니다. Snowflake가 이를 강제(enforced)하거나 유지하지는 않아요. 이는 이 속성들의 기본값을 변경할 수 있지만, 기본값을 변경하면 Snowflake가 제약 조건을 만들지 않는다는 뜻입니다. 예외는 RELY 속성이에요. 표준 테이블의 데이터가 UNIQUE, PRIMARY KEY, FOREIGN KEY 제약 조건을 준수함을 확인했다면 그 제약 조건에 RELY 속성을 설정할 수 있습니다. Setting the RELY Constraint Property to Eliminate Unnecessary Joins 문서 참고. 하이브리드 테이블을 만들거나 변경할 때는 규칙과 요구 사항이 다릅니다. Overview of constraints 참고.

지원되는 제약 조건 속성 대부분은 ANSI SQL 표준 속성이지만, 다음 속성은 Snowflake 확장이에요. 아웃-오브-라인 제약 조건 정의 안에 주석을 정의할 수도 있습니다.

Properties (for all constraints)

[ NOT ] ENFORCED
[ NOT ] DEFERRABLE
INITIALLY { DEFERRED | IMMEDIATE }
{ ENABLE | DISABLE }
{ VALIDATE | NOVALIDATE }
{ RELY | NORELY }

• { ENFORCED | NOT ENFORCED } — 제약 조건이 트랜잭션에서 강제되는지 여부를 지정해요. 표준 테이블의 경우 이 속성과 무관하게 NOT NULL과 CHECK만 Snowflake가 강제하는 제약 조건 유형이에요. 하이브리드 테이블의 경우 PRIMARY KEY, FOREIGN KEY, UNIQUE 제약 조건에 NOT ENFORCED 속성을 설정할 수 없습니다 — 설정하면 "invalid constraint property" 오류가 발생해요. 기본값: NOT ENFORCED
• { NOT } DEFERRABLE — 이후 트랜잭션에서 제약 조건 검사를 트랜잭션 끝까지 지연할 수 있는지 여부를 지정해요. 기본값: NOT DEFERRABLE
• INITIALLY { DEFERRED | IMMEDIATE } — DEFERRABLE 제약 조건에 대해, 다음 트랜잭션부터 제약 조건 검사를 지연할 수 있는지 지정해요. 기본값: INITIALLY DEFERRED
• { ENABLE | DISABLE } — 제약 조건이 활성화되는지 비활성화되는지 지정해요. Oracle 호환성을 위해 제공됩니다. 기본값: DISABLE
• { VALIDATE | NOVALIDATE } — 제약 조건 생성 시 테이블의 기존 데이터를 검증할지 지정해요. { ENFORCED | NOT ENFORCED } 또는 { ENABLE | DISABLE }이 지정된 경우에만 적용됩니다. PRIMARY KEY와 FOREIGN KEY 제약 조건의 기본값: NOVALIDATE. CHECK 제약 조건의 기본값: VALIDATE. 하이브리드 테이블의 경우 ALTER TABLE로 UNIQUE 또는 FOREIGN KEY 제약 조건을 추가하면 이 속성과 무관하게 항상 테이블에 이미 있는 행을 검증해요.
• { RELY | NORELY } — NOVALIDATE 모드의 제약 조건이 쿼리 다시 쓰기(query rewrite) 중에 고려되는지 지정해요. 테이블의 데이터가 제약 조건을 준수함을 확인했다면 이 속성을 RELY로 변경해 쿼리 옵티마이저가 그런 데이터 무결성을 기대하도록 할 수 있어요. 표준 테이블의 경우 RELY 제약 조건을 강제하는 것은 사용자 책임이며, 그렇지 않으면 의도하지 않은 동작과 예상치 못한 결과가 발생할 수 있어요. RELY 속성이 설정된 제약 조건에서 참조 무결성 위반이 발생하면 DML 및 CTAS 문이 잘못된 데이터를 삽입할 수 있습니다. RELY 속성을 설정하면 불필요한 조인을 제거하는 등 쿼리 성능이 향상될 수 있어요. 관련 PRIMARY KEY와 FOREIGN KEY 제약 조건 모두에 이 속성을 설정하세요. 예:

  ALTER TABLE table_with_primary_key ALTER CONSTRAINT a_primary_key_constraint RELY;
  ALTER TABLE table_with_foreign_key ALTER CONSTRAINT a_foreign_key_constraint RELY;
  

  기본값: NORELY

Properties (for FOREIGN KEY constraints only)

MATCH { FULL | SIMPLE | PARTIAL }
ON [ UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
   [ DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]

• MATCH { FULL | PARTIAL | SIMPLE } — 하나 이상의 컬럼에서 NULL 값과 관련해 FOREIGN KEY 제약 조건이 충족되는지 여부를 지정해요. 기본값: MATCH FULL
• ON UPDATE ... — 외래 키의 기본 키 또는 고유 키가 업데이트될 때 수행되는 동작을 지정해요. 기본값: UPDATE NO ACTION
• ON DELETE ... — 외래 키의 기본 키 또는 고유 키가 삭제될 때 수행되는 동작을 지정해요. 기본값: DELETE NO ACTION

Properties (for CHECK constraints only)

CHECK ( <expr> )

• CHECK ( expr ) — 강제할 조건을 정의하는 표현식이에요. 표현식은 여러 항목을 포함할 수 있고, 특정 항목은 포함할 수 없어요. 자세한 내용은 CHECK constraints 문서를 참고하세요.

Non-default values for ENABLE and VALIDATE properties

다른 데이터베이스와의 구문 호환성을 위해 Snowflake는 제약 조건 속성의 기본값이 아닌 값을 지정하는 것을 지원해요. 그러나 PRIMARY KEY, UNIQUE, FOREIGN KEY 제약 조건의 경우 새 제약 조건을 만들 때 ENABLE 또는 VALIDATE(이 속성들의 기본값이 아닌 값)를 지정하면 제약 조건이 만들어지지 않습니다. 이는 RELY에는 적용되지 않아요. RELY를 지정하면 새 제약 조건이 생성됩니다.

CHECK 제약 조건의 경우 ENABLE이 기본값이며 필수입니다. DISABLE을 지정하면 CHECK 제약 조건이 만들어지지 않아요. 새 테이블에는 NOVALIDATE와 VALIDATE 모두 지원되며, VALIDATE는 기존 테이블에서는 지원되지 않습니다. Snowflake는 제약 조건 생성 중 기본값이 아닌 값을 지정할 때 오류를 생성할지 여부를 결정하는 세션 파라미터 UNSUPPORTED_DDL_ACTION을 제공해요.

Comments on constraints (제약 조건 주석)

다른 데이터베이스 객체와 유사하게 Snowflake는 제약 조건에 대한 주석을 지원해요. 다음 제한 사항을 참고하세요.

Usage notes (사용 참고 사항)

Access control requirements (접근 제어 요구 사항)

• PRIMARY KEY 또는 UNIQUE 제약 조건을 만들려면:
• FOREIGN KEY 제약 조건을 만들려면:
• REFERENCES 권한은 GRANT <privileges> … TO ROLE 및 REVOKE <privileges> … FROM ROLE 명령으로 역할에 부여·취소할 수 있어요:

  GRANT REFERENCES ON TABLE <pk_table_name> TO ROLE <role_name>
  
  REVOKE REFERENCES ON TABLE <pk_table_name> FROM ROLE <role_name>
  

Examples of constraints with standard tables (표준 테이블 제약 조건 예제)

하이브리드 테이블 제약 조건 예제는 CREATE HYBRID TABLE을 참고하세요.

테이블을 만들면서 동시에 간단한 NOT NULL 제약 조건을 만들고, 테이블을 변경하면서 또 다른 NOT NULL 제약 조건을 만들어 봅시다.

테이블을 만들면서 제약 조건을 만들어 봅시다.

CREATE TABLE table1 (col1 INTEGER NOT NULL);

테이블을 변경해 제약 조건이 있는 컬럼을 추가해 봅시다.

ALTER TABLE table1 ADD COLUMN col2 VARCHAR NOT NULL;

다음 예제는 컬럼의 의도가 고유 값을 보유하는 것임을 지정하지만, 제약 조건이 실제로는 강제되지 않음을 분명히 해요. 또한 제약 조건의 이름(uniq_col3)을 지정하는 방법을 보여줍니다.

ALTER TABLE table1
  ADD COLUMN col3 VARCHAR NOT NULL CONSTRAINT uniq_col3 UNIQUE NOT ENFORCED;

다음은 PRIMARY KEY 제약 조건이 있는 부모 테이블과, 첫 번째 테이블의 PRIMARY KEY 제약 조건과 같은 컬럼을 가리키는 FOREIGN KEY 제약 조건이 있는 다른 테이블을 만들어요.

CREATE TABLE table2 (
  col1 INTEGER NOT NULL,
  col2 INTEGER NOT NULL,
  CONSTRAINT pkey_1 PRIMARY KEY (col1, col2) NOT ENFORCED
);
CREATE TABLE table3 (
  col_a INTEGER NOT NULL,
  col_b INTEGER NOT NULL,
  CONSTRAINT fkey_1 FOREIGN KEY (col_a, col_b) REFERENCES table2 (col1, col2) NOT ENFORCED
);

다음 예제는 CREATE TABLE 문에서 인라인 CHECK 제약 조건을 지정해요.

CREATE TABLE test_check_constraint_orders (
  order_id INT,
  quantity INT CHECK (quantity > 0),
  price NUMBER(10, 2));

이 CHECK 제약 조건은 quantity가 음수이거나 0이기 때문에 다음 DML 작업들에서 실패해요.

INSERT INTO test_check_constraint_orders (order_id, quantity, price)
  VALUES (101, -5, 25.35);

UPDATE test_CHECK_constraint_orders
  SET quantity = 0
  WHERE order_id = 101;

다음 예제는 여러 컬럼에 대한 아웃-오브-라인 CHECK 제약 조건을 지정해요.

CREATE TABLE test_check_constraint_max_orders (
  order_id INT,
  quantity INT,
  price NUMBER(10, 2),
  max_price NUMBER(10, 2),
  CONSTRAINT chk_price_max CHECK (price < max_price));

이 CHECK 제약 조건은 price가 최대 가격을 초과하지 않도록 보장해요. 다음 예제는 CTAS 문에서 인라인 CHECK 제약 조건을 지정합니다.

CREATE TABLE high_value_products (
  product_id INT,
  product_name VARCHAR(100),
  list_price NUMBER(10, 2),
  CONSTRAINT high_price CHECK (list_price > 100)
  )
  AS SELECT product_id,
            product_name,
            list_price
  FROM products
  WHERE list_price > 100;

이 CHECK 제약 조건은 새 high_value_products 테이블이 고가로 간주되는 항목만 포함하도록 보장해요.

더 알아보기 (Learn more)

• CREATE TABLE
• ALTER TABLE
• CREATE HYBRID TABLE
• Constraints