Constraints and Triggers
Necessity
무결성 제약조건(Integrity Constraint)
- 우리가 어떤 특정한 Relation을 삽입할 때 혹은 DB가 변할 때 큰 문제중 하나에 직면하게 된다.
(Domain에 벗어나는 값이라던가, 말이 안되는 값이라던가 하는) - 즉, Update, Insert 같은 Modification 후에 테스트를 하며 항상 반복적으로 확인해야 할 필요가 있다.
- SQL은 '무결성 제약(Integrity Constraint)' 이라는, 제약조건을 표현하는 다양한 기법을 제공한다.
- Keys
- Referential Integrity ( Foreign-Key Constraint )
- Domain & NOT NULL
- CHECK
- Assertions
- 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를 선언하는 두 가지 방법
- Column 선언 후 해당 Column이 PK라고 선언하기
- 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와 의미는 거의 유사하지만 다른 두가지는
- PK는 Relation당 한개지만, UNIQUE 개수는 상관없다.
- 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값을 참조하는 것이다. - 이 선언의 조건은 두 가지로 볼 수 있는데,
- 참조 받는, 두 번째 Relation의 Column은 반드시 Unique이거나 PK여야 한다.
- 참조 하는 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 시 표기 법
- column_name data_type REFERENCES < table > ( < column_name > )
- 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)하는것이다.- NULL이 아니면서 t2[C2]도 아닌 것을 Insert하려고 할 때
- NULL이 아니면서 t2[C2]도 아닌 것으로 Update하려고 할 때
- C2의 Tuple 중 C1에 등장하는 값을 Delete하려고 할 때
- 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가지 측면에서 다르다.
- DB 프로그래머로부터 어떠한 event가 발생할 때만 발동한다( 깨어난다 ). ( 보통은 Insert, Delete, Update )
- 깨어나면, event 실행 전 Condition을 확인한다. 이 때 Condition을 충족하지 않으면, 가만히 냅둔다.
- Condition을 충족하면, 특정 action을 취한다. ( 다른곳으로 옮긴다던가, 실행취소한다던가 )
Kinds of Options for Triggers in SQL
SQL에서, Trigger는 사용자에게 Event, Condition, Action 부분에서 다양한 옵션을 제공한다.
- Action은 Trigger Event 전/후로 실행될 수 있다.
- Action은 Tuple의 취해지기 전(old)값 혹은 새로운(new)값을 참조할 수 있다.
- 여기서 old, new개념은 Modification기준이 아닌 Trigger 발동 기준
- Update Event는 특정 Column(들)에 대해 한정적일 수 있다.
- Condition은 WHEN clause를 통해 기재된다.
- 한 번에 한 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.
- 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.
마침.
'IT > Database Concepts' 카테고리의 다른 글
| [DB개념] :: Index Structures (인덱스 구조) (2) | 2018.04.13 |
|---|---|
| [DB개념] :: Representing Data Elements (데이터 엘리먼트의 표현) (0) | 2018.04.10 |
| [DB개념] :: Define on SQL about kinds of Relations (다양한 릴레이션에 대한 SQL) (0) | 2018.04.04 |
| [DB개념] :: SQL (Structured Query Language, 구조화 질의어) (0) | 2018.04.03 |
| [DB개념] :: Extended Operators of Relational Algebra (확장 관계대수) (1) | 2018.04.02 |
차트
소소한 일상 C코드 DB 항상 행복하게^-^★