서론

PostgreSQL 1 | Basic 과 MySQL 1 | Basic 에서 간단하게 언급한 무결성에 관한 내용이다.

1. 개체 무결성 | Entity Integrity | Primary Key 값이 고유하고 NULL 을 허용하지 않는다.
2. 참조 무결성 | Refrential Integrity | Foreign Key 값이 NULL 이거나 참조된 테이블의 기본 키 값과 동일해야 한다.
3. 범위 무결성 | 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)
);

---

참조 무결성

일반적으로 참조 무결성에 대한 제약조건은 다음과 같다.

1. 부모 테이블이 자식 테이블보다 먼저 생성되어야 한다.
2. 부모 테이블에서 참조된 컬럼의 값만 자식 테이블에서 입력 가능하다.
3. 참조되는 컬럼은 모두 프라이머리 키이거나 unique 제약 조건 형식이어야 한다.
4. 참조되는 컬럼은 모두 프라이머리 키이거나 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를 참조하려고 하면 알아서 필터링이 된다.