김용주님의 블로그

DB Chapter 7

13 Jun 2019 |

Ch.7: Constraints and Triggers

1. Foreign Key

다른 table에서 가져온 녀석인데 가져오려면 그 table의 primary key 또는 unique만을 가져올 수 있음. 예시를 갖고 끝까지 봅시다.

CREATE TABLE Books (
   name CHAR(20) PRIMARY KEY,
   publisher CHAR(20)
);

#REAL = float(24)
CREATE TABLE Sells (
   bookstore CHAR(20),
   book CHAR(20),
   price REAL,
   FOREIGN KEY book REFERENCES Books(name)
	  ON DELETE SET NULL
	  ON UPDATE CASCADE
);

여기서 sells의 book은 books의 name에 있는 data를 가져오기 때문에 books의 name에 없는 data는 등록할 수 없음. 만약 이를 어길 시 어떻게 하느냐? 바로 reject.

그러면 원래 있던 data의 값을 바꾸거나(update) 지우면(delete) foreign key로 가져온 data는 어떻게 해야할까? 3가지 policy가 존재

  1. Default Policy: 아예 바꾸는 것이 허용이 안댐
  2. Cascade Policy: 삭제하거나 바꾸면 foreign key에 해당하는 값도 삭제하거나 바꿔야 함
  3. Set-null Policy: 삭제하면 foreign key의 값이 null이 됨.

delete, update의 경우를 따로 설정 가능.

Deferred checking of constraint

Circular Constraints: A가 되기 위해서는 B가 필요, B가 되기 위해서는 A가 필요 마치 deadlock

신규채용 요건: 5년 이상 직장 경험자! 아니 경험을 어디서 쌓냐구요~~

Solution!

동시에 2개를 같이 넣는데 transaction 끝나기 전까지는 다른 애들이 못건드리게 해서 동시에 넣으면 됨. lock을 걸고 풀고~ 이게 DEFERRABLE

default로는 initally immediate인데 initally deferrable로 바꾸면 해결~

2. Constraints on Attribute and Tuple

Check Constraints

CHECK(condition) 의 형식으로 사용. check는 attribute나 tuple이 바뀔때(insert, update) condition을 check함. 새로 바뀌는 값이 condition을 만족하지 못할경우 reject.

Tuple based constraints는 data가 자주 바뀌지만 Attribute based constraints는 attribute가 자주 바뀌지는 않음.

4. Assertion

relation에서 변화가 일어날 때 마다 check하는 것.

CREATE ASSERTION name

CHECK(condition)

Bookstore(name, addr, license)
Students(studentID, name, phone)

CREATE ASSERTION FewBookstore
CHECK(
   (SELECT COUNT(*) FROM Bookstore) <= (SELECT COUNT(*) FROM Students)
);

Bookstore나 Students에 변화가 일어날 때 마다 check

Assertion은 insert, delete, update를 다 check하지만 tuple based check는 insert, update만

DB7-1

5. Trigger

Trigger는 특정한 case만 check함. Assertion은 다하는데 oracle의 경우 assertion대신 triger만 사용

Event(insert, update 등), Condition(where부분), Action(SQL statement) 로 나뉨

Sells(bookstore, book, price)
CheatingBookstores(bookstore)

#책들의 가격이 이전보다 1달러 더 오른 서점들만 cheatingbookstore에 들어감.
CREATE TRIGGER PriceTrig
AFTER UPDATE OF price ON Sells
FOR EACH ROW
WHEN(new.price > old.price + 1.00)
  BEGIN
  INSERT INTO CheatingBookstore VALUES(:new.bookstore);
  END;
.
run

after는 before로도 사용 가능하고, view라면 instead of 로 바뀜. 위의 경우 sells의 price가 update될 때만 check. when은 조건문

after의 경우 update나 insert를 일단 실행을 하고 when에 있는 조건문을 보고 조건문이 맞으면 begin ~ end를 실행.

before의 경우 조건문을 보고 begin ~ end를 실행하고 update나 insert를 실행(after는 확실한데 before는 잘 모르것네요)

. run은 trigger를 DB에 저장시킴.

Related Posts