7-1.제약

1.무결성 관리

세상에 완벽한 것이 없듯이 데이터베이스나 그 안에 저장된 데이터도 항상 완벽할 수 없다. 모든 데이터가 결함없이 완벽한 상태를 무결성(Integrity)이라고 한다. 데이터베이스 관리 시스템은 무결성을 지키기 위해 최선의 노력을 하며 그래서 이전의 단순한 정보 저장 방식과 차원이 다르다.

시스템이 완벽해도 무결성이 깨지는 경우가 종종 있다. 실수나 에러로 잘못된 데이터를 입력할 수 있고 불가항력적인 요인으로 멀쩡하던 데이터가 파괴되기도 한다. 무결성이 깨지면 데이터를 사용하는 응용 프로그램도 제대로 돌아갈 리 없다. 컴퓨터 부품을 파는 쇼핑몰의 주문 테이블을 살펴보자.

날짜	회원	제품	개수
2020-3-4	김한슬	하이낵스 메모리 DDR6 64G	2
2020-3-9	홍길동	알지 노트북 킬로그램17	32767
2042-12-25	터미네이터	ATT 그래픽 카드	-8242
2020-4-1	임꺽정	NULL	3

첫 번째 레코드만 정상이고 이후의 레코드는 뭔가 의심스럽다. 홍길동의 노트북 32767개는 정상 주문으로 보기 어렵고 터미네이터 회원은 주문 날짜가 2042년인데다 주문 개수도 음수이다. 임꺽정이는 주문한 제품의 이름을 입력하지 않았다. 한마디로 개판이다.

무결성이 깨지는 원인은 다양하다. 컴퓨터는 실수하지 않지만 전압 급변으로 디스크가 기계적으로 손상되거나 네트워크 불안정으로 데이터가 손실되는 경우는 가끔 있다. 가장 큰 원인은 사람이라는 불완전한 존재의 실수이다. 고의건 실수건 조작이 미숙하면 어쩔 수 없이 오류가 발생한다.

프로그램도 결국은 사람이 만든 것이다 보니 완전하지 않아 버그에 의해 데이터가 깨지기도 한다. 그렇다고 속수무책으로 당할수만은 없으니 발생 가능한 모든 오류에 대처하는 방어 체계를 갖추어야 한다. 데이터를 처리하는 단계별로 서버측과 클라이언트측이 각자의 무결성 관리 정책을 제공한다.

클라이언트측에서는 입력할 정보에 꼭 맞는 라디오 버튼, 리스트 박스 등의 컨트롤로 오입력을 원천 차단한다. 값의 논리적 의미까지 점검해 보고 정상값 입력시까지 재입력을 요구하여 유효한 값만 서버로 보낸다. 자바, C#등의 언어로 섬세하고 복잡한 처리를 할 수 있지만 클라이언트가 무거워지는 부작용이 있고 규칙 변경시 수정 및 배포가 번거롭다.

서버측의 무결성 처리는 DBMS가 규칙을 기억하고 있다가 비정상 데이터의 입력을 거부하는 식이다. 중앙에서 규칙을 일괄 처리하여 클라이언트의 부담을 덜어 주고 규칙 수정시 배포할 필요가 없다. 그러나 속성으로 규칙을 선언하는 형태여서 단순한 점검만 가능하며 섬세한 처리는 한계가 있다. 적용 범위에 따라 세 가지로 구분한다.

l 컬럼 무결성 : 컬럼 하나에 저장되는 원자적인 값을 점검하며 도메인 무결성이라고도 한다. 타입 지정, 널 허용 여부, 체크, 기본값 등의 제약이 있다.

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

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

테이블의 각 요소에 규칙을 적용하여 데이터와 관계의 무결성을 관리한다. 클라이언트와 서버의 무결성 관리 정책은 장단점이 있고 상호 보완적인 관계여서 상황에 따라 적절히 분담하고 협조적으로 동작해야 한다. 서버쪽에서 하기 쉬운 처리는 서버가 하고 서버가 하기 어려운 처리는 클라이언트가 분담한다.

우리는 DBMS를 학습하고 있는 중이므로 서버측 무결성에 대해 알아 본다. 무결성을 유지하는 가장 기본적인 수단은 타입이며 데이터의 물리적인 포맷을 강제한다. 다음 문장은 딱 봐도 에러이다.

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

인구나 면적은 수치형이서 123 같은 숫자만 가능하며 문자열을 저장할 수 없다. 설사 타입이 맞더라도 지정한 길이를 초과해서는 안된다. 타입은 데이터의 물리적인 형태만 지정할 뿐 논리적인 값까지는 점검하지는 못한다. 음수나 너무 큰 수도 수치형 필드에 잘 저장된다.

그래서 논리적인 값까지 점검할 수 있는 장치가 필요하다. DBMS는 주로 제약을 사용한다. 제약(Constraint)은 조건을 위반하는 데이터를 방지하여 완전성을 보장하는 규칙이다. 제약이 없으면 아무 데이터나 자유롭게 입력할 수 있지만 무결성이 쉽게 깨진다.

테이블 디자인창의 필드 속성을 보면 이름과 타입 외에 널 허용 여부와 기본값 등을 설정하는 란이 있고 기본키인 PK를 설정할 수 있다. 이 편집창에서 필드의 입력값 형태를 미리 지정해 두는 것이 바로 제약이며 이 규칙에 맞는 데이터만 입력받는다.

쿼리문에서는 NULL, NOT NULL, PRIMARY KEY 같은 속성으로 제약을 지정한다. 테이블을 만들 때 지정하는 제약은 간단한 규칙만 가능하지만 선언만 해 두면 적용은 DBMS가 알아서 처리한다는 면에서 편리하고 효율적이다. 여기서는 각 제약의 의미와 효과에 대해 순서대로 실습해 보자.

2.NULL 허용

NULL은 아무것도 입력되어 있지 않은 것이며 알수 없거나 결정되지 않은 특수한 상태를 의미한다. 필드의 NULL 허용 속성은 NULL 상태가 존재하는지를 지정한다. 반드시 입력해야 하는 필수 필드는 NULL을 허용해서는 안되며 없어도 그만인 옵션 필드는 NULL을 허용해도 상관없다.

회원 정보의 경우 아이디, 이름, 비밀번호, 이메일 등은 회원 관리를 위한 필수 정보여서 반드시 입력해야 한다. 그러나 홈페이지, 주소, 전화번호는 없는 사람도 있고 공개하기를 꺼리는 사람도 있어 필수 정보는 아니다. 웹 사이트의 회원 가입 페이지는 두 부류의 정보를 분명히 구분한다.

굵게 표시된 정보는 필수이며 생략시 회원 가입을 거부한다. 반면 옵션 정보는 밝히기 싫으면 빈칸으로 남겨두어도 가입할 수 있다. NULL 허용 속성을 설정하지 않은 필드를 누락하면 레코드 삽입을 금지하여 회원 테이블의 무결성을 지킨다.

필드의 NULL 허용 속성은 테이블을 생성할 때 필드 속성 뒤에 지정한다. 필드 선언 제일 뒤에 NULL이라고 지정하면 NULL값을 허용한다는 뜻이고 NOT NULL이라고 적으면 NULL을 허용하지 않는다는 뜻이다. 상수 NULL과 좀 헷갈리는데 Nullable, Not Nullable이라고 이해하면 된다. tCity 테이블의 생성 스크립트를 보자.

name, metro, region 필드는 PRIMARY KEY 또는 NOT NULL 속성이 있어 NULL을 허용하지 않음을 지정한다. 이름이나 소속이 없는 도시는 정보로서의 가치가 없다. 반면 area와 popu 필드는 NULL 속성으로 지정되어 있어 생략 가능하다. 인구나 면적을 모르거나 굳이 알 필요 없다면 비워 놓을 수 있다.

INSERT 명령으로 레코드를 삽입할 때 NOT NULL로 지정한 필드는 반드시 입력해야 한다. 필수 정보 중 하나라도 빼 먹으면 정상적인 정보가 아니라고 판단하여 레코드 삽입을 거부한다. 그러나 NULL로 지정한 필드는 생략해도 상관없다. 다음 두 쿼리문은 정상 실행된다.

INSERT INTO tCity (name, popu, metro, region) VALUES ('울산', 114, 'y', '경상');

INSERT INTO tCity (name, metro, region) VALUES ('삼척', 'n', '강원');

울산은 면적을 모르는 상태여서 area 필드는 빼고 입력했다. 잘 입력되며 생략한 area는 NULL이 된다. 면적을 모른다고 울산의 정보 자체가 무효한 것은 아니며 정보가 없다는 것도 하나의 정보이다. 삼척은 인구, 면적을 다 생략해도 잘 삽입된다. 그러나 다음 두 쿼리문은 에러이다.

INSERT INTO tCity (area, popu, metro, region) VALUES (456, 123, 'n', '충청');

INSERT INTO tCity (name, area, popu) VALUES ('의정부', 456,123);

충청도에 있는 어느 도시인 모양인데 이름이 없다. 제일 중요한 이름이 누락되어 도대체 어느 도시의 정보인지 알 수 없으며 써 먹을 데도 없다. 의정부는 이름은 있지만 소속과 광역시 여부가 없어 역시 무효한 정보이며 DBMS는 이런 무효한 레코드의 삽입을 거부한다.

생략 가능한 옵션 필드인가 아니면 반드시 입력해야 하는 필수 필드인가에 따라 선언문 끝에 NULL 또는 NOT NULL이라고 명시한다. 그렇다면 NULL 허용 여부를 생략해 버리면 어떻게 될까? 다음 테이블을 생성해 보자.

CREATE TABLE tNullable

(

name CHAR(10) NOT NULL,

age INT

);

name은 필수 필드임이 분명하다. 그러나 age는 정수형이라는 것만 밝혔을 뿐 NULL 허용 여부에 대한 지정이 없다. NULL 허용 여부를 생략하면 DBMS의 디폴트 허용 여부를 따른다. 다음 쿼리문을 실행해 보자.

INSERT INTO tNullable (name, age) VALUES ('흥부', 36);

INSERT INTO tNullable (name) VALUES ('놀부');

INSERT INTO tNullable (age) VALUES (44);

모든 필드값을 다 입력한 흥부는 항상 잘 삽입된다. 이름만 입력하고 나이를 생략한 놀부는 DBMS의 설정에 따라 입력될 수도 있고 아닐 수도 있다. 대부분의 DBMS는 NULL 허용이 디폴트여서 이상 없이 삽입될 확률이 높다. 이름을 생략하고 나이 44세만 입력하면 당연히 에러 처리된다.

NULL 허용 속성의 디폴트는 DBMS마다 다르며 데이터베이스나 연결 수준에서 변경할 수도 있다. 그러나 설정에 영향을 받는 명령은 일관성이 없어 위험하다. 손가락 좀 편해 볼려고 속성을 생략하지 말고 필드 선언문 끝에 항상 NULL, NOT NULL을 분명히 명시하는 것이 바람직하다.

3.기본값

NULL 허용 속성은 데이터베이스의 성능을 저해하는 주범이다. 항상 NULL 상태를 감안하여 필드값이 존재하는지 점검해야 하고 보통의 값과는 다루는 방식이 달라 느릴 수밖에 없다. 응용 프로그램도 NULL을 항상 고려해야 하는 부담이 있다.

NULL 허용 대신 기본값(DEFAULT)을 사용하는 것이 성능상 유리하다. 기본값은 필드값을 지정하지 않을 때 자동으로 입력할 값이다. 보통 무난한 값을 지정하는데 수치형은 0이 적당하고 문자열은 비워 두거나 'N/A' 등을 많이 사용한다.

대부분의 경우 일정한 값이되 가끔 예외가 있는 필드에 기본값을 적용한다. 도시중 광역시는 몇 개 되지 않으며 대부분 지역에 소속된다. 이럴 때 metro 필드 속성에 DEFAULT 키워드와 함께 디폴트값을 'n'으로 지정한다.

CREATE TABLE tCityDefault

(

name CHAR(10) PRIMARY KEY,

area INT NULL ,

popu INT NULL ,

metro CHAR(1) DEFAULT 'n' NOT NULL,

region CHAR(6) NOT NULL

);

값을 괄호로 감싸 DEFAULT('n')이라고 적어도 된다. DEFAULT 키워드는 NULL 허용 속성보다 앞에 와야 한다. SQL Server와 MariaDB는 순서를 강제하지 않지만 오라클은 순서를 바꾸면 에러 처리해 버린다.

새로운 레코드를 삽입할 때 metro 필드를 지정하지 않으면 자동으로 'n'을 적용한다. 기본값은 입력하지 않을 때만 적용하는 것이다. 값을 직접 지정하면 기본값을 무시하고 지정한 값을 적용한다. 다음 두 명령을 실행해 보자.

INSERT INTO tCityDefault (name, area, popu, region) VALUES ('진주', 712, 34, '경상');

INSERT INTO tCityDefault (name, area, popu, metro, region) VALUES ('인천', 1063, 295, 'y', '경기');

진주시는 면적, 인구, 지역만 지정하고 광역시 여부는 생략했다. 필드 목록에 metro가 아예 없는데 이 경우 디폴트가 적용되어 광역시가 아닌 것으로 삽입한다. 인천시는 모든 필드의 값을 다 제공하여 광역시로 등록하였다.

쿼리문 내에서 DEFAULT 키워드는 현재 설정된 기본값이다. 기본값 설정도 편집 가능해서 테이블 생성시와 달라질 수 있는데 DEFAULT 키워드는 항상 현재 설정된 기본값을 읽어와 적용한다. 이 키워드는 필드 목록을 생략할 때 유용하다.

INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, '강원'); -- 에러

INSERT INTO tCityDefault VALUES ('강릉', 1111, 22, DEFAULT, '강원'); -- 정상 실행

필드 목록을 생략하면 값 목록이 완전해야 한다. metro에 기본값이 있더라도 값 목록에 이 자리를 비워 두면 안된다. 값 목록을 완전히 적되 기본값을 적용할 필드값에 DEFAULT라고 적는다. 기본값으로 변경할 때도 DEFAULT 키워드를 사용한다. 다음 명령은 인천의 metro 필드를 기본값인 'n'으로 변경한다.

UPDATE tCity_Default SET metro = DEFAULT WHERE name = '인천'

기본값의 유무와 NULL 허용 여부는 완전히 별개의 속성임을 주의하자. 생략시 자동 적용되는 기본값이 있으면 항상 NULL은 아닐 것이라고 오해할 수도 있다. 그러나 기본값이 지정되어 있더라도 NULL을 직접 입력할 수는 있고 UPDATE 명령으로 NULL로 바꿀 수도 있다. 기본값은 생략시 적용할 값일 뿐이지 NULL 허용 여부까지 통제하는 것은 아니다.

연습 문제

1.직원 테이블의 각 필드에 기본값을 적용하여 tStaffDefault 테이블을 생성하라. 부서는 영업부, 직급은 수습, 초봉은 280, 성취도는 1.0의 기본값을 적용한다.

4.체크

체크 제약은 필드의 값 종류를 제한한다. 모든 속성은 유의미한 범위가 있고 상식적으로 가능한 값과 그렇지 않은 값이 있다. 예를 들어 도시가 아무리 거대해도 인구 100억을 넘길 수는 없고 면적이 음수가 될 수는 없다. 체크 제약은 이런 무의미한 값을 걸러낸다.

타입은 물리적인 형식을 점검하는데 비해 체크는 논리적인 값의 형식을 점검한다. 필드 선언문에 CHECK 키워드와 함께 필드값으로 가능한 값을 조건문으로 지정한다. WHERE 절의 조건을 지정하는 모든 문법을 다 쓸 수 있다. 테스트를 위해 간단한 테이블을 만들어 보자.

CREATE TABLE tCheckTest

(

gender CHAR(3) NULL CHECK(gender = '남' OR gender = '여'),

grade INT NULL CHECK (grade >= 1 AND grade <= 3),

origin CHAR(3) NULL CHECK(origin IN ('동','서','남','북')),

name CHAR(10) NULL CHECK(name LIKE '김%')

);

각 필드에 조건문으로 체크 제약을 지정했다. 성별을 저장하는 gender 필드는 '남', 아니면 '여'만 가능하다. CHECK 조건문에 gender가 둘 중 하나임을 명시하여 이 두 값 이외에는 입력을 금지한다.

grade는 중고등학교의 학년을 표현하는데 1~3까지만 유효하다. 초등학교라면 6까지, 대학교라면 4까지 범위를 늘리면 된다. 일정 범위내일 때는 최소값, 최대값을 지정하는 대신 BETWEEN AND 조건문이 편리하다.

grade INT NULL CHECK (grade BETWEEN 1 AND 3),

origin 필드는 방위를 나타내는데 동서남북 넷 중 하나만 가능하다. 여러 개의 임의값 중 하나를 지정할 때는 IN 연산자가 편리하다. 부분 문자열을 점검할 때는 LIKE 연산자를 사용한다. name 필드는 성씨가 김씨인 경우만 받아들인다.

모든 필드에 제약을 걸어 두었으므로 이 규칙에 맞는 값만 허가한다. 다음 쿼리문은 모두 체크 제약 조건에 맞는 값을 입력하여 잘 실행된다. 삽입한 값을 보면 모두 체크 제약과 일치한다.

INSERT INTO tCheckTest (gender) VALUES ('여');

INSERT INTO tCheckTest (grade) VALUES (1);

INSERT INTO tCheckTest (origin) VALUES ('동');

INSERT INTO tCheckTest (name) VALUES ('김좌진');

그러나 다음 명령은 모두 제약 조건과 충돌했다는 에러 메시지가 출력되며 삽입을 거부한다. 삽입한 값을 보면 이유를 금방 알 수 있다.

INSERT INTO tCheckTest (gender) VALUES ('노');

INSERT INTO tCheckTest (grade) VALUES (0);

INSERT INTO tCheckTest (origin) VALUES ('중');

INSERT INTO tCheckTest (name) VALUES ('청산리');

남자도 여자도 아닌 '노'는 성별이 될 수 없고 중고등학교에 0학년이나 4학년은 존재하지 않는다. 방향은 동서남북 중 하나만 가능하며 이름은 김씨여야 한다. 삽입할 때 뿐만 아니라 UPDATE할 때도 체크 제약 조건을 점검한다.

UPDATE tCheckTest SET grade = 4 WHERE grade IS NOT NULL;

4학년은 체크 조건에 위배되어 아무 레코드도 갱신하지 않는다. 도시 정보 테이블인 tCity에도 지역명에 제약을 걸어 두면 엉뚱한 지역을 입력하는 것을 방지한다.

region CHAR(6) NOT NULL CHECK (region IN ('경기', '충청', '강원', '경상', '전라', '제주'))

region 필드는 반드시 체크 제약 조건에 지정한 지역 중의 하나여야 한다. 이 제약이 있는 상태에서 다음 문장은 에러 처리되며 삽입을 거부한다. '우산'이라는 지역명을 인정하지 않는다.

INSERT INTO tCity3 VALUES ('울릉',72,1,'n','우산');

서버는 레코드를 삽입, 변경할 때마다 제약 조건을 점검하며 클라이언트에서 무슨 짓을 하더라도 이 제약 조건을 어길 수 없다. 따라서 tCity에 있는 모든 도시는 6개 지역 중 하나에 소속됨을 보장할 수 있다.

연습 문제

2.직원 테이블의 각 필드에 제약 조건을 설정하여 부서는 영업부, 총무부, 인사과 중 하나만, 성별은 남 아니면 여로 제한하고 월급은 0보다 크다는 조건을 설정하라.