서론
PostgreSQL 1 | Basic 과 MySQL 1 | Basic 에서 간단하게 언급한 무결성에 관한 내용이다.
- 개체 무결성 | Entity Integrity | Primary Key 값이 고유하고 NULL 을 허용하지 않는다.
- 참조 무결성 | Refrential Integrity | Foreign Key 값이 NULL 이거나 참조된 테이블의 기본 키 값과 동일해야 한다.
- 범위 무결성 | Domain Integrity | Domain 타입을 사용하면 이 Domain 타입의 규칙을 따라야 한다.
개체 무결성, 즉 PK 에 대한 개념은 컬럼 값에 대한 제한자인 NOT NULL 과 UNIQUE 가 디폴트 제어자로 설정되어 있다고 할 수 있다. 따라서 별도의 설명을 하지 않고 후술되어 있는 컬럼 제한자 를 확인하면 될 것 같다.
일반적으로 PK 는 하나이지만, PRIMARY KEY (칼럼명 1, 칼럼명 2) 를 통해서 복수의 PK 를 만들 수도 있다. 용법에 대해서는 나중에 알아봐야겠다.
그리고 참조 무결성은 맨 마지막에 서술되어 있다.
범위 무결성
PostgreSQL 6 | Data Type 에서 언급하지 않은 데이터 타입이다.
예제 코드
0과 9 사이의 숫자만 받는 타입
아래와 같이 도메인을 만들 수 있다.
만약 어떠한 타입을 만들었고 이 리스트를 보고싶으면 cmd 창에서 help 를 입력하면
사용 가능한 커맨드라인 명령어 리스트를 확인할 수 있다.
도메인 타입 만들기
CREATE DOMAIN phone_number AS INTEGER CHECK (VALUE > 0 AND VALUE <9);
\dD[S+]
DROP DOMAIN phone_numeber;도메인 타입의 칼럼을 가지는 테이블 만들기
일반적으로 사용가능한 integer 과 같은 타입들이 대문자로도 사용할 수 있는 반면에
도메인 타입은 대소문자를 구분하므로 정확히 기입해야 한다.
CREATE TABLE phone_book (
phone_number phone_number
);테스트 하기
INSERT INTO phone_book VALUES(0);
INSERT INTO phone_book VALUES(1);
INSERT INTO phone_book VALUES(8);
INSERT INTO phone_book VALUES(9);
INSERT INTO phone_book VALUES(-1);
컬럼 값 제한자
컬럼값 제한자는 다음과 같은 것들이 있다.
1. NOT NULL | NULL 값 입력 제한
2. UNIQUE | 컬럼값의 중복 제한
3. ON DELETE SET NULL | 참조키 칼럼값 삭제가 되면, 해당 값을 참조하고 있는 것들도 다 삭제
4. ON DELETE SET DEFAULT | 참조키 칼럼값 삭제가 되면, 해당 값을 참조하고 있는 칼럼의 값이 디폴트 값으로 변경 ( 만약 별도의 제약조건으로 디폴트값을 변경했다면 그 값은 무조건 참조키 칼럼에 존재해야 한다.)
5. CHECK | 뒤에 나오는 식이 불리언 타입의 True 를 만족해야 한다.
NOT NULL
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test
CREATE TABLE test_table2(
test_serial SERIAL PRIMARY KEY,
test_name VARCHAR(15) NOT NULL
);
UNIQUE
단일 Unique 속성 선언
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test
CREATE TABLE test_table2(
test_serial SERIAL PRIMARY KEY,
test_name VARCHAR(15) UNIQUE
);
복수 Unique 선언
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test
CREATE TABLE test_table2(
test_serial SERIAL PRIMARY KEY,
test_name VARCHAR(15),
test_email VARCHAR(30),
UNIQUE (test_name, test_email)
);
ON DELETE SET NULL
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test
CREATE TABLE book (
book_id NUMERIC(5) NOT NULL PRIMARY KEY,
book_name VARCHAR(20) NOT NULL
);
INSERT INTO book VALUES
(00001,'국어'),(00002,'수학'),(00003,'영어');
CREATE TABLE tutor (
student_id NUMERIC(5) NOT NULL PRIMARY KEY,
student_name VARCHAR(20) NOT NULL,
student_birthday DATE,
book_id NUMERIC(5) REFERENCES book ON DELETE CASCADE
);
INSERT INTO tutor VALUES
(0001,'김수정','1996-01-01',0001),
(0002,'김희라','1995-01-01',0002),
(0003,'김미애','1994-01-01',0003);
SELECT * FROM book;
SELECT * FROM tutor;
DELETE FROM book
WHERE book_id=00002;
SELECT * FROM book;
SELECT * FROM tutor;
CHECK
DROP DATABASE order_info (
order_id INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(100),
price MONEY,
order_amount INTEGER CHECK (order_amount > 0)
);참조 무결성
일반적으로 참조 무결성에 대한 제약조건은 다음과 같다.
- 부모 테이블이 자식 테이블보다 먼저 생성되어야 한다.
- 부모 테이블에서 참조된 컬럼의 값만 자식 테이블에서 입력 가능하다.
- 참조되는 컬럼은 모두 프라이머리 키이거나 unique 제약 조건 형식이어야 한다.
- 참조되는 컬럼은 모두 프라이머리 키이거나 UNIQUE 제약 조건 형식이어야 한다.
FOREIGN KEY(GOOD)
예시
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test
CREATE TABLE test_table(
test_serial NUMERIC(2) NOT NULL PRIMARY KEY,
test_name VARCHAR(40) NOT NULL
);
INSERT INTO test_table VALUES
(01, 'first row'),
(02, 'second row'),
(03, 'third row');
CREATE TABLE test_parent(
test_serial NUMERIC(2) NOT NULL PRIMARY KEY,
test_name VARCHAR(40) NOT NULL,
test_validator NUMERIC(2) REFERENCES test_table
);
INSERT INTO test_parent VALUES (01,'first row in parent',01);
INSERT INTO test_parent VALUES (02,'first row in parent',02);
INSERT INTO test_parent VALUES (03,'first row in parent',03);
INSERT INTO test_parent VALUES (04,'first row in parent',04);
// 참조 테이블의 primary key 가 03 까지밖에 없으므로, 04를 참조하려고 하면 알아서 필터링이 된다.
