데이터 유형은 테이블에 저장할 수 있는 데이터의 종류를 제한하는 방법입니다. 그러나 많은 응용 프로그램에서 제공하는 제약 조건이 너무 조잡합니다. 예를 들어 제품 가격이 포함된 열은 양수 값만 허용해야 합니다. 그러나 양수만 허용하는 표준 데이터 유형은 없습니다. 또 다른 문제는 다른 열 또는 행과 관련하여 열 데이터를 제한할 수 있다는 것입니다. 예를 들어, 제품 정보가 포함된 테이블에는 각 제품 번호에 대해 하나의 행만 있어야 합니다.
이를 위해 SQL을 사용하여 열과 테이블에 대한 제약 조건을 정의할 수 있습니다. 제약 조건을 사용하면 테이블의 데이터를 원하는 만큼 제어할 수 있습니다. 사용자가 제약 조건을 위반하는 열에 데이터를 저장하려고 하면 오류가 발생합니다. 이는 값이 기본값 정의에서 나온 경우에도 적용됩니다.
5.4.1. 제약 조건 확인
검사 제약 조건은 가장 일반적인 제약 조건 유형입니다. 특정 열의 값이 부울(truth-value) 표현식을 충족해야 함을 지정할 수 있습니다. 예를 들어 긍정적인 제품 가격을 요구하려면 다음을 사용할 수 있습니다.
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자 CHECK(가격 > 0)
);
보시다시피 제약 조건 정의는 기본값 정의와 마찬가지로 데이터 유형 뒤에 옵니다. 기본값과 제약 조건은 임의의 순서로 나열할 수 있습니다. 검사 제약 조건은 키워드 CHECK뒤에 괄호로 묶인 표현식으로 구성됩니다. 검사 제약 조건 표현식은 이렇게 제약된 열을 포함해야 합니다. 그렇지 않으면 제약 조건이 너무 의미가 없습니다.
제약 조건에 별도의 이름을 지정할 수도 있습니다. 이렇게 하면 오류 메시지가 명확해지고 변경해야 할 때 제약 조건을 참조할 수 있습니다. 구문은 다음과 같습니다.
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자 CONSTRAINT positive_price CHECK(가격 > 0)
);
따라서 명명된 제약 조건을 지정하려면 키워드 CONSTRAINT다음에 식별자를 사용하고 제약 조건 정의를 사용합니다. (이 방법으로 제약 조건 이름을 지정하지 않으면 시스템에서 이름을 선택합니다.)
검사 제약 조건은 여러 열을 참조할 수도 있습니다. 정상 가격과 할인 가격을 저장하고 할인 가격이 정상 가격보다 낮은지 확인하려고 한다고 가정해 보겠습니다.
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자 CHECK(가격 > 0),
Discounted_price 숫자 CHECK(discounted_price > 0),
확인(가격 > 할인된 가격)
);
처음 두 제약 조건은 친숙해 보일 것입니다. 세 번째는 새로운 구문을 사용합니다. 특정 열에 첨부되지 않고 대신 쉼표로 구분된 열 목록에 별도의 항목으로 나타납니다. 열 정의와 이러한 제약 조건 정의는 혼합된 순서로 나열될 수 있습니다.
처음 두 제약 조건은 열 제약 조건이고 세 번째 제약 조건은 하나의 열 정의와 별도로 작성되기 때문에 테이블 제약 조건이라고 합니다. 열 제약 조건은 테이블 제약 조건으로 작성할 수도 있지만 열 제약 조건은 연결된 열만 참조해야 하므로 반대가 반드시 가능한 것은 아닙니다. ( PostgreSQL 은 이 규칙을 적용하지 않지만, 테이블 정의가 다른 데이터베이스 시스템과 작동하도록 하려면 이 규칙을 따라야 합니다.) 위의 예는 다음과 같이 작성할 수도 있습니다.
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자,
확인(가격 > 0),
할인된 가격 숫자,
확인(할인된 가격 > 0),
확인(가격 > 할인된 가격)
);
또는:
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자 CHECK(가격 > 0),
할인된 가격 숫자,
확인(discounted_price > 0 AND 가격 > discounted_price)
);
그것은 취향의 문제입니다.
열 제약 조건과 동일한 방식으로 테이블 제약 조건에 이름을 할당할 수 있습니다.
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자,
확인(가격 > 0),
할인된 가격 숫자,
확인(할인된 가격 > 0),
CONSTRAINT valid_discount CHECK(가격 > 할인된 가격)
);
검사 식이 true 또는 null 값으로 평가되는 경우 검사 제약 조건이 충족된다는 점에 유의해야 합니다. 피연산자가 null인 경우 대부분의 식은 null 값으로 평가되므로 제한된 열에서 null 값을 방지하지 않습니다. 열에 null 값이 포함되지 않도록 하기 위해 다음 섹션에서 설명하는 not-null 제약 조건을 사용할 수 있습니다.
메모
PostgreSQLCHECK 은 확인 중인 새 행 또는 업데이트된 행 이외의 테이블 데이터를 참조하는 제약 조건을 지원하지 않습니다 . 이 규칙을 위반 하는 CHECK제약 조건은 간단한 테스트에서 작동하는 것처럼 보일 수 있지만 제약 조건 조건이 false인 상태에 데이터베이스가 도달하지 않는다고 보장할 수는 없습니다(관련된 다른 행의 후속 변경으로 인해). 이로 인해 데이터베이스 덤프 및 다시 로드가 실패합니다. 제약 조건을 충족하는 순서로 행이 로드되지 않기 때문에 전체 데이터베이스 상태가 제약 조건과 일치하는 경우에도 다시 로드가 실패할 수 있습니다. 가능하면 , 또는 제약 조건을 사용 UNIQUE하여 EXCLUDE행 FOREIGN KEY및 테이블 간 제한을 표현합니다.
지속적으로 유지되는 일관성 보장이 아니라 행 삽입 시 다른 행에 대한 일회성 검사를 원하는 경우 사용자 지정 트리거 를 사용하여 이를 구현할 수 있습니다. (이 접근 방식은 pg_dump 가 데이터를 다시 로드할 때까지 트리거를 다시 설치하지 않으므로 덤프/재로드 중에 검사가 시행되지 않기 때문에 덤프/재로드 문제를 방지합니다.)
메모
PostgreSQL 은 CHECK제약 조건의 조건이 변경 불가능하다고 가정합니다. 즉, 동일한 입력 행에 대해 항상 동일한 결과를 제공합니다. 이 가정은 CHECK행이 삽입되거나 업데이트될 때만 검사 제약 조건을 정당화하고 다른 시간에는 그렇지 않습니다. (다른 테이블 데이터를 참조하지 않는다는 위의 경고는 실제로 이 제한의 특별한 경우입니다.)
CHECK이 가정을 깨는 일반적인 방법의 예는 식에서 사용자 정의 함수를 참조한 다음 해당 함수의 동작을 변경하는 것입니다. PostgreSQL 은 이를 허용하지 않지만 테이블에 현재 CHECK제약 조건을 위반하는 행이 있는지 여부를 인식하지 못합니다. 그러면 후속 데이터베이스 덤프 및 다시 로드가 실패합니다. 이러한 변경을 처리하는 데 권장되는 방법은 제약 조건을 삭제하고( 를 사용하여 ALTER TABLE) 함수 정의를 조정하고 제약 조건을 다시 추가하여 모든 테이블 행에 대해 다시 확인하는 것입니다.
5.4.2. Null이 아닌 제약 조건
null이 아닌 제약 조건은 단순히 열이 null 값을 가정하지 않아야 함을 지정합니다. 구문 예:
CREATE TABLE 제품(
product_no 정수 NOT NULL ,
이름 텍스트 NOT NULL ,
가격 숫자
);
null이 아닌 제약 조건은 항상 열 제약 조건으로 작성됩니다. not-null 제약 조건은 기능적으로 check 제약 조건을 만드는 것과 동일 하지만 PostgreSQL 에서는 명시적 not-null 제약 조건을 만드는 것이 더 효율적입니다. 단점은 이러한 방식으로 생성된 null이 아닌 제약 조건에 명시적 이름을 지정할 수 없다는 것입니다.CHECK (column_name IS NOT NULL)
물론 열에는 둘 이상의 제약 조건이 있을 수 있습니다. 제약 조건을 하나씩 작성하십시오.
CREATE TABLE 제품(
product_no 정수 NOT NULL,
이름 텍스트 NOT NULL,
가격 숫자 NOT NULL CHECK(가격 > 0)
);
순서는 중요하지 않습니다. 제약 조건이 검사되는 순서를 반드시 결정하지는 않습니다.
NOT NULL제약 조건에는 반대의 제약 조건이 NULL있습니다. 이것은 열이 null이어야 함을 의미하지 않으며, 이는 확실히 쓸모가 없습니다. 대신 열이 null일 수 있는 기본 동작을 선택합니다. 제약 조건 은 NULLSQL 표준에 없으며 이식 가능한 응용 프로그램에서 사용하면 안 됩니다. (일부 다른 데이터베이스 시스템과 호환되도록 PostgreSQL 에만 추가되었습니다 .) 그러나 일부 사용자는 스크립트 파일에서 제약 조건을 쉽게 토글할 수 있기 때문에 좋아합니다. 예를 들어 다음과 같이 시작할 수 있습니다.
CREATE TABLE 제품(
product_no 정수 NULL,
이름 텍스트 NULL,
가격 숫자 NULL
);
NOT원하는 곳에 키워드 를 삽입하세요 .
팁
대부분의 데이터베이스 디자인에서 대부분의 열은 null이 아닌 것으로 표시되어야 합니다.
5.4.3. 고유 제약 조건
고유 제약 조건은 열 또는 열 그룹에 포함된 데이터가 테이블의 모든 행에서 고유하도록 합니다. 구문은 다음과 같습니다.
CREATE TABLE 제품(
product_no 정수 UNIQUE ,
이름 텍스트,
가격 숫자
);
열 제약 조건으로 작성된 경우:
CREATE TABLE 제품(
product_no 정수,
이름 텍스트,
가격 숫자,
고유(product_no)
);
테이블 제약 조건으로 작성될 때.
열 그룹에 대한 고유 제약 조건을 정의하려면 열 이름을 쉼표로 구분하여 테이블 제약 조건으로 작성하십시오.
CREATE TABLE 예(
정수,
b 정수,
c 정수,
고유 (a, c)
);
이는 표시된 열의 값 조합이 전체 테이블에서 고유함을 지정하지만 열 중 하나가 고유할 필요는 없으며 일반적으로 고유하지도 않습니다.
일반적인 방법으로 고유 제약 조건에 고유한 이름을 할당할 수 있습니다.
CREATE TABLE 제품(
product_no 정수 CONSTRAINT must_be_different UNIQUE,
이름 텍스트,
가격 숫자
);
고유 제약 조건을 추가하면 제약 조건에 나열된 열 또는 열 그룹에 고유한 B-트리 인덱스가 자동으로 생성됩니다. 일부 행에만 적용되는 고유성 제한은 고유 제약 조건으로 작성할 수 없지만 고유 부분 인덱스 를 생성하여 이러한 제한을 적용할 수 있습니다.
일반적으로 제약 조건에 포함된 모든 열의 값이 동일한 테이블에 둘 이상의 행이 있으면 고유 제약 조건이 위반됩니다. 그러나 이 비교에서는 두 개의 null 값이 동일한 것으로 간주되지 않습니다. 즉, 고유 제약 조건이 있는 경우에도 제약 조건이 있는 열 중 하나 이상에 null 값을 포함하는 중복 행을 저장할 수 있습니다. 이 동작은 SQL 표준을 따르지만 다른 SQL 데이터베이스는 이 규칙을 따르지 않을 수 있다고 들었습니다. 따라서 이식 가능한 애플리케이션을 개발할 때는 주의해야 합니다.
5.4.4. 기본 키
기본 키 제약 조건은 열 또는 열 그룹을 테이블의 행에 대한 고유 식별자로 사용할 수 있음을 나타냅니다. 이를 위해서는 값이 고유하고 null이 아니어야 합니다. 따라서 다음 두 테이블 정의는 동일한 데이터를 허용합니다.
CREATE TABLE 제품(
product_no 정수 UNIQUE NOT NULL,
이름 텍스트,
가격 숫자
);
CREATE TABLE 제품(
product_no 정수 기본 키 ,
이름 텍스트,
가격 숫자
);
기본 키는 둘 이상의 열에 걸쳐 있을 수 있습니다. 구문은 고유 제약 조건과 유사합니다.
CREATE TABLE 예(
정수,
b 정수,
c 정수,
기본 키(a, c)
);
기본 키를 추가하면 기본 키에 나열된 열 또는 열 그룹에 고유한 B-트리 인덱스가 자동으로 생성되고 열이 표시 NOT NULL됩니다.
테이블은 최대 하나의 기본 키를 가질 수 있습니다. (기능적으로 거의 동일하지만 하나만 기본 키로 식별될 수 있는 고유하고 null이 아닌 제약 조건이 여러 개 있을 수 있습니다.) 관계형 데이터베이스 이론에 따르면 모든 테이블에는 기본 키가 있어야 합니다. 이 규칙은 PostgreSQL 에 의해 시행되지 않지만 일반적으로 따르는 것이 가장 좋습니다.
기본 키는 문서화 목적과 클라이언트 응용 프로그램 모두에 유용합니다. 예를 들어, 행 값 수정을 허용하는 GUI 애플리케이션은 행을 고유하게 식별할 수 있으려면 테이블의 기본 키를 알아야 합니다. 또한 데이터베이스 시스템이 기본 키가 선언된 경우 기본 키를 사용하는 다양한 방법이 있습니다. 예를 들어 기본 키는 해당 테이블을 참조하는 외래 키에 대한 기본 대상 열을 정의합니다.
5.4.5. 외래 키
외래 키 제약 조건은 열(또는 열 그룹)의 값이 다른 테이블의 일부 행에 나타나는 값과 일치해야 함을 지정합니다. 이것이 두 개의 관련 테이블 간의 참조 무결성 을 유지한다고 말합니다 .
이미 여러 번 사용한 제품 테이블이 있다고 가정해 보겠습니다.
CREATE TABLE 제품(
product_no 정수 기본 키,
이름 텍스트,
가격 숫자
);
또한 해당 제품의 주문을 저장하는 테이블이 있다고 가정해 보겠습니다. 주문 테이블에 실제로 존재하는 제품의 주문만 포함되도록 하고 싶습니다. 그래서 우리는 제품 테이블을 참조하는 주문 테이블에 외래 키 제약 조건을 정의합니다.
CREATE TABLE 주문(
order_id 정수 기본 키,
product_no 정수 REFERENCES 제품(product_no) ,
수량 정수
);
product_no이제 제품 테이블에 표시되지 않는 NULL이 아닌 항목으로 주문을 생성 할 수 없습니다.
이 상황에서 주문 테이블은 참조 테이블이고 제품 테이블은 참조 테이블이라고 합니다. 마찬가지로 참조 및 참조 열이 있습니다.
위의 명령을 다음과 같이 줄일 수도 있습니다.
CREATE TABLE 주문(
order_id 정수 기본 키,
product_no 정수 REFERENCES 제품 ,
수량 정수
);
열 목록이 없으면 참조된 테이블의 기본 키가 참조된 열로 사용되기 때문입니다.
일반적인 방법으로 외래 키 제약 조건에 고유한 이름을 할당할 수 있습니다.
외래 키는 열 그룹을 제한하고 참조할 수도 있습니다. 평소와 같이 테이블 제약 조건 형식으로 작성해야 합니다. 다음은 인위적인 구문 예입니다.
테이블 생성 t1(
정수 기본 키,
b 정수,
c 정수,
외래 키(b, c) 참조 other_table(c1, c2)
);
물론 제약이 있는 열의 수와 유형은 참조된 열의 수와 유형과 일치해야 합니다.
때로는 외래 키 제약 조건의 " 다른 테이블 " 이 동일한 테이블인 것이 유용합니다. 이를 자체 참조 외래 키라고 합니다. 예를 들어, 테이블의 행이 트리 구조의 노드를 나타내도록 하려면 다음과 같이 작성할 수 있습니다.
CREATE TABLE 트리(
node_id 정수 기본 키,
parent_id 정수 REFERENCES 트리,
이름 텍스트,
...
);
최상위 노드에는 NULL 이 있는 parent_id반면 NULL이 아닌 parent_id항목은 테이블의 유효한 행을 참조하도록 제한됩니다.
테이블에는 둘 이상의 외래 키 제약 조건이 있을 수 있습니다. 이것은 테이블 간의 다대다 관계를 구현하는 데 사용됩니다. 제품 및 주문에 대한 테이블이 있지만 이제 하나의 주문에 많은 제품이 포함될 수 있도록 허용하려고 합니다(위 구조에서는 허용하지 않음). 다음 테이블 구조를 사용할 수 있습니다.
CREATE TABLE 제품(
product_no 정수 기본 키,
이름 텍스트,
가격 숫자
);
CREATE TABLE 주문(
order_id 정수 기본 키,
배송 주소 텍스트,
...
);
CREATE TABLE 주문_항목(
product_no integer REFERENCES 제품,
order_id 정수 REFERENCES 주문,
수량 정수,
기본 키(product_no, order_id)
);
기본 키는 마지막 테이블의 외래 키와 겹칩니다.
외래 키는 제품과 관련이 없는 주문 생성을 허용하지 않는다는 것을 알고 있습니다. 그러나 제품을 참조하는 주문이 생성된 후 제품이 제거되면 어떻게 됩니까? SQL을 사용하면 이를 처리할 수도 있습니다. 직관적으로 몇 가지 옵션이 있습니다.
- 참조된 제품 삭제 금지
- 주문도 삭제
- 다른 것?
이를 설명하기 위해 위의 다대다 관계 예에 대해 다음 정책을 구현해 보겠습니다. 누군가 주문에서 여전히 참조하는 제품을 제거하려는 경우( 를 통해 order_items) 이를 허용하지 않습니다. 누군가가 주문을 제거하면 주문 항목도 제거됩니다.
CREATE TABLE 제품(
product_no 정수 기본 키,
이름 텍스트,
가격 숫자
);
CREATE TABLE 주문(
order_id 정수 기본 키,
배송 주소 텍스트,
...
);
CREATE TABLE 주문_항목(
product_no integer REFERENCES 제품 ON DELETE RESTRICT ,
order_id 정수 REFERENCES 주문 ON DELETE CASCADE ,
수량 정수,
기본 키(product_no, order_id)
);
제한 및 계단식 삭제는 두 가지 가장 일반적인 옵션입니다. RESTRICT참조된 행의 삭제를 방지합니다. NO ACTION제약 조건이 검사될 때 참조하는 행이 여전히 존재하는 경우 오류가 발생함을 의미합니다. 아무 것도 지정하지 않으면 이것이 기본 동작입니다. (이 두 선택의 본질적인 차이점은 NO ACTION트랜잭션의 나중까지 검사를 연기할 수 있지만 RESTRICT그렇지 않다는 점입니다.) CASCADE참조된 행이 삭제될 때 이를 참조하는 행도 자동으로 삭제되어야 함을 지정합니다. 두 가지 다른 옵션이 있습니다 SET NULL.SET DEFAULT. 이로 인해 참조 행이 삭제될 때 참조 행의 참조 열이 각각 null 또는 기본값으로 설정됩니다. 이러한 이유로 제약 조건을 준수하지 않아도 된다는 점에 유의하십시오. 예를 들어, 작업이 지정 SET DEFAULT하지만 기본값이 외래 키 제약 조건을 충족하지 않는 경우 작업은 실패합니다.
참조된 열이 변경(업데이트)될 때 호출되는 것과 ON DELETE유사 합니다 . ON UPDATE가능한 조치는 동일합니다. 이 경우 CASCADE참조된 열의 업데이트된 값을 참조하는 행으로 복사해야 함을 의미합니다.
일반적으로 참조하는 열이 null인 경우 참조하는 행은 외래 키 제약 조건을 충족할 필요가 없습니다. 외래 키 선언에 가 추가 되면 MATCH FULL참조하는 행은 모든 참조 열이 null인 경우에만 제약 조건을 충족하는 이스케이프됩니다(따라서 null 값과 null이 아닌 값의 혼합은 MATCH FULL제약 조건 실패를 보장합니다). 참조 행이 외래 키 제약 조건 충족을 방지할 수 없도록 하려면 참조 열을 로 선언하십시오 NOT NULL.
외래 키는 기본 키이거나 고유한 제약 조건을 형성하는 열을 참조해야 합니다. 이는 참조된 열에 항상 인덱스(기본 키 또는 고유 제약 조건의 기초가 되는 인덱스)가 있음을 의미합니다. 따라서 참조 행에 일치 항목이 있는지 확인하는 것이 효율적입니다. 참조 된 DELETE테이블의 행 또는 UPDATE참조된 열의 경우 이전 값과 일치하는 행에 대한 참조 테이블의 스캔이 필요하므로 참조하는 열도 색인화하는 것이 좋습니다. 이것이 항상 필요한 것은 아니며 인덱싱 방법에 대해 많은 선택이 가능하기 때문에 외래 키 제약 조건의 선언은 참조하는 열에 인덱스를 자동으로 생성하지 않습니다.
데이터 업데이트 및 삭제에 대한 자세한 내용은 6장 에 있습니다. 또한 CREATE TABLE 에 대한 참조 문서에서 외래 키 제약 조건 구문에 대한 설명을 참조하십시오 .
5.4.6. 제외 제약
제외 제약 조건은 지정된 연산자를 사용하여 지정된 열 또는 표현식에서 두 행을 비교하는 경우 이러한 연산자 비교 중 적어도 하나가 false 또는 null을 반환하도록 합니다. 구문은 다음과 같습니다.
CREATE TABLE 서클(
c 원,
요점을 사용하여 제외(c와 &&)
);
자세한 내용은 참조 CREATE TABLE ... CONSTRAINT ... EXCLUDE하십시오.
제외 제약 조건을 추가하면 제약 조건 선언에 지정된 유형의 인덱스가 자동으로 생성됩니다.