DB2의 제약조건

DB2 기초: 제약조건

난이도 : 초급 Roman Melnyk, DB2 Information Development, IBM Canada Ltd. 2004 년 1 월 01 일 DB2 UDB에서 사용되는 제약조건은 데이터에 대해 비즈니스 규칙을 시행하고 데이터베이스 무결성을 유지하는데 도움이 된다. 이 글에서는 DB2 UDB에서 지원하는 다양한 제약조건 유형들을 설명한다. 각 제약조건 유형들을 예제를 통해 설명한다. 또한 기본적인 제약조건 관리(명령행 또는 DB2 Control Center 사용)를 설명한다. DB2 UDB에서 사용되는 제약조건은 데이터에 대해 비즈니스 규칙을 시행한다. 이 글에서는 다음과 같은 유형의 제약조건을 설명한다. NOT NULL 유일(unique) 기본 키 외래 키 테이블 체크 정보형 제약조건(informational constraint)으로 알려진 제약조건도 있다. 위에 열거한 다섯 개의 제약조건과는 달리 정보형 제약조건은 데이터베이스 관리자가 다룰 수 있는 것이 아니다. 쿼리 퍼포먼스를 높이기 위해 SQL 컴파일러에 의해 사용된다. 이 글에서는 위에 열거한 제약조건 유형들에 초점을 맞춰 설명할 것이다. 정보형 제약조건은 다음 기회에 다루기로 한다. 새로운 테이블을 만들 때 한 개 이상의 DB2 UDB 제약조건을 정의하거나, 테이블을 나중에 변경하여 몇 가지 제약조건을 정의할 수 있다. CREATE TABLE 문은 매우 복잡하다. 사실 너무 복잡해서 이 옵션들 중 일부가 제약조건 정의에 사용되더라도 신택스 다이어그램에서 볼 때에는 옵션들 자체로도 매우 복잡해보인다. (그림 1, 그림 2) 제약조건 관리는 DB2 Control Center를 통해 수행될 때 보다 편리하고 단순해질 수 있다. 그림 1. CREATE TABLE 문의 신택스 일부– 제약조건을 정의하는데 사용되는 구문을 보여주고 있다. 제약조건 정의는 이 정의가 적용될 데이터베이스와 제휴되고 데이터베이스 카탈로그에 저장된다.(표 1) 데이터베이스 카탈로그를 쿼리하여 정보들을 가져와서 검사한다. 명령행에서 직접 수행할 수 있고(물론, 데이터베이스에 먼저 연결해야 한다.), 또는 Control Center를 통해서 보다 편리하게 정보에 액세스 한다. 여러분이 만든 제약조건은 데이터베이스 객체들 처럼 다루어진다. 이름이 붙여지며, 제휴 스키마(생성자 ID)를 갖게 되며, 어떤 경우에는 제거(삭제)될 수도 있다. 그림 2. CREATE TABLE 문의 신택스 일부 – 제약조건을 정의하는데 사용되는 구문을 보여주고 있다. (계속) 표 1. 데이터베이스 카탈로그에 있는 제약조건 정보. 성공적으로 실행하려면 그 카탈로그에 대한 쿼리에는 데이터베이스 연결이 필요하다. Catalog View View Column 설명 쿼리 예제 SYSCAT.CHECKS 각 테이블 체크 제약조건에 대한 열이 포함된다. db2 select constname, tabname, text from syscat.checks SYSCAT.COLCHECKS 테이블 체크 제약조건에 의해 참조되는 각 칼럼에 대한 열을 포함하고 있다. db2 select constname, tabname, colname, usage from syscat.colchecks SYSCAT.COLUMNS NULLS 칼럼이 무효인지(Y), 무효가 아닌지(N)를 나타낸다. db2 select tabname, colname, nulls from syscat.columns where tabschema = 'MELNYK' and nulls = 'N' SYSCAT.CONSTDEP 몇 가지 다른 객체에 해당하는 제약조건의 종속성에 대한 열을 포함하고 있다. db2 select constname, tabname, btype, bname from syscat.constdep SYSCAT.INDEXES 각 인덱스에 대한 열을 포함하고 있다. db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'MELNYK' SYSCAT.KEYCOLUSE 유일 키, 기본 키, 외래 키 제약조건으로 정의된 키에 참여한 각 칼럼에 대한 열을 포함하고 있다. db2 select constname, tabname, colname, colseq from syscat.keycoluse SYSCAT.REFERENCES 각각의 참조 제약조건에 대한 열을 포함하고 있다. db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references SYSCAT.TABCONST 유일 키(U), 기본 키 (P), 외래 키 (F), 테이블 체크 (K) 제약조건에 대한 열을 포함하고 있다. db2 select constname, tabname, type from syscat.tabconst SYSCAT.TABLES PARENTS 이 테이블의 부모 테이블의 수(이 테이블이 종속되어 있는 참조 제약조건의 수). db2 "select tabname, parents from syscat.tables where parents > 0" SYSCAT.TABLES CHILDREN 이 테이블의 종속 테이블의 수(이 테이블이 부모가 되는 참조 제약조건의 수). db2 "select tabname, children from syscat.tables where children > 0" SYSCAT.TABLES SELFREFS 이 테이블에 대한 자가 참조 제약조간의 수(이 테이블이 부모이기도 하고 종속 테이블인 참조 제약조건의 수). db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0" SYSCAT.TABLES KEYUNIQUE 이 테이블에 정의된 (기본 키 외의) 유일 제약조건의 수. db2 "select tabname, keyunique from syscat.tables where keyunique > 0" SYSCAT.TABLES CHECKCOUNT 이 테이블에 정의된 체크 제약조건의 수. db2 "select tabname, checkcount from syscat.tables where checkcount > 0" NOT NULL 제약조건 NOT NULL 제약조건으로 인해 무효(null) 값이 칼럼에 추가될 수 없다. 이것 때문에 칼럼은 테이블의 각 열에 값을 갖게 된다. 예를 들어, SAMPLE 데이터베이스에 있는 EMPLOYEE 테이블의 정의에는 LASTNAME VARCHAR(15) NOT NULL이 포함된다. 이로 인해 각 열에는 사원(EMPLOYEE)의 성(last name)이 포함된다. 칼럼의 무효성 여부를 결정하기 위해서 그 테이블에 대한 데이터 정의 언어(DDL)를 참조할 수 있다. (db2look 유틸리티를 호출하여 생성한다.) DB2 Control Center(그림 3과 4)를 사용해도 된다. 또는 데이터베이스 카탈로그를 쿼리 할 수도 있다. (Listing 1) 그림 3. 특정 데이터베이스와 연결된 테이블은 DB2 Control Center의 컨텐츠 패인에 객체 트리에서 선택된 테이블과 함께 나타난다. 이 리스트는 melnyk 스키마에서 걸려졌다. DB2 Control Center에서는 테이블 같은 데이터베이스 객체에 편리하게 액세스 할 수 있다. 그림 3은 SAMPLE 데이터베이스의 사용자 테이블 모습이다. Table이 객체 트리에서 선택될 때 컨텐츠 패인에 나타난다. EMPLOYEE 테이블을 선택하면 Alter Table 창을 열어 칼럼 애트리뷰트 등 테이블 정보를 볼 수 있다. (그림 4) 그림 4. Alter Table 윈도우에서는 테이블의 애트리뷰트를 편리하게 볼 수 있다. Listing 1. 데이터베이스 카탈로그를 쿼리하여 어떤 데이터베이스 칼럼이 무효인지를 파악하기 db2 select tabname, colname, nulls from syscat.columns where tabschema = 'MELNYK' and nulls = 'N' 유일 제약조건 유일 제약조건(unique constraint)은 테이블의 특정 칼럼에 값이 한번 이상 나타나지 않도록 한다. 또한 특정 칼럼 세트 내에 값 세트가 한 번 이상 나타나지 않도록 한다. 유일 제약조건에서 참조되는 칼럼들은 NOT NULL로 정의되어야 한다. 유일 제약조건은 CREATE TABLE 문에서 UNIQUE 구문을 사용하여 정의되거나(그림 1과 2), ALTER TABLE 문으로 정의될 수 있다. Listing 2. 유일 제약조건 만들기. SAMPLE 데이터베이스에서 ORG_TEMP 테이블은 ORG 테이블과 동일하다. 단, ORG_TEMP의 LOCATION 칼럼은 무효가 될 수 없고 유일 제약조건이 정의된다. db2> create table org_temp (   deptnumb smallint not null,  deptname varchar(14),  manager smallint,  division varchar(10),  location varchar(13) not null)  db2 alter table org_temp  add unique (location); db2> insert into org_temp values (10, 'Head Office', 160, 'Corporate', 'New York') ; DB20000I The SQL command completed successfully. db2> insert into org_temp values (15, 'New England', 50, 'Eastern', 'New York'); SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MELNYK.ORG_TEMP" from having duplicate rows for those columns. SQLSTATE=23505 유일 제약조건은 의도하지 않은 중복을 방지하여 데이터 무결성을 유지시킨다. 우리 예제에서, New York을 그 회사의 지사로 지정하는 두 번째 기록이 삽입되는 것을 방지한다. 유일 제약조건은 유일 인덱스(unique index)를 통해 실행된다. 제약조건 이름 제약조건을 만들 때 이름을 정하지 않았다면 DB2에서 생성시간을 기준으로 이름을 만든다. 예) SQL031229211328410 기본 키(primary key) 제약조건 기본 키 제약조건은 테이블에 대한 기본 키를 구성하고 있는 칼럼이나 칼럼 세트의 모든 값들이 유일(unique)한 값이라는 것을 보장한다. 기본 키는 테이블의 특정 열을 구분하는데 사용된다. 테이블은 한 개 이상의 기본 키를 가질 수 없지만 여러 유일 키를 가질 수는 있다. 기본 키 제약조건은 유일 제약조건의 특별한 경우이며 기본 인덱스(primary index)를 통해 실행된다. 기본 키 제약조건에 참조된 칼럼들은 NOT NULL로 정의되어야 한다. 기본 키 제약조건은 PRIMARY KEY 구문을 사용하여 CREATE TABLE 문으로 정의되거나(그림 1과 2), ALTER TABLE 문으로 정의된다. Listing 3. 기본 키 제약조건 만들기. EMPLOYEE 테이블에 있는 EMPNO 칼럼은 무효가 될 수 없고, 여기에 기본 키 제약조건이 정의되어야 한다. db2 alter table employee add primary key (empno) 대안으로는, DB2 Control Center를 사용하여 테이블에 기본 키 제약조건을 정의하는 방법이 있다. (그림 5) 그림 5. Alter Table 윈도우에서는 테이블에 기본 키 제약조건을 편리하게 정의할 수 있다. 칼럼 리스트에서 한 개 이상의 칼럼을 선택한 다음 push 버튼을 누르고, 그렇게 선택된 칼럼 이름을 기본 키 칼럼 리스트로 이동시킨다. 선택된 칼럼은 무효가 될 수 없다. 외래 키 제약조건 외래 키 제약조건은 참조 제약조건이라고도 불린다. 참조 무결성(Referential integrity)은 "데이터베이스에서 모든 외래 키들의 값이 유효한 상태”로 정의된다. 그렇다면 외래 키는 무엇인가? 외래 키(foreign key)는 테이블에 있는 칼럼이나 칼럼 세트의 값이 부모 테이블의 열에 있는 적어도 한 개의 기본 키나 유일 키 값과 매치하는 것을 의미한다. 더 정확히 말한다면? 테이블(T2)에 있는 칼럼(C2)이 또 다른 테이블(T1)에 있는 칼럼(C1)의 값들과 매치하는 값을 갖고 있고, C1은 T1에 대한 기본 키 칼럼이 되고, C2는 T2의 외래 키 칼럼이 된다는 것을 의미한다. 기본 키(기본 키 또는 유일 키)를 포함하고 있는 테이블을 부모 테이블(parent table)이라 하고, 외래 키를 포함하고 있는 테이블을 종속 테이블(dependent table)이라고 한다. SAMPLE 데이터베이스에 있는 PROJECT 테이블은 RESPEMP라는 칼럼을 갖고 있다. 이 칼럼에 있는 값은 이 테이블에 수록되어 있는 각 프로젝트를 담당하는 사원들의 수를 나타낸다. RESPEMP는 무효가 될 수 없다. 이 칼럼은 EMPLOYEE 테이블의 EMPNO 칼럼에 상응하고, 우리는 EMPNO가 이제는 EMPLOYEE 테이블에 대한 기본 키라는 것을 알고, RESPEMP는 PROJECT 테이블에서 외래 키로 정의될 수 있기 때문이다.(Listing 4) 따라서 EMPLOYEE 테이블에서 삭제가 실행되면 프로젝트 담당 사원이 없는 PROJECT 테이블은 존재하지 않는다. 외래 키 제약조건은 FOREIGN KEY 구문(그림 1과 2)을 사용하는 CREATE TABLE 문으로 정의되거나, ALTER TABLE 문으로 정의된다. Listing 4. 외래 키 제약조건 만들기 db2> alter table project add foreign key (respemp) references employee on delete cascade REFERENCES 구문은 이 참조 제약조건에 대한 부모 테이블을 가리킨다. 외래 키 제약조건을 정의하는 신택스에는 rule-clause가 포함되는데, 바로 여기에서 무결성의 관점에서 업데이트나 삭제 방식을 DB2에 명령할 수 있다.(그림 1) 삽입 작동은 표준 방식으로 다루어진다. 여러분은 이에 대한 제어권이 없다. 참조 제약조건의 삽입 규칙(insert rule)은 외래 키의 삽입 값이 부모 테이블의 부모 키의 값 일부와 매치해야 한다는 것을 지정하고 있다. 새로운 기록이 PROJECT 테이블에 삽입되면 그 기록에는 EMPLOYEE 테이블의 기존 기록에 대한 (부모-외래 키 관계를 통해) 참조를 포함하고 있어야 한다. 참조 제약조건의 업데이트 규칙(update rule)은 외래 키의 업데이트 값이 부모 테이블의 부모 키의 일부 값과 매치해야 하고, 부모 키에 대한 업데이트 작동이 완료할 때 모든 외래 키 값은 매칭하는 부모 키 값을 갖고 있어야 한다는 것을 지정하고 있다. 다시 말하면, 어떤 "고아"도 없어야 한다. 모든 종속들은 부모가 있어야 한다. 참조 제약조건의 삭제 규칙(delete rule)은 부모 테이블에서 열이 삭제될 때 적용되고, 참조 제약조건이 정의될 때 지정되었던 옵션에 의존한다. RESTRICT나 NO ACTION 구문이 지정되면 어떤 열도 삭제되지 않는다. SET NULL 구문이 지정되면 외래 키의 무효가 가능한 칼럼들이 무효로 설정된다. 하지만 참조 제약조건을 만들 때 CASCADE 옵션을 지정했다면 삭제 작동은 부모 테이블의 자식들에게로 퍼진다. 다음 예제는 위에 설명한 것들을 나타낸 것이다. Listing 5. 외래 키 제약조건에서의 업데이트 규칙과 삭제 규칙 db2> update employee set empno = '350' where empno = '000190' DB20000I The SQL command completed successfully. db2> update employee set empno = '360' where empno = '000150' SQL0531N The parent key in a parent row of relationship "MELNYK.PROJECT.SQL040103212526610" cannot be updated. SQLSTATE=23504 db2> "select respemp from project where respemp < '000050' order by respemp" RESPEMP ------- 000010 000010 000020 000030 000030 db2> delete from employee where empno = '000010' DB20000I The SQL command completed successfully. db2> "select respemp from project where respemp < '000050' order by respemp" RESPEMP ------- 000020 000030 000030 부모 테이블(EMPLOYEE)에 있는 EMPNO 값 '000190'은 변경될 수 있다. 종속 테이블(PROJECT)에 '000190' RESPEMP 값이 없기 때문이다. 하지만 EMPNO 값 '000150'는 경우가 다르다. 이것은 PROJECT 테이블에 매칭하는 외래 키 값을 갖고 있고 따라서 업데이트 될 수 없다. CASCADE 옵션을 지정한 삭제 규칙으로 인해 기본 키 값 '000010'이 EMPLOYEE 테이블에서 삭제되면 삭제 연결된(delete-connected) PROJECT 테이블은 매칭하는 외래 키 값을 포함하고 있는 모든 열을 잃게 된다. 테이블 체크 제약조건 테이블 체크 제약조건(table check constraint)은 데이터에 정의된 제한들이 테이블에 추가될 수 있도록 한다. 예를 들어, 테이블 체크 제약조건은 EMPLOYEE 테이블에 내선 번호가 추가 및 업데이트 될 때 정확히 4자리 수가 되도록 한다. 테이블 체크 제약조건은 CHECK 구문을 사용하여 CREATE TABLE 문으로 정의되거나(그림 1과 2), ALTER TABLE 문으로 정의된다. Listing 6. 테이블 체크 제약조건 만들기. PHONENO_LENGTH 제약조건은 EMPLOYEE 테이블에 추가되는 내선번호를 4자리 숫자로 지정한다. db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4) 대안으로는 DB2 Control Center를 사용하여 테이블 체크 제약조건을 정의할 수 있다. (그림 6) 그림 6. Alter Table 윈도우에서 테이블 체크 제약조건을 편리하게 정의할 수 있다. Add 버튼을 클릭하여 새로운 제약조건(Add Check Constraint 옵션)을 정의하거나, Change 버튼을 눌러 기존 제약조건을 변경한다.(그림 7) 그림 7. Change Check Constraint 창에서 기본 체크 조건을 변경할 수 있다. 이 테이블의 기존 열의 값이 새로운 제약조건을 위반한다면 테이블 체크 제약조건을 만들 수 없다.(그림 8) 이렇게 비 호환되는 값이 적절히 업데이트 된 후에 제약조건을 추가하거나 수정할 수 있다. 그림 8. 새로운 테이블 체크 제약조건이 테이블의 기본 값과 호환되지 않는다면 에러가 리턴된다. 데이터 체크 연기하기 SET INTEGRITY 문은 테이블을 체크 보류 상태로 만드는데 사용된다. 새로운 체크 제약조건을 정의한 ALTER TABLE 문은 이 테이블의 기존 값을 체크하지 않고 처리된다. 테이블 체크 제약조건은 SET INTEGRITY 문을 사용하여 실행되거나 실행되지 않는다. 테이블에 대해 대규모의 데이터 부하가 걸려있는 동안 퍼포먼스를 최적화 할 때 매우 유용하다. Listing 7은 SET INTEGRITY 문을 사용하는 시나리오이다. 이 예제에서 EMPLOYEE 테이블의 무결성 체크가 비활성 된 후에, 사원 '000100 '의 내선 번호는 123 값으로 업데이트 된다. 4자리 숫자의 내선 번호 값이 필요한 체크 제약조건은 EMPLOYEE 테이블에서 정의된다. EMPL_EXCEPT 라고 하는 예외 테이블이 만들어 진다. 이 새로운 테이블의 정의는 EMPLOYEE 테이블의 정의를 모방한 것이다. 무결성 체크가 활성화 되면, 이러한 테이블에 대한 쿼리는 해당 열이 예외 테이블에만 존재하는지를 확인한다. Listing 7. SET INTEGRITY 문을 사용하여 제약조건 체크 연기하기 db2 update employee set phoneno = '123' where empno = '000100' db2 set integrity for employee off db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4) db2 create table empl_except like employee db2 set integrity for employee immediate checked for exception in employee use empl_except SQL3602W Check data processing found constraint violations and moved them to exception tables. SQLSTATE=01603 db2 select empno, lastname, workdept, phoneno from empl_except EMPNO LASTNAME WORKDEPT PHONENO ------ --------------- -------- ------- 000100 SPENSER E21 123 1 record(s) selected. 위로 요약 지금까지 DB2 Universal Database에서 지원하는 여러 가지 제약조건 유형들을 살펴보았다. 이러한 제약조건들이 DB2 UDB에 사용되어 데이터에 대해 비즈니스 규칙을 실행하고 데이터 무결성을 유지하는 방법도 알았다. 또한, 제약조건을 효과적으로 관리하는 방법으로 명령행과 DB2 Control Center의 사용법에 대해서도 배웠다.

 

참고자료

• DB2 Technical Support.
  

필자소개

	Roman Melnyk, DB2 Information Development, IBM

출처 :http://www-128.ibm.com/developerworks/kr/library/dm-0401melnyk/