Integrity Constraints

Integrity Constraint 는 데이터의 무결성을 유지하기 위한 방법이다. 이전 포스트에서 Integrity Constratins 를 설명했지만, SQL 쿼리로 적용하는 내용은 다루지 않았기 때문에 해당 내용을 정리하자.

Constraints on a Single Relation

단일한 테이블에 대해 데이터 무결성을 보장하기 위해서 우리는 다음과 같은 SQL 키워드들을 사용할 수 있다.

1. NOT NULL
2. PRIMARY KEY
3. UNIQUE
4. CHECK(P)

NOT NULL

NOT NULL 은 어떤 attribute 가 NULL 값을 가지는 것을 허용하지 않기 위해 사용된다.

CREATE TABLE student (
    name VARCHAR(20) NOT NULL,
    phone VARCHAR(15)
);

이렇게 테이블을 생성하게 되면, phone attribute의 값은 null을 허용하지만 name의 값은 null을 허용하지 않게된다.

UNQIUE

UNIQUE 는 어떤 attrubute 가 해당 attribute에 대해 중복된 값을 가지는 것을 허용하지 않게 하는 것을 말한다. 즉, 해당 attribute 를 candidate key로 삼는 것이다. candidate key는 유일성과 최소성을 만족하는 key를 말한다. 해당 attribute 하나를 통해 어떤 tuple을 식별할 수 있게되기 때문에, 유일성과 최소성을 만족한다. 하지만 candidate key는 null 값을 가질 수 있다.

CREATE TABLE student (
    name VARCHAR(20) NOT NULL,
    SSN VARCHAR UNIQUE,
    phone VARCHAR(15)
);

이렇게 테이블을 만들면, SSN은 Candidate key 가 되고, 해당 attribute에는 null을 제외한 중복값을 허락하지 않는다.

CHECK

CHECK 는 어떤 attribute의 Domain을 더 명확하게 특정해주어서 해당 Domain 이 아닌 값이 들어올 수 없게 한다.

CREATE TABLE student (
    name VARCHAR(20) NOT NULL,
    SSN VARCHAR UNIQUE,
    phone VARCHAR(15),
    nationality VARCHAR(20),
    CHECK (nationality IN ('Korea', 'China', 'Japan'))
);

이렇게 테이블을 만들면 nationality attribute 에 대해서는 모든 tuple이 Korea, China, Japan 세 값 중에 하나를 가지고 있어야 하고, 그 이외의 다른 값들은 모두 거절된다.

Referential Integrity

여러 테이블을 관계시키거나 함께 사용할 때, 우리는 Foreign Key 를 사용하게 된다. Foreign Key의 정의는 다음과 같이 할 수 있다.

CREATE TABLE student (
    name VARCHAR(20) NOT NULL,
    SSN VARCHAR UNIQUE,
    phone VARCHAR(15),
    nationality VARCHAR(20),
    CHECK (nationality IN ('Korea', 'China', 'Japan'))
    FOREIGN KEY (SSN) REFERENCES Korea
);

위와 같이 테이블을 만들게 되면, student 테이블에 있는 SSN 이라는 attribute는 Foreign Key 가 되어서 Korea 테이블에 있는 동일한 이름의 attribute를 참조하게 된다. 즉, student 테이블의 SSN이라는 값은 항상 Korea 테이블에 있는 SSN 컬럼에 존재해야한다.

ON DELETE/UPDATE CASCADE

Foreign key 를 생성하면서 데이터 무결성을 위해 위화 같은 조건을 추가해줄 수 있다.

CREATE TABLE student (
    name VARCHAR(20) NOT NULL,
    SSN VARCHAR UNIQUE,
    phone VARCHAR(15),
    nationality VARCHAR(20),
    CHECK (nationality IN ('Korea', 'China', 'Japan'))
    FOREIGN KEY (SSN) REFERENCES Korea
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

위 예시에서 만들었던 테이블에 CASCADE 를 추가해주었다. 이렇게 만들어진 student 테이블의 SSN 값은 Korean 테이블의 SSN 값을 참조하고 있다.

그런데 만약 Korea 테이블의 SSN 에 있던 값 중에 하나가 삭제되었다고 해보자. 이렇게 되면 참조의 대상이 되는 Korea 테아블에서는 해당 데이터가 없어졌는데, 참조하는 student 테이블에는 데이터가 남아있을 수도 있는 상황이 생긴다. Foreign Key 의 값이 항상 참조의 대상이 되는 attribute에 있어야 한다는 조건이 깨지는 것이다.

따라서 DELETE CASCADE 조건을 추가해주면, Korea 테이블에서 참조받는 값을 가진 tuple이 삭제되었을 때, 자동으로 student 테이블에 있는 해당 값을 가진 tuple을 함께 삭제해주게 된다.

비슷한 맥학으로 UPDATE CASCADE 조건을 추가하게 되면, 참조받는 테이블에 있는 값이 새로운 값으로 갱신될 때, 참조를 하고있는 student 테이블에 있는 값도 함께 갱신된다.

이렇게 하면 데이터 무결성을 유지할 수 있을 것이다.

Self Referencing Table Issue

어떤 테이블은 자기 자신을 참조하고 있을 수도 있다. 이 경우에는 새로운 tuple 들을 테이블에 넣는 것이 데이터 무결성을 쉽게 꺠뜨릴 수도 있다. 이런 시나리오에서는 다음과 같은 작업을 수행함으로 데이터 무결성을 유지할 수 있다.

1. 참조할 데이터가 이미 존재하도록 한다.
2. 모든 foreign key 데이터를 null 로 초기화해두고 모든 tuple을 삽입한 이후에 업데이트한다.
3. Foreign key가 없는 상태로 테이블을 만들고 Foreign key 를 나중에 지정해준다.