제약 조건과 트리거

1. 제약 조건이 없다면?

1) 새로운 값에 대해 삽입, 삭제, 업데이트 연산이 이루어지는 경우

(1) 애플리케이션 프로그램에서 값의 정당성을 판단하는 코드를 짜는 방법
- 제약 조건에 변화가 일어났을 때 해당 코드를 바꿔주어야 하며 그에 따라 다시 컴파일해야 하는 문제가 생김

- 프로그래머가 바뀐 제약 조건을 모르는 경우에도 문제가 생김

-> 데이터와 애플리케이션 프로그램간의 독립성 보장 불가

 

(2) 제약 조건을 DB 스키마의 일부로 저장하는 방법
- (1)의 문제들을 해결할 수 있음

 

 

2) active(능동적) 요소

- 데이터에 변경 연산이 발생했을 경우 수행되는 요소

(1) 무결성 제약 조건(IC, Integrity Constraint)
- key constraint, foreign-key constraint (참조 무결성 제약조건)
- constraint on attributes : NOT NULL, UNIQUE
- constraint on tuples
- assertion

(2) Trigger
- 특정 이벤트에 호출됨

2. Key and Foreign Keys

1) Foreign-key Constraint

- 참조 무결성 제약조건 : 외래키에 대한 규칙으로, 외래키는 참조할 수 없는 값을 가질 수 없다는 규칙이다. 외래키는 반드시 다른 릴레이션의 key attribute를 참조하며, 외래키 attribute의 모든 값들은 참조되는 key attribute에 있는 모든 값들 안에 속해야 한다는 말이다.

- 외래키는 NULL값도 허용한다.

선언 방법 예시들

 

 

2) 참조 무결성 제약조건을 유지하는 정책

(1) 참조 무결성 제약조건을 위반하는 연산을 허용하지 않는 정책(기본 정책)
- 참조하는 릴레이션은 위반되는 insert or update 거절
- 참조되는 릴레이션은 위반되는 deletion or update 거절

(2) Cascade 정책
- 값이 같이 변경되게 하는 방법
- Cascade deletion : 참조되는 튜플이 삭제되면 참조하는 튜블도 삭제
- Cascade update : 참조되는 튜플이 업데이트되면 참조하는 튜플도 업데이트

(3) NULL로 두는 방법
- 참조되는 튜플이 deleted or updated 될 때, 참조하는 튜플의 외래키 값을 NULL로 설정

선언 방법 예시

 

 

 

3) 제약 조건 체크를 지연하는 정책

- 참조 무결성 제약조건을 체크하기 전에, 참조되는 릴레이션에 먼저 적절한 튜플을 삽입함으로써 사전에 위반을 막는 정책

- DEFERRABLE INITIALLY DEFERRED : 각 트랜잭션이 commit되기 직전에 체크
- DEFERRABLE INITIALLY IMMEDIATE : 지연 없이 체크
- SET CONSTRAINT 제약조건이름 DEFERRED : IMMEDIATE -> DEFERRED
- SET CONSTRAINT 제약조건이름 IMMEDIATE : DEFERRED -> IMMEDIATE
- NOT DEFERRABLE : 기본값, DEFFERRED로 변경 불가

선언 방법 예시

 

 

4) Circular constraints에 대한 정책

(1) Circular constraints란?
- 두 릴레이션이 서로를 참조하는 상황
- 두 릴레이션에 어떤 튜플도 삽입될 수 없음 -> 삽입이 일어날 때마다 참조 무결성 제약조건이 위반됨

(2) 정책
- 두 삽입 연산을 하나의 트랜잭션으로 묶어서 삽입이 동시에 일어나게 하면 된다. 트랜잭션이 끝날 때까지 DBMS의 제약조건 체크를 지연시킨다.

 

3. Constraint on Attributes and Tuples

1) NOT NULL

- NULL을 허용하지 않는 Attribute에 적용
- insert/update시 NULL 적용 불가
- 참조 무결성 제약조건의 유지 정책인 set-null 사용 불가

선언 방법 예시

 

2) CHECK

- insert/update 수행 시 Attribute, Tuple에 대한 검사를 위한 조건
- WHERE문에서 나타날 수 있는 어느 조건이든 사용 가능
- 조건에 만족하지 않으면 연산이 거절됨
- 튜플이 삭제될 때는 CHECK하지 않음
- 조건이 다른 릴레이션을 참조한다면, 해당 서브쿼리에 있는 릴레이션에 변화가 발생했을 때 체크가 수행되지 않기 때문에 주의해야 함
- 조건에서 참조하는 Attribute가 2개 이상일 경우에는 Tuple 기반, 1개일 경우에는 Attribute 혹은 Tuple(일반적) 기반으로 작성함
- Tuple 기반에서는 어느 Attribute가 변경될 때마다 CHECK가 수행되기 때문에, Attribute 기반보다 더 자주 CHECK된다.

Attribute 기반 선언 방법 예시

Tuple 기반 선언 방법 예시

 

4. 제약 조건에 이름 붙히기

- 하나의 제약 조건을 여러 부분에서 활용하기 위해 이름을 붙임

- 테이블에 특정 이름의 제약 조건을 붙일 수 있음. 단, 추가 시에는 모든 튜플들이 해당 제약 조건을 만족하고 있을 때 가능

※ Tuple 기반으로 적용됨

- 존재하는 제약 조건을 편집하려면 기존 제약 조건을 지우고 새 제약 조건을 추가하는 방법을 써야 함

 

5. Assertions

- 데이터베이스가 항상 만족해야 하는 True 조건을 표현한 것
- 서브쿼리 안에 있는 릴레이션의 변화 감지를 못하는 문제를 해결한 방법
- 여러 개의 릴레이션들을 포함한 무결성 규칙, 튜플의 삭제에 영향을 받는(예 - Aggregation 연산) 무결성 규칙에 유용함

- 아래의 경우에는 MovieExec 테이블의 networth Attribute에 변화가 일어났어도 감지하지 못함

선언 방법 예시
CREATE ASSERTION 단언이름 CHECK 조건

제거 방법
DROP ASSERTION 단언이름

+) CHECK, ASSERTION 총 정리

※ On any change to any mentioned relation : 릴레이션의 변화가 이루어지는 모든 상황

 

6. Trigger

1) Trigger란?

- ECA(Event-Condition-Action) 규칙 : 이벤트가 발생했을 때 조건이 맞으면 액션을 수행하는 것 - 예) A값에 변동이 일어났는데(Event) A값이 감소했다면(Condition) A를 전의 상태로 복원하라(Action)
- 데이터베이스의 일관성을 보장할 수 있도록 도와줌

 

 

2) 제약 조건과의 차이

- 트리거는 DB 프로그래머가 명시한 특정 이벤트에만 발생하며, 이벤트가 발생하자마자 조건을 검사함

 

3) Trigger 생성

- BEFORE|AFTER : 이벤트의 적용 전 또는 후에 트리거(Condition, Action 체크)를 수행

※ BEFORE은 삽입될 튜플들을 사전에 검사하고 수정할 때 유용하다. - 예) NULL이 허용되지 않는다면 특정 기본값을 부여

- OLD ROW AS : 이벤트에 의한 변동 전의 튜플, NEW ROW AS : 이벤트에 의한 변동 후의 튜플
- OLD TABLE AS : old rows 테이블, NEW TABLE AS : new rows 테이블
- Action granularity : action의 단위 (튜플 : FOR EACH ROW, 테이블 : FOR EACH STATEMENT(기본값))

Trigger 예제 1 - row level trigger

- OF 키워드는 UPDATE에서 밖에 쓰지 못한다. Update는 Attribute 단위의 변동이 생기는 항이기 때문에, 어떤 Attribute의 변화를 체크할 것인지 명시해야 하는 것이다.
- OLD ROW AS 키워드는 INSERT문에서 사용할 수 없다.
- NEW ROW AS 키워드는 DELETE문에서 사용할 수 없다.
- REFERENCING문에서 ROW와 TABLE을 함께 참조할 수 있으며, 이 때는 Action granularity를 FOR EACH ROW로 작성한다.
- WHEN문이 없다면 이벤트가 발생할 때마다 action이 수행된다.
- action부분에 여러 SQL문을 작성해야 한다면 BEGIN, END 키워드 사이에 작성하면 된다.
- statement-level trigger은 튜플 단위로 처리되지 않고 한꺼번에 처리된다.

Trigger 예제 2 - statement level trigger

- 삽입, 삭제, 변경 모두 평균값에 영향을 미치므로 3개의 이벤트에 대한 Trigger가 필요하다 -> FOR EACH STATEMENT
- 트리거 이벤트 UPDATE OF는 삽입, 삭제 연산도 감지할 수 있다.
- 위 예제의 action은 변화가 일어난 튜플들을 통째로 삭제하고 기존 튜플들을 통째로 복구하는 것이다.

Trigger 예제 3 - BEFORE trigger

- NewStuff은 변화가 일어난 튜플들을 모은 테이블이기 때문에 통째로 year값을 1915로 바꿔준다.

4) INSTEAD OF

- BEFORE or AFTER 대신 사용되는 키워드로, 트리거 이벤트는 실행되지 않고 대신 트리거 액션만 실행된다.
- VIEW에서 유용하다.