[DB] On Delete, On Update, Check, Assertion

Integrity Constraint On Delete, On Update

teaches 가 professor 를 참조하고 있는 상황에서 professor 가 삭제 혹은 수정 될 때

• Cascade : 관련된 teaches 도 삭제

• Set null : teaches 의 pID 를 null 로 설정

• 위의 경우 허용되지 않음

• teaches 의 PK 는 다중 column 으로 구성되었으며 그 중 한 column 이 pID 이기 때문

• No action : 허용하지 않음

• pID 가 200인 teaches row 를 모두 삭제한 뒤 professor 200을 삭제 가능

Deferrable IC (지연된 제약조건)

초기 DB를 구성하는 상황에서 미리 준비된(준비되었다고 가정) 대용량의 teaches 와 professor 의 정보를 막 넣는 상황임

가장 좋은 방법은 professor 의 정보를 먼저 다 넣고 teaches 를 그 다음에 넣는 것이지만 현실적으로 어려울 수도 있음

이럴 때 사용하는 방법이 Defferable IC 임

Default 로는 IC 는 바로바로 체크되어 제약을 걸어야하지만 그렇지 않고 일단 insert 한 뒤에 Transaction 의 맨 끝에 한 번만 IC를 체크하는 방법임

Check

Mysql 은 check 구문을 통해 복잡한 제약조건을 걸 수 있는데 FK 를 표현하는 것 또한 가능함

CREATE TABLE teaches
(
  pID varchar(20) CHECK (pID IN (SELECT pID FROM professor)),
  name varchar(20)
);

하지만 위 구문은 이론적으로만 가능하며 실제로는 동작하지 않음. CHECK 구문 안에 sub query 가 들어가게 되면 query 를 처리하는데 비용이 많이들기 때문

Assertion

CREATE ASSERTION <assertion-name> CHECK <predicate>

CREATE ASSERTION myVerifyTotalCredit CHECK
(
  NOT EXISTS
  (
    SELECT s2.sID FROM student s1
    WHERE totalCredit <> (SELECT SUM(credits) FROM takes, course WHERE s1.sID = sID AND course.cID = takes.cID AND grade is not null and grade <> ‘F’)
  )
);

insert, update 할 때마다 해당 assertion 을 만족해야함

assertion 자체만으로 비용이 크게 증가하므로 허용하지 않는 DB가 많음