제약 조건

제약 조건 (Constraints)

데이터 타입만으로는 데이터를 충분히 통제하지 못할 때가 많아요. 예를 들어 상품 가격 열은 양수만 받아야 할 텐데, "양수만 받는" 표준 데이터 타입은 없죠. 또 한 열의 값만 제한하는 게 아니라 다른 열이나 다른 행과의 관계까지 제한하고 싶을 때도 있어요. 그럴 때 쓰는 것이 바로 제약 조건(constraint)이에요.

출처: 공식문서

제약 조건은 테이블 데이터에 대해 원하는 만큼 통제권을 줘요. 사용자가 제약 조건을 위반하는 데이터를 저장하려 하면 오류가 발생하는데, 그 값이 기본값 정의에서 온 경우에도 마찬가지예요. 종류는 크게 여섯 가지예요: Check, Not-Null, Unique, Primary Key, Foreign Key, Exclusion.

Check 제약 조건

check 제약 조건은 가장 일반적인 제약 조건 종류예요. 특정 열의 값이 Boolean(참/거짓) 표현식을 만족해야 한다고 지정할 수 있어요. 상품 가격이 양수여야 한다면 이렇게요:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

제약 조건 정의는 기본값 정의처럼 데이터 타입 뒤에 와요. 기본값과 제약 조건은 어떤 순서로 나열해도 돼요. check 제약 조건은 CHECK 키워드 뒤에 괄호로 감싼 표현식으로 구성돼요. 그 표현식은 제약되는 열을 포함해야 의미가 있어요.

이름 붙이기

제약 조건에 별도의 이름을 줄 수도 있어요. 그러면 오류 메시지가 명확해지고, 나중에 제약 조건을 수정할 때 참조할 수 있죠:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

이름을 지정하려면 CONSTRAINT 키워드 + 식별자 + 제약 조건 정의 순서로 쓰면 돼요. 이렇게 이름을 안 정하면 시스템이 알아서 이름을 골라줘요.

여러 열을 참조하는 check

check 제약 조건은 여러 열을 참조할 수도 있어요. 정가와 할인가를 저장하는데, 할인가가 정가보다 낮아야 한다고 확실히 하고 싶다면:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

처음 두 제약 조건은 익숙하죠. 세 번째는 새 문법인데, 특정 열에 붙지 않고 쉼표로 구분된 열 목록에서 별도의 항목으로 나타나요. 열 정의와 이런 제약 조건 정의는 섞어서 쓸 수 있어요.

처음 두 개처럼 특정 열에 붙은 것을 **열 제약 조건(column constraint)**이라 하고, 세 번째처럼 어느 한 열 정의와 분리돼 쓰인 것을 **테이블 제약 조건(table constraint)**이라 불러요. 열 제약 조건은 테이블 제약 조건으로도 쓸 수 있지만, 그 반대는 항상 가능하진 않아요. 열 제약 조건은 그것이 붙은 열만 참조해야 하기 때문이죠. (PostgreSQL은 이 규칙을 강제하진 않지만, 다른 DB와 호환되게 하려면 지키는 게 좋아요.)

check의 null 동작

check 제약 조건은 검사 표현식이 true나 null 값으로 평가되면 만족된 것으로 봐요. 대부분의 표현식은 피연산자 중 하나가 null이면 null로 평가되기 때문에, check 제약 조건은 제약된 열에 null 값이 들어오는 것을 막지 못해요. 열에 null이 없도록 확실히 하려면 다음 절에서 설명하는 not-null 제약 조건을 쓰면 돼요.

주의: 다른 데이터 참조 금지

PostgreSQL은 검사 중인 새 행·갱신 행 외의 다른 테이블 데이터를 참조하는 CHECK 제약 조건을 지원하지 않아요. 단순 테스트에선 동작하는 것처럼 보여도, 관련된 다른 행이 나중에 바뀌면 제약 조건이 거짓이 되는 상태에 도달할 수 있어서 데이터베이스 덤프·복원이 실패할 수 있어요. 행 간·테이블 간 제한이 필요하면 UNIQUE, EXCLUDE, FOREIGN KEY 제약 조건을 쓰는 게 좋아요. 행 삽입 시점에 다른 행을 딱 한 번 검사하고 싶다면 커스텀 트리거로 구현할 수 있어요.

또 한 가지, PostgreSQL은 CHECK 제약 조건의 조건이 불변(immutable)이라고 가정해요. 즉 같은 입력 행에 대해 항상 같은 결과를 낸다고 보는 거예요. 이 가정 덕분에 행이 삽입·갱신될 때만 check를 검사하고 그 외에는 검사하지 않는 거죠. 이 가정을 깨는 흔한 예가 check 표현식에서 사용자 정의 함수를 쓰고 그 함수의 동작을 나중에 바꾸는 거예요. 그렇게 하면 이제 제약 조건을 위반하는 행이 있어도 시스템이 알아차리지 못해요. 이런 변경은 ALTER TABLE로 제약 조건을 지우고, 함수 정의를 고친 뒤, 제약 조건을 다시 추가해 모든 행에 대해 재검사하는 것을 권장해요.

Not-Null 제약 조건

not-null 제약 조건은 열이 null 값을 취해서는 안 된다고 지정하는 단순한 제약이에요:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

명시적 이름도 줄 수 있어요:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text CONSTRAINT products_name_not_null NOT NULL,
    price numeric
);

not-null 제약 조건은 보통 열 제약 조건으로 써요. 테이블 제약 조건 문법도 있지만 표준이 아니고, 주로 pg_dump 용도로 쓰여요.

기능적으로는 CHECK (column_name IS NOT NULL)을 만드는 것과 같지만, PostgreSQL에서 명시적 not-null 제약 조건을 만드는 게 더 효율적이에요.

한 열에 제약 조건을 여러 개 달 수 있어요. 그냥 연달아 쓰면 되고, 순서는 상관없어요(검사 순서를 결정하지 않아요). 다만 한 열에 명시적 not-null 제약 조건은 최대 하나만 가능해요.

NOT NULL의 역(inverse)인 NULL 제약 조건도 있어요. 이건 열이 null이어야 한다는 뜻이 아니라(그건 쓸모없겠죠), "열이 null일 수도 있는" 기본 동작을 선택하는 거예요. NULL 제약 조건은 SQL 표준에 없으므로 이식 가능한 애플리케이션에서는 쓰지 말아야 해요. 일부 사용자가 스크립트에서 제약 조건을 쉽게 토글하려고 좋아하기는 하지만요.

팁: 대부분의 데이터베이스 설계에서 대다수 열은 not null로 표시해야 해요.

Unique 제약 조건

unique 제약 조건은 열 또는 열 그룹에 들어있는 데이터가 테이블의 모든 행 사이에서 유일하다는 것을 보장해요.

열 제약 조건으로 쓰면:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

테이블 제약 조건으로 쓰면:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

열 그룹에 대한 unique 제약 조건을 정의하려면 테이블 제약 조건으로 쓰고 열 이름을 쉼표로 구분해요:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

이건 a와 c 값의 조합이 테이블 전체에서 유일하다는 뜻이에요. 각 열 자체는 유일할 필요가 없고 (보통 그렇지도 않죠). unique 제약 조건에도 평소처럼 이름을 붙일 수 있어요.

unique 제약 조건을 추가하면 그 제약에 포함된 열(들)에 unique B-tree 인덱스가 자동으로 만들어져요. 일부 행만 대상으로 하는 유일성 제한은 unique 제약 조건으로는 쓸 수 없지만, unique 부분 인덱스(partial index)는 만들 수 있어요.

기본적으로는, 제약 조건에 포함된 모든 열의 값이 같은 행이 두 개 이상이면 unique 제약 조건을 위반한 거예요. 그런데 기본적으로 null 값 두 개는 이 비교에서 같지 않은 것으로 취급돼요. 즉 unique 제약 조건이 있어도 제약 열 중 하나 이상에 null이 있는 중복 행을 저장할 수 있죠. 이 동작은 NULLS NOT DISTINCT 절을 추가해 바꿀 수 있어요:

CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

기본 동작은 NULLS DISTINCT로 명시할 수 있어요. SQL 표준에 따르면 unique 제약 조건의 기본 null 취급은 구현별로 정해져 있고, 다른 구현은 다른 동작을 해요. 이식성을 염두에 둔 애플리케이션을 개발할 때는 주의하세요.

Primary Key (기본 키)

primary key 제약 조건은 열(또는 열 그룹)이 테이블 행의 유일한 식별자로 쓰일 수 있음을 나타내요. 그러려면 값이 유일하고 null이 아니어야 해요. 그래서 다음 두 테이블 정의는 같은 데이터를 받아들여요:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

primary key는 여러 열에 걸칠 수도 있어요. unique 제약 조건과 문법이 비슷해요:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

primary key를 추가하면 그 열(들)에 unique B-tree 인덱스가 자동 생성되고, 그 열(들)을 NOT NULL로 표시하도록 강제돼요.

테이블은 primary key를 최대 하나만 가질 수 있어요. 관계형 데이터베이스 이론은 모든 테이블이 primary key를 가져야 한다고 말하지만, PostgreSQL이 이 규칙을 강제하진 않아요. 그래도 지키는 게 보통 좋죠.

primary key는 문서화 목적과 클라이언트 애플리케이션 모두에 유용해요. 예를 들어 행 값을 수정할 수 있는 GUI 애플리케이션은 행을 유일하게 식별하기 위해 테이블의 primary key를 알아야 하죠. 또 데이터베이스 시스템이 primary key를 다양한 방식으로 활용해요. 예를 들어 primary key는 그 테이블을 참조하는 외래 키의 기본 대상 열을 정의해요.

Foreign Key (외래 키)

foreign key 제약 조건은 한 열(또는 열 그룹)의 값이 다른 테이블의 어떤 행에 나타나는 값과 일치해야 한다고 지정해요. 이로써 두 관련 테이블 사이의 **참조 무결성(referential integrity)**을 유지한다고 말해요.

앞서 여러 번 쓴 product 테이블이 있다고 할게요:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

이 상품들의 주문을 저장하는 테이블도 있다고 합시다. orders 테이블이 실제로 존재하는 상품의 주문만 담도록 확실히 하고 싶다고 해볼게요. 그래서 orders 테이블에 products 테이블을 참조하는 foreign key 제약 조건을 정의해요:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

이제 products 테이블에 없는 product_no인데 null이 아닌 주문을 만들 수 없어요.

이 상황에서 orders는 참조하는(referencing) 테이블, products는 참조되는(referenced) 테이블이라고 불러요. 열도 마찬가지로 참조하는/참조되는 열이 있죠.

열 목록 없이 더 짧게 쓸 수도 있어요:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

열 목록이 없으면 참조되는 테이블의 primary key가 참조 열로 쓰이기 때문이에요.

foreign key도 열 그룹을 제한·참조할 수 있어요. 그러려면 평소처럼 테이블 제약 조건 형태로 써야 해요:

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

제한되는 열의 개수와 타입은 참조되는 열의 개수와 타입과 일치해야 해요.

자기 참조 외래 키

외래 키의 "다른 테이블"이 바로 그 테이블 자신인 경우도 유용한데, 이를 자기 참조(self-referential) 외래 키라고 불러요. 테이블 행이 트리 구조의 노드를 나타내고 싶다면 이렇게 쓸 수 있어요:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

최상위 노드는 parent_id가 NULL이고, null이 아닌 parent_id 항목은 테이블의 유효한 행을 참조하도록 제한돼요.

다대다 관계

테이블은 foreign key 제약 조건을 여러 개 가질 수 있고, 이는 테이블 간 다대다 관계를 구현하는 데 쓰여요. 상품과 주문 테이블이 있는데, 한 주문이 여러 상품을 담게 하고 싶다면(위 구조로는 불가능했죠) 이런 구조를 써요:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

마지막 테이블에서 primary key가 foreign key들과 겹친다는 점에 주목하세요.

ON DELETE 동작

foreign key 덕분에 어떤 상품과도 관련 없는 주문을 만들 수 없어요. 그런데 주문이 만들어진 뒤 참조되던 상품이 삭제되면 어떨까요? SQL은 이것도 처리하게 해줘요. 직관적인 옵션이 몇 가지 있죠: 참조되는 상품 삭제 금지, 주문도 함께 삭제, 또는 다른 처리.

기본 ON DELETE 동작은 ON DELETE NO ACTION이에요. 이건 지정하지 않아도 되고, 참조된 테이블의 삭제를 진행시켜도 된다는 뜻이에요. 다만 foreign key 제약 조건은 여전히 만족되어야 하므로 보통 오류가 나죠. NO ACTION 설정은 제약 검사가 트랜잭션 후반으로 연기될 수 있는데, 그 경우 다른 명령이 참조된 테이블에 적절한 행을 삽입하거나 참조 테이블의 정리된 행을 지우는 등으로 상황을 "고칠" 수 있게 해줘요.

RESTRICT는 NO ACTION보다 더 엄격해요. 참조된 행의 삭제를 막죠. RESTRICT는 검사를 트랜잭션 후반으로 연기하는 것을 허용하지 않아요.

CASCADE는 참조된 행이 삭제되면 그것을 참조하는 행들도 자동으로 삭제하도록 지정해요.

SET NULL과 SET DEFAULT라는 두 옵션도 있어요. 참조된 행이 삭제되면 참조 행의 참조 열이 각각 null 또는 기본값으로 설정돼요. 단 이들은 어떤 제약 조건도 지키지 않아도 되는 면제가 아니라는 점을 기억하세요. 예를 들어 SET DEFAULT를 지정했는데 기본값이 foreign key 제약 조건을 만족하지 못하면 연산이 실패해요.

올바른 ON DELETE 선택은 관련 테이블이 어떤 종류의 객체를 나타내는지에 달려 있어요. 참조 테이블이 참조 테이블이 나타내는 것의 구성 요소이고 독립적으로 존재할 수 없으면 CASCADE가 적절할 수 있어요. 두 테이블이 독립 객체라면 RESTRICT나 NO ACTION이 더 적절하죠. SET NULL이나 SET DEFAULT는 foreign key 관계가 선택적 정보를 나타내면 적절할 수 있어요.

SET NULL/DEFAULT의 열 목록

SET NULL과 SET DEFAULT는 어떤 열을 설정할지 지정하는 열 목록을 받을 수 있어요. 보통은 foreign key 제약 조건의 모든 열이 설정되지만, 특수한 경우 부분집합만 설정하는 게 유용해요. 예를 들어:

CREATE TABLE tenants (
    tenant_id integer PRIMARY KEY
);

CREATE TABLE users (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    user_id integer NOT NULL,
    PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE posts (
    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
    post_id integer NOT NULL,
    author_id integer,
    PRIMARY KEY (tenant_id, post_id),
    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);

열을 지정하지 않으면 foreign key가 tenant_id 열도 null로 설정하겠지만, 그 열은 여전히 primary key의 일부로 필요해요.

ON UPDATE 동작

ON DELETE와 유사하게 ON UPDATE도 있어요. 참조되는 열이 바뀔(갱신될) 때 호출되죠. 가능한 동작은 같지만, SET NULL과 SET DEFAULT에는 열 목록을 지정할 수 없어요. 이 경우 CASCADE는 참조 열의 갱신된 값이 참조 행에 복사된다는 뜻이에요.

ON UPDATE NO ACTION(기본값)과 ON UPDATE RESTRICT 사이에는 눈에 띄는 차이가 있어요. 전자는 업데이트가 진행되게 하고 foreign key 제약 조건을 업데이트 후 상태에 대해 검사해요. 후자는 업데이트 후 상태가 제약을 만족해도 업데이트 실행을 막아요. 참조된 행을 "다르지만 비교에선 같은" 값(예: 대소문자 무시 collation을 쓰는 문자열 타입의 다른 대소문자 변형)으로 갱신하는 것을 막아주죠.

null과 MATCH FULL

보통 참조 열 중 하나라도 null이면 참조 행은 foreign key 제약 조건을 만족하지 않아도 돼요. MATCH FULL을 foreign key 선언에 추가하면 참조 열이 전부 null일 때만 제약 준수를 피할 수 있어요(그래서 null과 non-null이 섞인 값은 MATCH FULL 제약을 반드시 실패시켜요). 참조 행이 foreign key 제약 조건을 회피할 수 없게 하려면 참조 열을 NOT NULL로 선언하세요.

인덱스

foreign key는 primary key이거나 unique 제약 조건을 이루거나, 비부분 unique 인덱스의 열들을 참조해야 해요. 그래서 참조되는 열은 항상 인덱스가 있어서 참조 행에 일치가 있는지 효율적으로 찾을 수 있어요. 참조 테이블에서 행을 DELETE하거나 참조 열을 UPDATE하면 참조 테이블을 스캔해 예전 값과 일치하는 행을 찾아야 하므로, 참조 열도 인덱스하는 게 좋은 경우가 많아요. 항상 필요한 건 아니고 인덱스 방법에 선택지가 많기 때문에, foreign key 제약 조건 선언이 참조 열에 인덱스를 자동으로 만들지는 않아요.

Exclusion 제약 조건

exclusion 제약 조건은, 지정된 열이나 표현식 두 행을 지정된 연산자로 비교할 때, 그 연산자 비교 중 적어도 하나는 false나 null을 반환하도록 보장해요. 문법은 이래요:

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

자세한 내용은 CREATE TABLE ... CONSTRAINT ... EXCLUDE를 참고하세요. exclusion 제약 조건을 추가하면 제약 조건 선언에 지정된 타입의 인덱스가 자동으로 생성돼요.

더 알아보기 (Learn more)

• Generated Columns
• 시스템 열 (System Columns)
• CREATE TABLE