CREATE DOMAIN — 새 도메인 정의
CREATE DOMAIN이름[ AS ]자료형[ COLLATE문자정렬규칙] [ DEFAULT표현식] [제약조건[ ... ] ]제약조건자리에는: [ CONSTRAINT제약조건이름] { NOT NULL | NULL | CHECK (표현식) }
CREATE DOMAIN 명령은 새 도메인을 만든다.
도메인이란 기본 자료형의 새로운 정의로 그 값 범위를 제한하는 제약 조건을
선택적으로 추가한 것이다.
이 도메인을 만드는 사용자가 도메인 소유주가 된다.
스키마 이름을 포함하면, 해당 도메인은 그 스키마 안에
만들어 진다. (예, CREATE DOMAIN myschema.mydomain ...)
스키마를 지정하지 않으면, 현재 스키마 안에 만들어진다.
도메인 이름은 해당 스키마와 기반 자료형을 함께 따져 그 스키마 안에서
유일해야 한다.
도메인 개체는 자료 관리용으로 입력되는 자료 값을 유효성을 엄격하게 제한하고자 할 때 유용하게 사용된다. 예를 들어, 여러 테이블에서 이메인 주소를 저장하는 칼럼이 있을 때, 그 칼럼을 문자열 자료형으로 지정하고, 그 모든 칼럼들에 체크 제약 조건을 추가해서 자료 유효성을 보장할 수 있다. 이 때, 하나의 도메인을 만들면서 체크 제약 조건을 해당 도메인에 지정하고, 그 도메인을 이메일 칼럼의 자료형으로 사용하면, 보다 일관된 자료 유효성을 보장한다.
도메인을 만드려면, 그 기반 자료형에 대해서,
USAGE 권한이 있어야 한다.
이름만들고자 하는 도메인 이름(스키마 이름 포함).
자료형해당 도메인의 기반 자료형 이름. 배열 자료형도 가능하다.
문자정렬규칙
해당 도메인의 문자 정렬 규칙. 지정하지 않으면,
이 도메인의 문자 정렬 규칙은 기반 자료형의 그것을 따른다.
COLLATE 옵션을 사용하면, 그 기반 자료형도
이 문자 정렬 규칙을 사용할 수 있어야 한다.
DEFAULT 표현식해당 도메인의 기본값을 지정하는 구문. 이 기본값을 지정하는 표현식은 자유롭다. (단 서브 쿼리는 허용하지 않음) 이 표현식으로 만들어지는 값의 자료형은 기반 자료형과 같아야 한다. 이 옵션을 사용하지 않으면 해당 도메인의 기본값은 null이다.
이 도메인을 사용하는 칼럼에 특정한 값을 지정하지 않으면, 이 옵션으로 지정한 기본값이 해당 칼럼 값으로 사용된다. 하지만, 어떤 칼럼에서 그 칼럼 고유 기본값을 지정하면, 이 도메인의 기본값보다 우선적으로 사용된다. 특정 칼럼의 기본값을 지정하지 않았다면, 도메인 기본값이 그 도메인의 기반 자료형 기본값보다 우선적으로 사용된다.
CONSTRAINT 제약조건선택적으로 지정하는 제약 조건의 이름. 지정하지 않으면, 서버가 자동으로 만든 이름을 사용한다.
NOT NULL해당 도메인 값으로 null 값을 사용하지 못하도록 지정한다. (아래 참고도 살펴 볼 것)
NULL해당 도메인 값으로 null 값을 사용하도록 지정한다. 이것은 기본값이다.
비표준 SQL 데이터베이스와 호환하기 위해 있는 옵션이다. 새 응용 프로그램에서는 사용하지 않는 것이 좋다.
CHECK (표현식)CHECK 옵션은 해당 도메인의
자료 무결성 검사, 또는 자료 유효성 검사를 지정한다.
여기서 사용하는 표현식의 결과는 불리언 값(참, 거짓, 알수없음)이여야 한다.
이 표현식에서 입력되는 값은 VALUE 라는 키워드를
사용한다.
이 표현의 결과값으로 TRUE 또는 UNKNOWN이면 검사를 통과한 것으로 간주한다.
FALSE면, 오류로 처리한다. 즉 해당 도메인 값으로 쓸 수 없다는 뜻이다.
현재 버전에서는 CHECK 표현식에는
서브쿼리를 사용할 수 없으며, VALUE 외 다른 변수값을
대상으로 검사할 수도 없다.
하나 이상의 CHECK 제약 조건을 지정한다면,
그 제약 조건의 이름 알파벳 순으로 검사를 진행한다.
(PostgreSQL 9.5 이전 버전에서는
CHECK 제약 조건의 검사 순서를 정하지 않았다.)
도메인 제약 조건으로 NOT NULL 제약 조건은
도메인으로 변환 될 때 검사 한다. 이럼이도 불구하고
명목상 도메인 유형의 열에 null 값이 올 수 있다.
예를 들어, 이런 일은 outer 조인에서 일어난다.
outer 조인의 결과는 도메인인데, 해당 자료가 없으면
결국 그 값은 null이 될 수 밖에 없다.
또 다른 경우는 다음과 같이,
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
스칼라 서브 셀렉트 값이 null 인 경우에도 이런 문제가 생길 수 있다. 이 경우 domcol 도메인에 not null 제약 조건이 있음에도 불구하고, null 값으로 insert 작업은 성공한다.
null 값은 어떠한 자료형에서도 그 값으로 사용할 수 있다는 SQL 기본 사상 때문에, 이런 문제를 피할 방법은 많이 어렵다. 이 문제를 피할 가장 좋은 방법은 not null 제약 조건은 테이블을 만들 때 해당 도메인을 사용하는 칼럼의 제약 조건으로 지정하는 것이다.
PostgreSQL assumes that
CHECK constraints' conditions are immutable, that is,
they will always give the same result for the same input value. This
assumption is what justifies examining CHECK
constraints only when a value is first converted to be of a domain type,
and not at other times. (This is essentially the same as the treatment
of table CHECK constraints, as described in
5.4.1절.)
An example of a common way to break this assumption is to reference a
user-defined function in a CHECK expression, and then
change the behavior of that
function. PostgreSQL does not disallow that,
but it will not notice if there are stored values of the domain type that
now violate the CHECK constraint. That would cause a
subsequent database dump and reload to fail. The recommended way to
handle such a change is to drop the constraint (using ALTER
DOMAIN), adjust the function definition, and re-add the
constraint, thereby rechecking it against stored data.
다음 예제는 us_postal_code 도메인을 만드는 예제다.
이 도메인을 이용해서
미국 우편 번호 자료를 저장하는 테이블도 함께 만든다.
정규식을 이용해서 입력되는 값이 타당한지 확인한다:
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL PRIMARY KEY,
street1 TEXT NOT NULL,
street2 TEXT,
street3 TEXT,
city TEXT NOT NULL,
postal us_postal_code NOT NULL
);
CREATE DOMAIN 구문은 표준 SQL 규약을 준수한다.