12 데이터 무결성 제약조건 데이터 무결성 제약조건의 개념 데이터 무결성 제약조건의 종류 무결성 제약조건의 생성 방법.

Presentation on theme: "12 데이터 무결성 제약조건 데이터 무결성 제약조건의 개념 데이터 무결성 제약조건의 종류 무결성 제약조건의 생성 방법."— Presentation transcript:

1 12 데이터 무결성 제약조건 데이터 무결성 제약조건의 개념 데이터 무결성 제약조건의 종류 무결성 제약조건의 생성 방법

2 데이터 무결성 제약조건의 개념 데이터 무결성 제약조건의 개념 데이터 무결성 제약조건의 장점 데이터의 정확성과 일관성을 보장
데이터의 정확성을 유지하여 다양한 종류의 업무규칙 고려한 예 student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력 모든 학번은 유일 Student 테이블의 지도교수 번호는 professor 테이블의 교수 번호중의 하나와 반드시 일치 데이터 무결성 제약조건의 장점 테이블 생성시 무결성 제약조건을 정의 가능 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용 제약조건을 활성화, 비활성화 할 수 있는 융통성 12장. 데이터 무결성 제약조건

3 데이터 무결성 제약조건의 종류 12장. 데이터 무결성 제약조건

4 부서 테이블 학생 테이블 입력성공 입력실패 ① 기본 키 무결성 제약조건 - STUDNO 컬럼에는 값이 반드시 존재해야 함
DEPTNO DNAME COLLEGE LOC 101 컴퓨터공학과 100 1호관 102 멀티미디어학과 2호관 …… ……… … …. 정보미디어학부 10 공과대학 ① 기본 키 무결성 제약조건 - STUDNO 컬럼에는 값이 반드시 존재해야 함 - STUDNO 컬럼의 모든 값은 반드시 유일해야 함 ② 고유 키 무결성 제약조건 - USERID, IDNUM 컬럼의 모든 값은 반드시 유일해야 함 학생 테이블 STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 051) 176 72 101 9903 20101 이동훈 Dals 1 83/12/10 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 70 102 9905 10107 이광훈 huriky 81/10/13 055) 175 92 ④ CHECK 무결성 제약조건 - GRADE 컬럼 값은 반드시 1, 2, 3, 4 중 하나여야 함 ⑤ 참조키 무결성 제약조건 - 학생 테이블의 DEPTNO컬럼의 값은 부서 테이블의 DEPTNO컬럼의 값 중 하나와 일치해야 함 ③ NOT NULL 무결성 제약조건 - NAME 컬럼에는 값이 반드시 존재해야 함 컬럼 값으로 NULL을 가질 수 없음 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력실패 입력 예 (2) 10101 guest 5 500 9903 기본키 NOT NULL CHECK 고유키 참조키

5 NOT NULL 무결성 제약조건 입력성공 입력실패 12장. 데이터 무결성 제약조건 NOT NULL 무결성 제약조건 존재
STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 176 72 101 9903 20101 이동훈 1 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 102 9905 10107 이광훈 huriky 81/10/13 055) 92 NOT NULL 무결성 제약조건 존재 - STUDNO, NAME 컬럼에는 NULL을 입력할 수 없음 NOT NULL 무결성 제약조건이 없음 이 컬럼들의 어느 열에든지 NULL을 입력할 수 있음 은 NULL을 의미 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력실패 입력 예 (2) chsung 4 101 9903 - STUDNO 값 과 NAME 값은 NULL을 허용하지 않음 12장. 데이터 무결성 제약조건

6 고유 키 무결성 제약조건 존재 : 컬럼의 모든 값이 유일해야 하므로 중복될 수 없음
STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 051) 176 72 101 9903 20101 이동훈 Dals 1 83/12/10 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 70 102 9905 10107 이광훈 huriky 81/10/13 055) 175 92 고유 키 무결성 제약조건이 없음 - GRADE(학년) 컬럼의 값은 중복될 수 있음 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력성공 입력 예 (2) 30102 성춘향 chsung 2 86/05/05 055) 182 78 201 - 고유키 무결성은 NULL 값을 허용하므로 입력 성공 입력실패 입력 예 (3) 30103 이몽룡 jun123 4 101 9903 - USERID 값 ‘jun123’ 과 IDNUM 값 ‘ ’ 값이 이미 존재함 12장. 데이터 무결성 제약조건

7 기본키 무결성 제약조건 존재 : 컬럼의 모든 값이 유일하고, NULL을 허용하지 않음
기본 키 무결성 제약조건 기본키 무결성 제약조건 존재 : 컬럼의 모든 값이 유일하고, NULL을 허용하지 않음 STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 051) 176 72 101 9903 20101 이동훈 Dals 1 83/12/10 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 70 102 9905 10107 이광훈 huriky 81/10/13 055) 175 92 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력실패 입력 예 (2) 성춘향 chsung 2 86/05/05 055) 182 78 201 - 기본 키 무결성 제약조건은 NULL을 허용하지 않음 입력실패 입력 예 (3) 10101 jun123 4 101 9903 - 기본 키 무결성 제약조건은 중복을 허용하지 않음, STUDNO 의 값 은 이미 존재함 12장. 데이터 무결성 제약조건

8 참조 무결성 제약조건 한 테이블의 칼럼 값이 자신, 다른 테이블의 칼럼 값 중에 하나와 일치시키기 위한 제약조건 용어
자식 테이블 : 다른 테이블의 칼럼 값을 참조하는 테이블 부모 테이블 : 다른 테이블에 의해 참조되는 테이블 외래 키 : 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼 참조 키 : 자식 테이블에서 참조하는 부모 테이블의 칼럼 12장. 데이터 무결성 제약조건

9 부서 테이블 학생 테이블 입력성공 입력성공 입력실패 참조 키 (referenced key) 외래 키 (foreign key)
DEPTNO DNAME COLLEGE LOC 101 컴퓨터공학과 100 1호관 102 멀티미디어학과 2호관 …… ……… … …. 정보미디어학부 10 공과대학 학생 테이블 STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 051) 176 72 101 9903 20101 이동훈 Dals 1 83/12/10 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 70 102 9905 10107 이광훈 huriky 81/10/13 055) 175 92 외래 키 (foreign key) 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력성공 입력 예 (2) 30102 성춘향 chsung 2 86/05/05 055) 182 78 - 참조 무결성 제약조건은 NULL을 허용하므로 입력 성공 입력실패 입력 예 (3) 30103 이몽룡 guest 4 500 9903 - DEPTNO의 값 500 은 부서 테이블에 존재하지 않음

10 CHECK 무결성 제약조건 칼럼에서 허용 가능한 데이터의 범위나 조건을 지정
데이터 입력이나 수정시 실수로 부정확한 값을 입력 예방 하나의 컬럼에 여러 개의 CHECK 무결성 제약조건을 지정 가능 CURRVAL, NEXTVAL과 같은 가상 칼럼이나 SYSDATE, USER와 같은 함수는 사용 불가능 예 student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력 가능 12장. 데이터 무결성 제약조건

11 CHECK 무결성 제약조건 존재 : 지정한 조건에 부합되는 값만 허용
STUDNO NAME USERID GRADE IDNUM BIRTHDATE TEL HEIGHT WEIGHT DEPTNO PROFNO 10101 전인하 jun123 4 79/04/02 051) 176 72 101 9903 20101 이동훈 Dals 1 83/12/10 055) 172 64 201 ……… 10203 윤진욱 Samba7 3 053) 171 70 102 9905 10107 이광훈 huriky 81/10/13 055) 175 92 입력성공 입력 예 (1) 30101 홍길동 gdhong 1 86/05/05 055) 182 78 201 입력성공 입력 예 (2) 30102 성춘향 chsung 86/05/05 055) 182 78 201 - GRADE는 check 무결성 제약조건만 가지므로 NULL은 허용 가능 입력실패 입력 예 (3) 30103 이몽룡 mrlee 6 86/05/05 055) 182 78 201 - GRADE의 값은 1, 2, 3, 4 만 허용되므로 6은 조건에 부합하지 않음 12장. 데이터 무결성 제약조건

12 무결성 제약조건 생성 방법 무결성 제약조건 생성 테이블 생성과 동시에 정의 테이블을 생성한 이후에 추가, 삭제 할 수 있음
제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동생성 칼럼 레벨 : 칼럼 정의시 해당 칼럼별로 지정 테이블 레벨 : 제약조건이 하나 이상의 칼럼을 참조, 지정 12장. 데이터 무결성 제약조건

13 칼럼 레벨에서 제약조건 정의 12장. 데이터 무결성 제약조건

14 테이블 레벨에서 제약조건 정의 12장. 데이터 무결성 제약조건

15 무결성 제약조건 생성문에서의 키워드 ON DELETE CASCADE USING INDEX NOT DEFERRABLE :
부모 테이블에서 외래 키가 참조하는 기본 키나 고유 키를 포함한 행을 삭제할 경우 자식 테이블의 외래 키를 포함하는 행도 함께 삭제 USING INDEX 기본 키나 고유 키 무결성 제약조건 생성시 묵시적으로 생성되는 인덱스에 대한 스토리지 파라미터 정의 NOT DEFERRABLE : 하나의 DML 명령문이 처리될 때마다 제약조건 위반 여부를 검사, defer_spec을 명시하지 않을 경우에는 기본 값 DEFERRABLE 트랜잭션내의 모든 DML 명령문에 대한 제약조건 검사를 트랜잭션 종료 시까지 연기 INITIALLY IMMEDIATE DML 명령문이 종료될 때마다 제약조건 검사 INITIALLY DEFERRED 트랜잭션이 끝날 때만 제약조건 검사 12장. 데이터 무결성 제약조건

16 무결성 제약조건 생성 예 SQL PLUS를 이용한 실습 12장. 데이터 무결성 제약조건

17 무결성 제약조건 조회 12장. 데이터 무결성 제약조건

18 기존 테이블에 무결성 제약조건 추가 NULL을 제외한 무결성 제약조건 추가 NULL 무결성 제약 조건 추가
ALTER … ADD CONSTRAINT 명령문 사용 NULL 무결성 제약 조건 추가 ALTER TABLE…MODIFY 명령문 사용 12장. 데이터 무결성 제약조건

19 무결성 제약조건 추가 예 12장. 데이터 무결성 제약조건

20 무결성 제약조건에 의한 DML 명령문의 영향 즉시 제약조건(immediate constraints)에 위배되는 데이터 입력시
테이블에 데이터를 먼저 입력한 다음 무결성 제약조건을 위반하는 명령문을 롤백 지연 제약조건(deferred constraints)에 위배되는 데이터 입력시 트랜잭션내의 DML 명령문에서 제약조건 검사를 COMMIT 시점에서 한꺼번에 처리하여 트랜잭션의 처리 성능을 향상시키기 위해 사용 12장. 데이터 무결성 제약조건

21 즉시 제약조건 위배되는 데이터 입력 1 12장. 데이터 무결성 제약조건

22 즉시 제약조건 위배되는 데이터 입력 2 12장. 데이터 무결성 제약조건

23 지연 제약조건 위배되는 데이터 입력 SQL PLUS를 이용한 실습 12장. 데이터 무결성 제약조건

24 무결성 제약조건 삭제 무결성 제약조건 삭제시 ALTER TABLE … DROP CONSTRAINT 명령문 사용
부모 테이블의 기본키 무결성 제약조건을 삭제하는 경우 참조 무결성 제약조건을 먼저 삭제한 후 삭제하거나 CASCADE 옵션 사용 12장. 데이터 무결성 제약조건

25 무결성 제약조건 삭제 예 SQL PLUS를 이용한 실습 12장. 데이터 무결성 제약조건

26 무결성 제약조건 활성화 및 비활성화 대용량 데이터 초기 입력시
무결성 제약조건의 위반 여부를 검사하는 과정으로 인해 처리 시간이 오래 걸리는 경우가 발생 이러한 경우에는 기존의 무결성 제약조건을 일시적으로 비활성화하여 데이터를 입력한 다음, 비활성화된 무결성 제약조건은 다시 활성화 ALTER TABLE 명령문에서 ENABLE 또는 DISABLE 절 사용 12장. 데이터 무결성 제약조건

27 무결성 제약조건의 비활성화 12장. 데이터 무결성 제약조건

28 무결성 제약조건 비활성화 확인 12장. 데이터 무결성 제약조건

29 무결성 제약조건의 활성화 12장. 데이터 무결성 제약조건

30 무결성 제약조건의 활성화 예 12장. 데이터 무결성 제약조건

31 무결성 제약조건 조회 1 USER_CONSTRAINTS
제약조건이 설정된 테이블 이름, 무결성 제약조건 이름, 무결성 제약조건 종류 및 활성화 상태정보를 저장 12장. 데이터 무결성 제약조건

32 무결성 제약조건 조회 2 USER_CONS_COLUMNS 제약조건이 설정된 칼럼 이름을 저장 12장. 데이터 무결성 제약조건