[DB개념] :: Constraints and Triggers (제약과 트리거)

Constraints and Triggers

Necessity

무결성 제약조건(Integrity Constraint)

• 우리가 어떤 특정한 Relation을 삽입할 때 혹은 DB가 변할 때 큰 문제중 하나에 직면하게 된다.
  (Domain에 벗어나는 값이라던가, 말이 안되는 값이라던가 하는)
  
• 즉, Update, Insert 같은 Modification 후에 테스트를 하며 항상 반복적으로 확인해야 할 필요가 있다. 
  
• SQL은 '무결성 제약(Integrity Constraint)' 이라는, 제약조건을 표현하는 다양한 기법을 제공한다. 
  
  1. Keys
  2. Referential Integrity ( Foreign-Key Constraint )
  3. Domain & NOT NULL
  4. CHECK
  5. Assertions
  6. Triggers

• 프로그래밍 작업이 복잡해지고, 반복해서 구현해야 하고, 무결성 제약 조건들 간에 서로 충돌이 발생할 수도 있다는 단점이 있지만,
  저장된 데이터가 좀 더 실세계의 의미에 충실하게 되며, 에러의 발생 여지가 크게 감소된다는 장점이 있다.

1. Primary-Keys and Foreign-Keys

Keys

• DB의 가장 중요한 제약은, Key를 지정하는 'Column의 선언' 에 있다.
• Keys 제약도 다른 많은 제약들 처럼 CREATE 문에서 선언된다.
  두 가지 종류 : Primary Key(주 키, 이하 PK), UNIQUE
  

Primary Key

• Key(s)중 user에 의해 선정된 Key
• Relation당 최대 하나만 보유할 수 있다.
• CREATE TABLE 문에서 PK를 선언하는 두 가지 방법
  1. Column 선언 후 해당 Column이 PK라고 선언하기
  2. Column 선언을 모두 마치고 마지막에 특정 Column을 가리키며 PK라고 선언하기
     ( 둘 이상을 묶어서 PK로 사용하고 싶으면, 2번 방법을 쓴다. ) 
     

< 1번 방법 >

CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,       <-
    name  VARCHAR(20),
    dept  VARCHAR(30),
    grade  INT
);


< 2번 방법 >

CREATE  TABLE  Students (
    s_id  INT,
    name  VARCHAR(20),
    dept  VARCHAR(30),
    grade  INT,
    PRIMARY KEY  ( s_id, name )       <-
);

Student Table의 학번 (2번은 학번과 이름 모두)을 PK로 지정.

UNIQUE

• PK가 아닌 UNIQUE를 쓰는 기법
• PK와 의미는 거의 유사하지만 다른 두가지는
  1. PK는 Relation당 한개지만, UNIQUE 개수는 상관없다.
  2. NULL값 허용 ( NULL을 값으로 본다면 중복이 발생한다.하지만 허용함 )
     

CREATE  TABLE  Students (
    s_id  INT  UNIQUE,                   <-
    name  VARCHAR(20)  UNIQUE,       <-
    dept  VARCHAR(30),
    grade  INT
);

2. Referential Integrity ( Foreign-Key Constraint )

Concepts about Foreign-key

• 다음으로 중요한 제약은, '특정 Column은 말이 되야 한다는 것(Make Sense)' 이다.
  즉, 참조 하는 쪽의 Column 값은 참조 받는 쪽의 Column 중에서만 값이 존재해야 한다.
• SQL에서는, Foreign key(외래 키, 이하 FK)라는 특수한 Key를 둠으로 써 Referential Integrity를 준수하는데,
  이는 FK의 Column(s)값이 다른 Relation의(같은 Relation도 가능하긴 함) PK값을 참조하는 것이다. 
  
• 이 선언의 조건은 두 가지로 볼 수 있는데,
  
  1. 참조 받는, 두 번째 Relation의 Column은 반드시 Unique이거나 PK여야 한다.
  2. 참조 하는 Relation의 Column 내 값은 참조 받는 Relation의 어느 Tuple에라도 반드시 존재해야 한다. 
     더 정확하게 말하면, Relation R1의 Column C1의 Tuple t1, Relation R2의 Column C2의 Tuple t2에 대해 
     t1[C1] = t2[C2]를 만족해야 한다. (여기서 C1은 FK, C2는 PK/UNIQUE) 
     

How to use it

CREATE 시 표기 법

1. column_name data_type REFERENCES < table > ( < column_name > )

2. Column들을 다 명시한 뒤 맨 마지막에 
   FOREIGN KEY ( < column_name > ) REFERENCES < table > ( < column_name > ) 
   

CREATE  TABLE  Department (
    dept_name  VARCHAR(30)  PRIMARY KEY,
    building  VARCHAR(20),
    budget  INT
);


CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30)  REFERENCES  Department ( dept_name ),            <-
    grade  INT
);


CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30),
    grade  INT,
    FOREIGN  KEY  ( dept )  REFERENCES  Department ( dept_name )         <-
);

Students table의 dept column을 Department table의 PK인 dept_name을 참조하게 하는 외래키로 설정.

• 한 가지 예외가 있다면, NULL값도 참조해야 하는 건 아니다. ( 받는 쪽은 PK라 NULL값이 없지만서도 )

Maintaining Referential Integrity

과연 이 까다로운 제약을 '어떻게' 유지할까? 
Implementor는 다음과 같은 세 가지 방법으로 유지할 수 있다.

• The Default Policy : Reject Violating Modifications ( DEFAULT )
  디폴트 정책으로, 시스템 차원에서부터 거절(Reject)하는것이다.
  
  1. NULL이 아니면서 t2[C2]도 아닌 것을 Insert하려고 할 때
  2. NULL이 아니면서 t2[C2]도 아닌 것으로 Update하려고 할 때
  3. C2의 Tuple 중 C1에 등장하는 값을 Delete하려고 할 때
  4. C2의 Tuple 중 C1에 등장하는 값을 Update하려고 할 때

• The Cascade Policy 
  계단식 정책으로, 참조받는 값이 변하면, 참조하는 값도 같이 따라서 변하게 설정하는 것이다. 
  즉, C2의 값이 Update나 Delete되면 C1의 값도 같이 Update나 Delete된다. 
  

CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30)  REFERENCES  Department ( dept_name )
        ON UPDATE CASCADE,                                                                   <-
    grade  INT
);

• The Set-Null Policy 
  FK에 독립적으로 설정함으로써 지워진 값은 NULL로 지정하는 것이다. 
  

CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30)  REFERENCES  Department ( dept_name )
        ON DELETE SET NULL,                                                                   <-
    grade  INT
);

Delete에 있어선 Set Null을, Update에 있어선 Cascade를 설정하는게 유리하다. 

CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30)  REFERENCES  Department ( dept_name )
        ON UPDATE CASCADE                                                                    <-
        ON DELETE SET NULL,                                                                   <-
    grade  INT
);

3. Domain & Not-Null Constraints

• 각 Column의 값들에 대한 Data type, Default value, 범위 등을 제한/결정한다.
• 또한 해당 Column의 맨 마지막에 NOT NULL을 기재하면, NULL값을 불허한다. 
  이 때엔, Insert에서 모든 Column에 대해 명시해준다거나 Tuple의 모든 값들을 일일이 명시할 필요가 있다.
  
• 위에서 살펴본 Set-Null policy는 사용 불가이다. 
  

CREATE  TABLE  Students (
    s_id  INT  PRIMARY KEY,
    name  VARCHAR(20),
    dept  VARCHAR(30)  REFERENCES  Department ( dept_name )
    grade  INT  NOT NULL                                                                <-
);

4. CHECK

• 새로운 값을 받으면, 그 때마다 check하는(말 그대로 CHECK) 역할이며 
  주어진 Condition에 부합하는 지 확인하는, 일종의 New Value에 대한 제약이다.
  
• Column-based와 Tuple-based 두 가지가 있지만 거의 유사하여 구분하진 않겠다. 
  
• 하지만 Insert나 Update시 WHERE cluase에서 조건을 명확히 주면 되기도 하고 
  새로운 값에 대해 일일이 확인하는 작업이라 무거워 진다는 단점이 있다.
• Relation마다 걸 수 있으며, 다른 Relation에겐 보이지 않는다(Invisible). 
  

CREATE  TABLE  Employees (
    name  VARCHAR(20),
    age  INT,
    gender  CHAR(1)  CHECK ( gender  IN  ( 'F', 'M' ) ),             <-
    part  VARCHAR(10)
);

5. Assertions

• General Constraint 라고도 불리우며, SQL expression상에서 
  항상 True만 나오게 하는 boolean-value이다.
  

CREATE ASSERTION < name > CHECK ( < condition > )

• Condition은 항상 True여야 한다. ( False로 오게 하는 어떤 Modification이든 전부 Reject) 
  
• Assertion에서 언급된 relation의 어떠한 change라도 발생하면 그 즉시 발동된다.
• 위에서 살펴본 CHECK와의 차이점
  • CHECK는 선언되고 나타난 그 Relation 안에서 참조를 하는 거라면,
  • ASSERTION은 독립적으로 생성하여 어떤 Column이든 상관없이 수행한다. 
    즉 Condition에 Subquery가 등장할 수도 있다는 말이다. 
    또한 Boolean-Value를 갖기 때문에 Condition의 결과를 Aggregate하여 상수와의 비교를 하거나 
    NOT EXISTS같은 표현도 사용할 수 있다. 
    

CREATE  ASSERTION  Graduate_Yet  CHECK
    (  NOT  EXISTS 
        (  SELECT  *
           FROM  Students
           WHERE  grade  =  4
        )
    );

Student에 4학년이 없도록 하는 Assertion 생성.

• DROP을 이용해 제거할 수 있다.
  

DROP  ASSERTION  Graduate_Yet;

6. Triggers

What is Trigger?

Triggers ( Event-Condition-Action Rules / ECA Rules )는 이전에 살펴본 제약들과 3가지 측면에서 다르다. 

1. DB 프로그래머로부터 어떠한 event가 발생할 때만 발동한다( 깨어난다 ). ( 보통은 Insert, Delete, Update )
2. 깨어나면, event 실행 전 Condition을 확인한다. 이 때 Condition을 충족하지 않으면, 가만히 냅둔다.
3. Condition을 충족하면, 특정 action을 취한다. ( 다른곳으로 옮긴다던가, 실행취소한다던가 )

Kinds of Options for Triggers in SQL

SQL에서, Trigger는 사용자에게 Event, Condition, Action 부분에서 다양한 옵션을 제공한다. 

1. Action은 Trigger Event 전/후로 실행될 수 있다.
2. Action은 Tuple의 취해지기 전(old)값 혹은 새로운(new)값을 참조할 수 있다.
   
   • 여기서 old, new개념은 Modification기준이 아닌 Trigger 발동 기준
3. Update Event는 특정 Column(들)에 대해 한정적일 수 있다.
4. Condition은 WHEN clause를 통해 기재된다.
5. 한 번에 한 Tuple(Row 단위) 혹은 한 번에 모든 Tuple(Statement 단위)에 대한 옵션을 취할 수 있다.

CREATE  TRIGGER  Graduate
AFTER  UPDATE  OF  grade  ON  Students
REFERENCING
    NEW TABLE  AS  nTable
FOR  EACH  STATEMENT
WHEN  (  4  >  (  SELECT  grade  FROM  nTable  ) )
BEGIN
    DELETE  FROM  Students
    WHERE  (  s_id, name, dept, grade )  IN  nTable;
END;

Students 에 학년이 +1 될 때마다(Update 될 때마다), 학년이 4를 초과하면 삭제하는 Trigger.

6. Instead-Of Trigger를 통해, 좀 더 간단히 표현할 수 있다. 
   상업DB에서도 많이 서포트 되는 것이며, BEFORE/AFTER 대신 INSTEAD OF를 사용한다.
   

CREATE  VIEW  Marvel  AS
    SELECT  title, year
    FROM  Movies
    WHERE  studioName  =  'Marvel';


CREATE  TRIGGER  MarvelInsert
INSTEAD  OF  INSERT  ON  Marvel
REFERENCING  NEW  ROW  AS  nRow
FOR  EACH  ROW
INSERT  INTO  Movies ( title, year, studioName )
VALUES ( nRow.title, nRow.year, 'Marvel' );

View에 삽입되는 tuple들을 view 대신 Base Table로의 삽입으로 대체하는 Trigger.

마침.