| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 5. 데이터 정의 | Fast Forward | Next |
데이터형은 테이블에 저장하는 데이터의 종류를 한정하기 위한 방법입니다. 그러나 많은 어플리케이션에서는 형태가 제공하는 제한에서는 정밀함이 부족합니다. 예를 들면, 제품의 가격이 들어가는 열에는 정수만을 받아들이도록 할 필요가 있습니다. 그러나 정수만을 받아들이는 표준 데이터형은 없습니다. 또한, 다른 열이나 행에 관련해 열데이터를 제한하는 경우도 있습니다. 예를 들면, 제품의 정보가 들어가는 테이블에서는 1개의 제품 번호에 대한 행이 하나여야만 합니다.
이러한 문제를 해결하기 위해, SQL에서는 열 및 테이블에 대한 제한을 정의할 수 있습니다. 제한에 의해 테이블 내의 데이터를 자유롭게 제어할 수 있습니다. 제한에 위반하는 데이터를 열에 저장하려고 하면, 에러가 납니다. 이는 디폴트 값으로서 정의된 값을 저장하는 경우에도 적용됩니다.
검사 제한은 가장 일반적인 제한의 종류입니다. 이것을 사용해, 특정한 열의 값이 Boolean(true-값)의 식을 채우도록 지정할 수 있습니다. 예를 들면, 제품 가격을 반드시 정수로 하려면 이하와 같이 합니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
이와 같이, 제한의 정의는 디폴트 값의 정의와 같게, 데이터형의 뒤에 옵니다. 디폴트치와 제한은 임의의 순서로 열거할 수 있습니다. 검사 제한의 구성은 CHECK키워드의 뒤에 계속되는 괄호로 둘러싸인 식입니다. 검사 제한식에는 제한되는 열을 포함할 필요가 있습니다. 그렇게 하지 않으면 제한은 그다지 의미가 없는 것이 됩니다.
제한에 개별적으로 이름을 붙일 수도 있습니다. 이름을 붙이는 것으로, 에러 메세지가 알기 쉬워지고, 변경하고 싶은 제한을 참조할 수 있게 됩니다. 구문은 이하와 같습니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
위와 같이, 이름 제한의 지정은CONSTRAINT키워드로 시작해 이것에 식별자, 제한 정의로 이어집니다. (이 방법으로 제한명을 지정하지 않는 경우는 시스템에 의해 이름을 붙일 수 있습니다).
검사 제한에서는 복수의 열을 참조할 수도 있습니다. 예를 들면, 보통 가격과 할인 가격을 저장하는 경우에, 반드시 할인 가격이 보통 가격보다 낮아지도록 하고 싶다고 합시다.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
최초의 2개의 제한은 위에서 설명한 대로입니다. 3번째의 제한에서는 새로운 구문을 사용하고 있습니다. 이것은 특정한 열에 부가되는 것이 아니라, 콤마로 단락지어진 열 리스트 내의 별개 항목으로서 나타납니다. 열 정의와 이러한 제한 정의는 임의의 순서로 열거할 수 있습니다.
최초의 2개의 제한을 열 제한이라고 말합니다. 이에 반해, 3번째의 제한은 열 정의와는 별개로 쓰여지므로, 테이블 제한이라고 말합니다. 열 제한을 테이블 제한으로서 쓸 수 있지만, 그 반대는 반드시 가능하지는 않습니다. 왜냐하면 열 제한은 제한과 관련된 열만을 참조하기 때문에입니다(PostgreSQL는 이 규칙을 강요하지 않습니다. 그러나 다른 데이터베이스 시스템과 함께 가동시키고 싶은 경우는 이 규칙에 따라 주세요). 위의 예는 이하와 같이 쓸 수도 있습니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
혹은 다음과 같이 할 수 있습니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
어떻게 할까는 기호의 문제입니다.
열 제한과 같이, 테이블 제한에 이름을 지정할 수 있습니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
검사 제한에서는 검사식이 true 또는 NULL 값일 경우, 조건이 만족되는 것을 설명해야 합니다. 대부분의 식은 어떠한 연산 항목이 NULL여도 NULL라고 평가되므로, 제한 대상의 열에서는 NULL 값을 예방하지 않습니다. 열이 NULL 값을 포함하지 않도록 하기 위해서, 다음 절에서 NULL이 아닌 사용할 수 있는 제한을 설명합니다.
not-null은 단순히, 열이 NULL값을 받지 않는다고 가정합니다. 구문 예는 이하와 같습니다.
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
not-null은 항상 열 제한으로서 기술됩니다. not-null은CHECK ( column_name IS NOT NULL) 검사 제한과 기능적으로 동등하지만, PostgreSQL에서는 명시적으로 not-null을 작성하는 편이 보다 효과적입니다. 이 방법으로 작성된 not-null에 명시적인 이름을 붙일 수 없는 것이 결점입니다.
물론, 1개의 열에 복수의 제한을 적용할 수도 있습니다. 그 때문에 차례로 제한을 쓸 수 있습니다.
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
순서는 관계 없습니다. 쓰여진 순서와 체크되는 순서는 반드시 같지는 않습니다.
NOT NULL제한에 대해, 반대되는 NULL제한이 있습니다. 이는 열이 NULL이 아니면 안된다는 것이 아닙니다. 그러한 제한은 의미가 없습니다. 이 제한은 열이 NULL이여도 괜찮다는 디폴트의 행동을 선택할 뿐입니다. NULL제한은 표준 SQL에는 존재하지 않으므로, 이식할 수 있는 어플리케이션에서는 사용하지 말아야 합니다. (이는 PostgreSQL와 다른 데이터베이스 시스템과의 호환성을 위해 추가된 기능에 지나지 않습니다). 무엇보다, 스크립트 파일 제한의 변환이 간단하다라고 말하는 이유로써 이 기능을 환영하는 유저도 있습니다. 예를 들면, 이하와 같이 시작할 수 있습니다.
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);
그런 다음, 원하는 곳에 NOT키워드를 삽입할 수 있습니다.
Tip: 대부분의 데이터베이스 설계에 대해, 열의 대다수를 NOT NULL로 마크해야 할 필요가 있습니다.
유일성 제한에 의해, 열이나 열 그룹에 포함되는 데이터가, 테이블 내의 모든 행에 관해서 유일하다는 것을 확실히 합니다. 구문은 다음과 같습니다.
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
열 제한의 경우의 구문은 이하와 같습니다.
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
테이블 제한으로서 쓰여진 경우는 다음과 같습니다.
유일성 제한이 열 그룹을 참조하는 경우, 각 열은 콤마로 단락지어 리스트됩니다.
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
이는 지정된 열 값의 편성이 테이블 전체에 유일한 것을 지정하고 있습니다. 그러나 열의 다른 한쪽이 유일할 필요는 없습니다(보통 유일하지 않습니다).
유일성 제한에는 이하와 같이 이름을 지정할 수도 있습니다.
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
일반적으로, 제한의 대상인 열에 대해 같은 값을 가지는 행은 테이블 내에 2행 이상일 경우 유일성 제한 위반이 됩니다. 그러나 이 비교에서는 2개의 NULL 값은 같다고 고려되지 않습니다. 즉, 유일성 제한이 있다고 해도, 제한 대상의 열이 적어도 1개에 NULL 값을 가지는 행을 복수 저장할 수 있다는 것입니다. 이 행동은 표준 SQL에 준거하고 있지만, 이 규칙에 따르지 않는 SQL 데이터베이스도 있습니다. 그러므로, 이식할 예정의 어플리케이션을 개발하려면 주의하십시요.
기술적으로 주 키 제한은 단순하게 유일성 제한과 not-null 제한을 조합한 것입니다. 즉, 다음의 2개의 테이블 정의는 같은 데이터를 받아들입니다.
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
);
주 키에서도 복수 열을 제한할 수가 있습니다. 구문은 유일성 제한과 유사합니다.
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
주 키는 열 또는 열의 그룹을, 테이블 내에서 행의 유일한 식별자로서 사용할 수 있는 것을 나타냅니다 (이는 주 키의 정의로부터 직접적인 결과입니다. 유일성 제한에서 NULL 값을 제외하지 않기 때문에, 유일한 식별자가 제공되지 않는 것을 주의하십시요). 이것은 문서화 및 클라이언트 어플리케이션의 양쪽 면에 도움이 됩니다. 예를 들면, 행 값의 변경이 가능한 GUI 어플리케이션에서 행을 유일하게 식별하기 위해서는 테이블의 주 키를 인지할 필요가 있습니다.
1개의 테이블에 포함할 수 있는 주 키의 최대 수는 1개입니다 (유일성 제한 및 not-null 제한에는 개수의 제한이 없습니다. 기능적으로는 같은 것이지만, 주 키로서 식별되는 제한은 1개입니다). RDB 이론에서는 모든 테이블에 주 키가 1개 필요하다고 지시합니다. 이 규칙은PostgreSQL에서는 강요하지 않지만, 대부분의 경우는 이에 따르도록 추천합니다.
외부 키 제한은 열(또는 열의 그룹)의 값이, 다른 테이블 행의 값과 일치해야 하는 것을 지정합니다. 이것에 의해 관련하는 2개 테이블의참조 무결성이 유지됩니다.
이미 여러번 예로 사용한 products 테이블에 대해 생각해 보겠습니다.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
또한, 이러한 제품에 대한 주문을 저장하는 테이블도 작성이 끝난 상태라고 합시다. 이 orders 테이블에는 실제로 존재하는 제품의 주문만을 저장하고 싶다고 생각하고 있습니다. 거기서, products 테이블을 참조하는 orders 테이블에 외부 키 제한을 정의합니다.
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
이것으로, products 테이블에 존재하지 않는 product_no항목을 사용해 주문을 작성할 수 없도록 합니다.
이러한 경우에, orders 테이블을참조하는테이블, product 테이블을참조되는테이블이라고 부릅니다. 비슷하게, 참조하는 열과 참조되는 열이 있습니다.
위의 커맨드는 다음과 같이 단축할 수도 있습니다.
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
열 리스트 내에 없기 때문에, 참조되는 테이블의 주 키가 참조 되는 열(복수가능)로서 사용됩니다.
외부 키에서도, 열의 그룹을 제한하거나 참조할 수 있습니다. 이것도 또한, 테이블 제한의 형식에서 기술할 필요가 있습니다. 이하는 설명을 위한 현실적이지 않은 예입니다.
CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );
물론, 제한되는 열의 수 및 형태는 참조되는 열의 수 및 형태와 일치해야 합니다.
외부 키에는 이하와 같이 이름을 지정할 수도 있습니다.
테이블에는 복수의 외부 키 제한을 포함할 수 있습니다. 이것은 테이블 간의 다대다 관계를 실행하기 위해서 사용됩니다. 예를 들면, 제품과 주문에 관한 각각의 테이블이 있는 경우에, 복수의 제품을 포함하는 주문을 하고 싶다고 합시다. (위의 예제 구조에서는 불가능합니다). 이 경우, 다음의 테이블 구조를 사용할 수 있습니다.
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)
);
마지막 테이블로, 주 키와 외부 키가 겹치고 있는 것을 주목해 주십시요.
외부 키가 제품과 관련지을 수 없는 주문의 작성을 허가하지 않는다는 것을 알고 있습니다. 그러나 만약 주문이 생성된 후에 이를 참조하는 제품이 삭제되면 어떨까요? SQL에서는 이러한 경우도 취급할 수 있습니다. 직감적으로, 몇 개의 옵션을 가질 수 있습니다.
참조되는 제품의 삭제를 허가하지 않는다
주문도 함께 삭제한다
그 밖에도 있습니까?
구체적인 예로서 위 예제의 다대다 관계에 다음의 방침을 실행해 봅시다. (order_items에 의해) 주문에 의해 참조된 상태로 제품을 제거한다면, 이러한 연산은 허락될 수 없습니다. 주문이 제거되면, 주문 항목도 제거됩니다.
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 ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
삭제의 제한 및 cascading은 가장 일반적인 2개의 옵션입니다. RESTRICT는 참조되는 행이 삭제되는 것을 막습니다. NO ACTION는 제한이 체크되었을 때, 참조하는 행이 여전히 존재하고 있는 것을 의미합니다. 이는 에러가 발생하게 됩니다. 이는 아무것도 지정하지 않는 경우는 디폴트 행동이 됩니다 (이러한 두가지 선택에서 근본적인 차이는 NO ACTION가 트랜젝션에서 검사를 나중으로 미룰 수 있는데 반해, RESTRICT는 그럴 수 없는 것입니다.) CASCADE는 참조되는 행이 삭제되었을 때, 그것을 참조하는 행(복수가능)도 같이 삭제되지 않으면 안 되는 것을 지정합니다. 그 밖에도 2개의 옵션이 있습니다. SET NULL와 SET DEFAULT입니다. 이것들은 참조되는 행이 삭제되었을 때, 참조하는 열이 각각 NULL나 디폴트로 설정되게 됩니다. 이것들은 제한을 보존하려는 것으로부터 면제하는 것이 아니라는 것을 주의하십시요. 예를 들면, 동작에SET DEFAULT를 지정했다고 해도, 디폴트 값이 외부 키 제한을 만족시키지 않는 경우는 조작이 실패합니다.
ON DELETE를 닮은 것으로, 참조되는 열이 변경(업데이트)되었을 때, 호출되는 ON UPDATE도 있습니다. 이것들을 실시할 수 있는 액션은 같습니다.
데이터의 업데이트 및 삭제에 대한 자세한 사항은Chapter 6을 참조해 주십시요.
마지막으로, 외부 키는 주 키나 유일성 제한 형식을 참조하는 열이어야 합니다. 외부 키가 유일성 제한을 참조하는 경우, NULL 값을 일치시킬수 있는 몇 가지 방법들이 있습니다. 이에 관해 레퍼런스 문서 CREATE TABLE 에 설명되어 있습니다.