제약과 무결성에 대해 알아보자 (feat. NULL, DEFAULT, CHECK)

안녕하세요, 끙정입니다.

 

오늘은 DB에서 아주 중요한 개념인 무결성에 대해서 알아보도록 하겠습니다.

또한 무결성을 위해서 제약이라는 방식이 따라붙습니다.

 

처음 들으면 이게 뭔 소린가 하시겠지만,

천천히 이해하면 크게 어렵지 않습니다.

 

자, 따라오시죠.

 

1. 무결성 관리

DB에서는 모든 데이터가 결함 없이 완벽한 상태를 무결성(Integrity)이라고 합니다.

DB가 이전의 단순한 정보 저장 방식과 다른 것은 바로 이런 무결성 덕분이라고도 할 수 있습니다.

 

예를 들면 아래와 같은 컴퓨터 부품을 파는 쇼핑몰의 주문 테이블이 있다고 가정해봅시다.

 

날짜	회원	제품	개수
2020-3-4	김유신	NVIDIA GEFORCE 3070	2
2020-3-10	최영	SAMSUNG DDR RAM 16G	32352
2045-3-2	알파고	DOSHIBA SSD 12ZB	-523
2020-3-5	허난설헌	NULL	3

 

레코드가 조금씩 이상한 것을 알 수 있습니다.

3만 개가 넘는 개수, -가 붙은 개수, 먼 미래의 날짜, NULL값으로 되어 있는 제품명까지. 

이러한 DB를 무결성이 깨졌다고 합니다.

그리고 이러한 무결성이 깨지지 않도록, 방어 체계를 갖추어야 합니다.

 

무결성은 적용 범위에 따라 아래의 세 가지로 구분합니다.

1) 칼럼 무결성: 컬럼 하나에 저장되는 원자적인 값을 점검하며 도메인 무결성이라고도 합니다. SET TYPE(타입 지정), NULL(널 허용 여부), CHECK(체크), SET DEFAULT(기본값) 등의 제약이 있습니다.

2) 엔터티 무결성: 레코드끼리 중복 값을 가지지 않도록 하여 유일한 식별자를 관리합니다. 예를 들어 주민등록번호가 같은 두 명이 존재하지 않아야 합니다. PRIMARY KEY(기본키)와  UNIQUE(유니크) 제약이 있습니다.

3) 참조 무결성: 테이블 간의 관계를 구성하는 키가 항상 유효하도록 관리하며 FOREIGN KEY(외래 키) 제약으로 관리합니다. 이쪽 테이블이 참조하는 정보가 저쪽 테이블에 반드시 존재해야 합니다.

 

다시 예를 들어 아래와 같은 쿼리를 날렸다고 가정해봅시다.

 

INSERT INTO tCity VALUES ('평양', '엄청 넓음', '꽤 많음', 'n', '조선인민민주주의공화국')

 

 

위 쿼리는 제약사항에 위배되기 때문에 에러가 뜨게 됩니다.

area는 TYPE을 int로 지정해두었기 때문에 문자열인 '엄청 넓음' 이 들어갈 수 없습니다.

popu도 마찬가지입니다.

이렇듯 DBMS는 무결성을 지키기 위한 방법으로 제약(Constraint)을 둡니다.

 

그렇다면 이러한 제약이 무엇이 있는지 하나씩 살펴보겠습니다.

 

2. NULL

NULL은 아무것도 입력되어 있지 않은 것이며,

알 수 없거나 결정되지 않은 특수한 상태를 의미합니다.

필드의 NULL 허용 속성은 NULL 상태가 존재하는지를 지정합니다.

반드시 입력해야 하는 필수 필드는 NULL을 허용해서는 안됩니다.

 

필드의 NULL 허용 속성은 테이블을 생성할 때 필드 속성 뒤에 지정합니다.

필드 선언 제일 뒤에 NULL이라고 지정하면 NULL값을 허용한다는 뜻이고,

NOT NULL이라고 적으면 NULL을 허용하지 않는다는 뜻입니다.

 

CREATE TABLE tCity
(
    name CHAR(10) PRIMARY KEY, /* 뒤에 나오지만 PK는 NULL을 허용하지 않는다. */
    area INT NULL, /* NULL이 허용된다. */
    popu INT NULL,
    metro CHAR(1) NOT NULL, /* NULL이 허용되지 않습니다. */
    region CHAR(6) NOT NULL
    
);

 

위처럼 테이블을 만들어주었을 때,

area, popu는 NULL 값이 허용되므로 값을 입력하지 않아도 괜찮지만,

name, metro, region은 NULL 값이 허용되지 않으므로 무조건 값을 입력해주어야 합니다.

 

3. DEFAULT

그러나 NULL 허용 속성은 데이터베이스의 성능을 저해하는 주범입니다.

항상 NULL 상태를 감안하여 필드 값이 존재하는지 점검해야 하고 보통의 값과는 다루는 방식이 달라 느릴 수밖에 없습니다.

 

따라서 NULL 허용 대신 DEFAULT(기본값)을 사용하는 것이 성능상 유리합니다.

기본값은 필드 값을 지정하지 않을 때 자동으로 입력할 값입니다.

수치형은 보통 0이 적당하고, 문자열은 비워 두거나 'N/A'를 많이 사용합니다.

 

CREATE TABLE tCity
(
    name CHAR(10) PRIMARY KEY,
    area INT NULL,
    popu INT NULL,
    metro CHAR(1) DEFAULT 'n' NOT NULL, /* DEFAULT 를 통해 기본값 'n'을 지정해준다. */
    region CHAR(6) NOT NULL
    
);

 

값을 괄호로 감싸 DEFAULT ('n')이라고 적어도 괜찮습니다.

중요한 것은 DEFAULT 키워드는 무조건 NULL 허용 속성 앞에 와야 합니다.

 

DEFAULT 값을 믿고 필드의 값을 비우고 입력을 하려면 아래와 같이 쿼리를 날려야 합니다.

 

/* metro 값이 비어 있지만 아래 쿼리는 입력이 된다. 필드 목록을 지정해주었기 때문이다. */
INSERT INTO tCity (name, area, popu, region) VALUES ('진주', 712, 34, '경상');

/* 위 쿼리와 같이 metro 값이 비었지만 에러가 뜬다. 필드 목록을 지정하지 않았기 때문이다. */
INSERT INTO tCity VALUES ('강릉', 1111, 22, '강원');

/* 위 쿼리를 제대로 입력시키려면 아래와 같이 DEFAULT 키워드를 넣어주어야 한다. */
INSERT INTO tCity VALUES ('강릉', 1111, 22, DEFAULT, '강원');

 

특정 레코드의 필드를 DEFAULT 값으로 바꿔주고 싶을 때도 있습니다.

그럴 때는 아래와 같이 쿼리를 날려 줍니다.

 

/* name이 '인천'인 레코드의 metro를 DEFAULT값으로 변경 */
UPDATE tCity SET metro = DEFAULT WHERE name = '인천'

 

4. CHECK

체크 제약은 필드의 값 종류를 제한합니다.

타입은 물리적인 형식을 점검하는데 비해 체크는 논리적인 값의 형식을 점검합니다.

 

CHECK 제약을 주는 방법은 필드 선언문에 CHECK 키워드와 함께 필드 값으로 가능한 값을 조건문으로 지정하는 것입니다.

이때 WHERE절의 조건을 지정하는 모든 문법을 다 쓸 수 있습니다.

 

CREATE TABLE tCheck
(
      gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'), /* OR 사용 */
      grade INT NULL CHECK (grade >= 1 AND grade <= 3), /* AND 사용 */
      origin CHAR(3) NULL CHECK(origin IN ('동', '서', '남', '북')), /* IN 사용 */
      name CHAR(10) NULL CHECK(name LIKE '김%'), /* LIKE 사용 */
      class INT NULL CHECK (class BETWEEN 1 AND 10) /* BETWEEN AND 사용 */
);

 

CHECK로 제약을 걸어두면 해당 필드 조건의 값이 아닌 다른 값이 입력되었을 때 에러가 발생합니다.

 

'노' 값은 '남', '여' 에 해당하지 않기 때문에 에러가 뜬다.

 

 

그만 알아보겠습니다.

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24