DB Design시 고려해야 할 제약성(Constraint) 정의

<출처: 엔코아 - 열린기술광장>

PURPOSE

RDBMS의 물리적인 데이터베이스 설계시 필요한 각종 제약성 정의 (Integrity Constraints)에 대하여 정확한 의미를 전달하고자 한다.

SCOPE & APPLICATION

RDBMS 에서는 서로 독립적이면서 데이터의 중복을 최소화하면서도 필요시 언제든지 서로 관계 연산자를 위용하여 정보의 연결을 가능하게 하여 새로운 정보를 창출하게 하는 위력을 가지고 있다. 또한 DB설계시 정의된 설계 사상에 대하여 DBMS Level에서 사전 정의를 할 수 있으며 이로 인하여 데이터의 일관성 및 일치성 그리고 무결성을 응용 프로그램의 추가 부담이 없이 보장 받을 수 있다.

그러나, 실제 고객사의 상황을 접하면 성능 또는 User Interface 불편 등을 이유로 하여 제대로 DBMS의 효율적인 기능을 활용하지 못하고 비정상적인 데이터들이 누적되고 있는 상황을 많이 접하게 된다.

여기서는 RDBMS를 이용한 DB설계시 고려할 수 있는 각종 Constraint에 대한 현실 활용 이해를 높이고 상황에 다른 명확한 적용지침을 기술토록 한다.

KEY IDEA

(KEY WORD : Referential, Constraint, Integrity, 무결성, 제약, 참조, DB Design)

SUPPOSITION

DESCRIPTION

• RDBMS 에서 언급하는 대표적인 사전 제약정의 (Pre-Define Constraints)는 참조 무결성(Referential Integrity) , 속성 무결성(Attribute Integrity) , 엔터티 무결성(Entity Integrity) 그리고 사용자정의 무결성(User Defined Integrity)과 같이 대표적인 네가지 무결성 정의를 사용할 수 있다.

• 그럼, 다음 [그림1]의 사례 Instance를 이용하여 대표적인 네가지 형태의 무결성 정의에 대하여 각각 상세히 살펴보자

                      

[그림 1]

• 엔터티 무결성(Entity Integrity)

√기본키(Primary Key)를 구성하는 모든 속성은 반드시 값을 가져야 한다. (not null)

√기본키(Primary Key)는 유일성을 보장해 주는 최소한의 집합이어야 한다. (minimal set of attributes)

√상기 제약 정의의 대표적인 Constraints는 PRIMARY KEY, UNIQUE, NOT NULL constraints가 해당 된다.

√즉, 엔터티 무결성이란 엔터티의 자격 검증을 할 수 있는지를 엄밀히 검토해 보면 명확해 진다. 실제 현실에서는 PK도 존재하지 않는 테이블을 사용하고 있는 경우도 다반사라는 것을 여러분 중에서도 경험자가 많을 것이다.

√ 하나의 엔터티가 물리적인 테이블로서 존재의 의미를 부여받기 위해서는 반드시 그 테이블에 존재하는 수많은 Row(Tuple) 중에서 유일한 Row(Tuple)를 인식할 수 있는 식별자가 있어야 된다는 것은 이미 데이터 모델링 단계의 엔터티 자격 검증에서 이루어졌을 것이며 이러한 엔터티 자격의 원칙이 물리적인 DB Design단계에서는 바로 PK 정의가 된다.

√고로, 식별자가 되기 위해서는 절대 모르는 값(Null)이 식별자가 될 수 없음은 당연한 이치이며,

√데이터 모델링 단계에서는 엔터티가 태어나기 위한 부모가 명확하게 정의되어 의미상의 주어가 식별자가 되었다면 물리적인 측면을 고려하여 최소한의 식별자를 구성하기 위한 속성 검토 역시 설계자가 노력하여야 되는 것도 당연한 이치이다.

√ 만일 A라는 엔터티의 식별자(UID)가 고객코드+상품코드+계약순번 으로 구성된다고 가정해 보자. 그리고 이러한 A라는 엔터티는 업무적으로 자료발생의 중심이 되는 메인 엔터티라면 또다시 수 많은 자식 엔터티를 탄생하게 되어 손자, 증손자 등의 후손 엔터티를 갖게 될 경우에는 직접종속 속성으로 PK를 상속받아 구성할 경우에는 손자, 증손자 대 에서는 PK구성 속성은 엄청나게 많은 속성들이 복합구성으로 필요로 하게될 것이다.

√ 이러한 문제를 미연에 예방하기 위해서 인공식별자(얼굴마담,Artificial UID)를 데이터 모델링 단계에서 고려할 수 있으며, 데이터 모델링 단계에서 지정하지 못할 경우에는 물리적인 DB Design 단계에서 최소의 속성 모임으로 PK를 지정하게 하는 것이다. 이것이 바로 Minimal Set Of Attributes 정의이다.

√유일한 식별자 지정은 테이블의 수많은 컬럼을 조합시킨다면 당연히 PK 자격이 될 수 있으나, 진정으로 임의의 Row를 인식시킬 수 있는 최소한 컬럼으로 정의하여야 된다는 의미이다.

√상기 [그림1] 예제에서 엔터티 무결성을 정의한 DEPT Table을 위한 DDL Script는 다음과 같다.

Create Table DEPT
(deptno number not null primary key,
dname varchar(20),
loc varchar(20))

• 참조무결성 (Referential Integrity)

√외부키(Foreign Key)는 반드시 존재하고 있는 기본키(Primary Key)와 연결되거나 NULL 이어야 한다

√상기 제약 정의의 대표적인 Constraints는 Reference constraint 가 해당 된다.

√즉, RDBMS는 모든 엔터티들이 관계(Relation)를 가지고 설계되어 서로 독립적인 정보들만으로 데이터의 중복을 억제할 수 있는 기본 사상이 바로 관계(Relation)이다.

√임의의 엔터티는 관계(Relation)를 통하여 정의되어 있을 경우에는 해당 관계의 1쪽(부모:참조되는쪽) 식별자 속성이 관계 명칭으로 M쪽(자식:참조하는쪽) 물리적인 테이블에 속성(컬럼)으로 생성되게 된다.

√이렇게 관계에 의하여 생성된 컬럼은 반드시 부모가 존재해야 만이 생성될 자격이 있으며 이러한 원리를 정의하는 DBMS Constraint가 바로 Referential Constraint이다.

√고로, 관계가 아직 정해지지 않고 Row(Tuple)가 생성된 경우 즉, 아직 관계를 모르는 경우에는 값이 Null이어야 하며 관계가 생성될 경우에는 반드시 존재하는 부모의 식별자가 컬럼 값으로 사용되어야 한다.

√ 이렇게 RI(Referential Integrity) 정의가 되어 있는 상황에서는 부모(참조되는쪽) 테이블의 임의의 Row (tuple)를 삭제할 경우에는 자식(참조하는쪽)들에서 RI가 지정되어 있다면 함부로 자식이 있는 부모가 사라질 수 없도록 DBMS는 자료의 참조 무결성을 보장하기 위하여 체크 및 검증을 내부적으로 보장하게 한다.

√이러한 RI 정의가 명확하게 되어 있을 경우에는 부모가 없는 자식 정보가 태어나거나, 자식이 있음에도 불구하고 부모의 정보가 사라지게 되는 참조무결성 위험요소를 극복할 수 있다.

√ 그러나, 현실 Project에서는 이 뛰어난 DBMS능력을 배제하고자 한다. 이유는 초기단계에서의 Data Migration 및 개발 프로그램 테스트시에 정확한 Data의 관계를 체크하여 줌으로 인하여 오히려 짜증을 내게 되며 다량의 초기 정보 구축시에는 부모 정보의 정확한 구축이 되어야 자식정보 구축이 가능하기 때문에 시간이 부족한 상황에서 병렬로 진행하고자 이를 모두 무시하고(Disabled) 개발을 진행하게 된다.

√이러한 상황은 충분히 이해를 할 수 있으나, 이러한 무방비책을 운영환경까지 고수하는 것은 안될 말이다. 즉, Migration시 쓰는 기법은 그 단계에서만 효율적이라는 예기이다.

√ 그동안의 IT 시스템의 주 관심은 자료의 축적 그리고 업무처리에 중심을 둔 시스템 구축이었으나 최근의 환경은 유행처럼 번지고 있는 DataWare House , DB Marketing이다. 이러한 DW의 성공 여부는 바로 정확한 정보의 숙성된 축적정보가 핵심이며 이를 위해서 DW 프로젝트에서 얼마나 많은 노고를 ETT에 쏟아야만 하는가 ? 또 그 엄청난 노력에도 불구하고 얻는 효과가 미흡하다는 결론이 당연시 되는 이유는 바로 기본에 충실하지 못했던 과거의 업보가 아닌가 ?

√ 혹자는 RI, PK 등 각종 Constraints 들로 인하여 성능상 문제가 있어 어쩔 수 없이 모두 해제하여 사용하고 있다고 한다. 진정 그런가 ? 아니다 ! DBMS Internal Processing에 의하여 보장되는 이러한 기능은 성능상 문제를 일으킬 만한 어리석은 DBMS는 아마 사장되었을 것이다. 성능의 문제는 바로 다른 곳에 있다. 이러한 잘못된 판단기준은 대부분 대량의 초기 데이터 구축시 체험한 감으로 잘못된 기준을 스스로 만들고 있는 것이다.

√ 대량의 데이터를 생성할 초기 Migration 시점이나 대량의 Batch작업시에는 해당 Constraints를 Disable시킨 상황에서 작업하는 것이 정답이며 작업 후 최단시간에(Parallel등의 기법 이용) Enable시키는 것이 정답이다. 단, 대량 데이터 처리시에 각종 제약사항을 모두 만족시키도록 데이터 생성 원칙을 준수한 프로그램이 되어야 할 것이다. 그외의 Online TXN 및 업무적인 데이터 처리에서의 성능상 문제는 결코 지대하지 않다는 것을 알아야 한다.

√ 이러한 Constraint 지정을 잠시 묵인토록 하는 Disable기법이나, 관계를 무시한 데이터의 삭제 및 수정작업을 위한 DDL Script는 DBMS마다 사용하는 방법에 약간의 차이가 있다. 하지만 어느 RDBMS에서나 이러한 작업을 위한 기법은 다 사용이 가능하니, 해당 DBMS Vendor에서 제공되는 SQL Reference Manual을 참고하기 바란다. (예:Oracle Cascade option : SQL Reference Manual , Create table, Alter table, constraint clause)

√상기 [그림1] 예제에서 참조적 무결성을 정의한 DEPT와 EMP Table을 위한 DDL Script는 다음과 같다.

Create Table DEPT
(deptno number not null primary key,
dname varchar(20),
loc varchar(20))

Create Table PROJ
(projno number not null primary key,
projname varchar(20),
stdt date,
enddt date)

Create Table EMP
(empno number not null primary key,
ename varchar(20),
deptno number references dept,
projno number references proj)

• 속성무결성 (Attribute Integrity)

√컬럼은 지정된 데이타 형식(format,type,size)을 만족하는 값만 포함해야 한다.

√ 상기 제약 정의의 대표적인 Constraints는 DDL Script에 사용되는 Data Type (size)들이며, 예를들면 숫자를 위한 NUMBER, 고정길이 Alpha-Numeric을 위한 CHAR, 가변길이 Alpha-Numeric을 위한 VARCHAR 등, DATE, LONG, LONG RAW 등등 DBMS제품마다 약간의 차이는 있지만 대부분 범용적으로 사용되는 Data type은 어는 DBMS나 거의 유사하다.

√임의의 테이블에는 하나 이상의 여러 속성들이 정의되게 되며 이러한 속성들은 물리적인 테이블의 컬럼들로 정의되게 되며 이렇게 하나의 컬럼이 지정될 경우에는 Datatype 및 Format이 지정되어야 한다.

√ 예로, 사원번호라는 컬럼이 number data type으로 정의될 경우에는 반드시 숫자 값만이 사원번호에 들어올 자격이 있으며 만일 사원번호 컬럼을 number(4) 로 지정할 경우에는 숫자 값 중에서 4자리수 이내의 값만이 들어올 자격이 있다는 의미이다.

√어떻게 보면 이러한 속성 무결성을 위한 고려는 매우 쉽게 생각하거나 간과하여 지나치는 경우가 많다.

√ 그러나, 진정 RDBMS내부를 아는 설계자라면 하나의 컬럼의 data type 및 format을 결정하는데 수 많은 고려를 하게 된다. 매출일자라는 컬럼의 Data type을 character로 지정하여야 하는지, 아니면 date format으로 지정하여야 할지 설계자의 짧은 판단으로 인하여 수많은 개발자와 후손들로부터 지탄의 대상이 되어서는 안된다. 이 부분에 대한 설명(data type의 결정)만으로도 언급해야 될 사항이 많기 때문에 별도의 자료에서 언급하기로 하고 여기서는 Integrity constraint에 대해서만 언급하기로 한다.

√상기 [그림1] 예제에서 속성 무결성을 정의한 DEPT Table을 위한 DDL Script는 다음과 같다.

Create Table DEPT
(deptno number not null primary key,
dname varchar(20),
loc varchar(20))

• 사용자정의 무결성(User defined Integrity)

√DB에 저장된 모든 데이타는 사전 정의된 업무규칙 (Business rule)을 준수해야 한다.

√상기 제약 정의의 대표적인 Constraints는 DDL Script에 사용되는 Check와 Default constraints이다.

√ 또한, 단순한 정의로 DB Internal Processing 에 도움을 얻기가 어려고 정의를 표현하기가 난해하여 적용하기가 어려운 업무규칙에 대해서는 DataBase Trigger를 이용하여 사용자 정의 무결성 보장을 하기도 한다.

√ 예로, 계약일자와 유효시작일자 라는 컬럼은 업무적으로 계약일자 <= 유효시작일자 라는 정의를 지켜야 한다거나, 계약금액은 100,000원 이상만이 들어올 수 있다는 업무 규칙 또한, 아무 값이 지정되지 않고 하나의 Row가 생성될 경우에 특정 컬럼의 값을 초기치(Default) 값으로 자동 입력토록 하는 등, 이러한 일련의 무결성 정의을 하고자 할 경우 설계자의 의도에 맞추어 정의할 수 있다.

√ 상기와 같은 단순한 무결성 정의로는 해결되지 않는 다소 복잡한 무결성 정의을 지정할 경우에는 흔히 사용하는 기법이 바로 DB Trigger이다. DB Trigger에서는 절차적인 판단 및 DML처리를 할 수 있으며, 거의 무제한에 가까운 사용자 정의 무결성 정의를 가능하게 하기 때문에 개발자들이 자주 사용하는 기법 중의 하나이다. 그러나, DB Trigger는 성능상 문제를 극복하기 위해서는 목숨걸고 최적화에 심혈을 기울여야 한다는 것을 명심하여야 한다.

√상기 [그림1] 예제에서 사용자정의 무결성을 정의한 PROJ Table을 위한 DDL Script는 다음과 같다. DB Trigger에 대한 예제는 DBMS 공급업체의 매뉴얼을 참고하기 바란다.

Create Table PROJ
(projno number not null primary key,
projname varchar(20) check (projname = upper(projname)),
stdt date check (stdt >= to_date('20000101','yyyymmdd')),
enddt date check (enddt <= to_date('20000101','yyyymmdd')),
check (stdt <= enddt))

• 끝으로 이러한 무결성 정의시에는 위에서 필자가 제시한 방법을 그대로 사용하지 말기 바란다. 문제는 이러한 무결성 정의에 대한 제어가 필요할 경우가 도래할 경우를 대비하여 모든 Constraint는 가급적 설계자 Naming Rule에 근거하여 모두다 Constraint name을 지정하여 생성하기 바란다. 이를 위하여 독자께서는 DBMS Vendor에서 제공하는 매뉴얼을 참고하여 향 후 Control이 가능한 정의를 하여야 한다.