17. Null and check Constraints, Trigger

2021. 5. 29. 02:31ㆍComputer Sciences/Database

Not-Null constraints

  • 기본적으로 데이터를 입력하지 않은 컬럼에는 null 값이 들어감
  • 이러한 null을 허용하지 않고 반드시 값을 입력하도록 NOT NULL을 지정해줄 수 있음
  • Primary key는 이 키워드를 지정하지 않더라도 적용되어있음
CREATE TABLE Stduio (
	name CHAR(30) PRIMARY KEY,
	address VARCHAR(255),
	presCNo INT REFERENCES MovieExec(certNo) NOT NULL
);

Attribute-Based Checks

  • 속성이 만족해야 하는 조건을 체크함
  • 만족하지 못하는 튜플은 거부함
  • CHECK 키워드 사용
// presCNo가 10만 이상인 경우를 체크함
CREATE TABLE Studio (
	name CHAR(50),
	address VARCHAR(255),
	presCNo INT REFERENCES MovieExec(certNo)
		CHECK (presCNo >= 100000),
		PRIMARY KEY (name)
);
// gender가 F 또는 N인지 체크함
CREATE TABLE MovieStar ( 
    name CHAR(30)
    PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR
        CHECK (gender IN ('F', 'N')), 
    birthdate DATE
);
  • 외래 키의 참조와 CHECK를 통한 방법은 차이가 있음
  • 외래키 참조는 presCNo와 certNo의 변경을 양방향으로 감지할 수 있음
  • CHECK를 통한 방법은 presCNo의 변경만을 감지할 수 있음
CREATE TABLE Stduio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presCNo INT REFERENCES MovieExec(certNo)
);
CREATE TABLE Stduio (
    name CHAR(50) PRIMARY KEY,
    address VARCHAR(255),
    presCNo INT
        CHECK(presCNo IN (SELECT certNo FROm MovieExec))
);

Tuple-Based CHECK Constraints

  • 튜플 자체를 체크하는 제약 조건
CREATE TABLE MovieStar (
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1),
    birthdate DATE,
    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
);

Giving Name to Constraints

  • 제약 조건에 이름을 붙임
CREATE TABLE MovieStar (
    name CHAR(30)
    CONSTRAINT NameIsKey PRIMARY KEY,
    address VARCHAR(255),
    gender CHAR(1)
        CONSTRAINT NoAndro CHECK (gender IN ('F', 'M')),
    birthdate DATE,
        CONSTRAINT RightTitle CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
);

Altering Constraints on Tables

ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;
ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;
ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

ALTER TABLE MovieStar AND CONSTRAINT NameIsKey
    PRIMARY KEY (name);
ALTER TABLE MovieStar ADD CONSTRAINT NoAndro
    CHECK (gender IN ('F', 'M'));
ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey
    CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');

Schema-Level Constraints and Triggers

Assertions

  • 언급된 릴레이션의 모든 변경 시마다 체크함
  • 문법 
    CREATE ASSERTION <name> CHECK(<condition>); DROP ASSERTION <name>
  • 성능 문제로 인해 대부분이 DBMS는 지원하지 않음
  • 아래와 같은 Assertion 시 Studio와 MovieExec을 조인 연산한 뒤 만들어진 모든 튜플에 대해 체크를 하게 됨
CREATE ASSERTION RichPres
    CHECK (NOT EXISTS (SELECT *
    FROM Studio, MovieExec
    WHERE presCNo = certNo
    AND netWorth < 10000000
    )
);

DROP ASSERTION RichPres;

Oracle-Triggers

  • 트리거는 보통 event-condition-action rules 또는 ECA rules라고 불림
  • Event: 어떤 테이블의 SQL 문 입력의 발생을 나타냄
  • Condition: Event가 발생했을 때 부가적으로 지정된 조건을 말함
  • Action: Event가 일어났을 때 Condition을 부합한다면 실행되는 SQL 문을 말함
  • 위 세 가지를 한 묶음으로 트리거라고 부름
  • 전체 트랜잭션 작업에 대해 발생되는 트리거와 각 행에 발생되는 트리거가 있음
// MovieExecTrig가 없으면 만들고 있다면 대체하는 문
// Studio에 INSERT 문이 실행된 후에
// 각 행에 대하여
// 새로 입력된 튜플의 presCNo가 10000 이상이라면
// MovieExec의 certNo 값을 presCNo 값으로 입력함
// MovieExec의 나머지 값은 default가 지정되어 있지 않다면 null로 채워질 것임

CREATE OR REPLACE TRIGGER MovieExecTrig
AFTER INSERT ON Studio
    FOR EACH ROW
        WHEN (new.presCNo > 10000)
            BEGIN
                INSERT INTO MovieExec(certNo)
                VALUES(:new.presCNo);
            END;
  • 문법 
    CREATE [OR REPLACE] TRIGGER <trigger-name>
(AFTER|BEFORE|INSTEAD OF)
    (INSERT|DELETE|UPDATE [OF <attribute>])
        ON <table-name> [REFERENCING
            OLD AS oldTuple
            NEW AS newTuple
            PARENT AS parentTuple ]
                [FOR EACH ROW [WHEN (<condition>) ]]
                    BEGIN
                        <sql-statements> 
                    END;

Triggers in SQL3

  • 문법 
    CREATE TRIGGER NetWorthTrigger
AFTER UPDATE [OF netWorth]
    ON MovieExec
    REFERENCING
        OLD ROW AS OldTuple,
        NEW ROW AS NewTuple
            FOR EACH ROW
                WHEN(OldTuple.netWorth > NewTuple.netWorth)
                    UPDATE MovieExec
                    SET netWorth = OldTuple.netWorth
                    WHERE certNo = NewTuple.certNo;
  • Oracle Trigger와 SQL3 표준 문법은 거의 비슷함

PostgreSQL 트리거의 스페셜 변수

  • NEW
  • OLD
  • TG_NAME
  • TG_WHEN: BEFORE or AFTER
  • TG_LEVEL: ROW or STATEMENT
  • TG_OP: INSERT, UPDATE or DELETE
  • TG_RELID, TG_RELNAME
  • TG_NARGS, TG_ARGV[]

PostgreSQL 트리거 예시

  • 예제 트리거는 다음을 보장함
    • 테이블에 행이 삽입되거나 업데이트 됐을 때
      • 작업을 수행하는 유저와 당시 시간이 행에 삽입됨
      • employee의 name이 주어졌는지와 salary가 양수값인지를 확인함
CREATE TABLE emp(
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $$
    BEGIN
        - check that empname and salary are given
        IF NEW.empname IS NULL THEN 
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname; 
        END IF; 
        - Who works for us when she must pay for it? 
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;  
        END IF;  
        - Remember who changed the payroll when   
        NEW.last_date := current_timestamp; 
        NEW.last_user := current_user;
        RETURN NEW;    
    END; $$
LANGUAGE plpgsql; 

CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE ON emp 
    FOR EACH ROW   
        EXECUTE PROCEDURE emp_stamp(); 
        
// salary 가 NULL 이므로 에러가 발생함
INSERT INTO emp VALUES ('Katie Couric', NULL, NULL, NULL);
// last_date와 last_user에는 current_timestamp, current_user가 삽입됨
INSERT INTO emp VALUES ('Katie Couric', 5000, NULL, NULL);
  • 로그를 남기는 트리거
CREATE TABLE emp (
    empname text NOT NULL,
    salary integer
); 

CREATE TABLE emp_audit(
    operation char NOT NULL,
    stamp timestamp NOT NULL,
    userid text NOT NULL,
    empname text NOT NULL, 
    salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit()
RETURNS TRIGGER AS $$ 
    BEGIN   
        -   
        - Create a row in emp_audit to reflect the operation performed on emp, 
        - make use of the special variable TG_OP to work out the operation   
        -     
        IF (TG_OP = 'DELETE') THEN      
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;   
            RETURN OLD;     
        ELSIF (TG_OP = 'UPDATE') THEN    
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*; 
            RETURN NEW; 
        ELSIF (TG_OP = 'INSERT') THEN  
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;   
            RETURN NEW;       
        END IF;   
        RETURN NULL; - result is ignored since this is an AFTER trigger  
    END; 
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW 
        EXECUTE PROCEDURE process_emp_audit();

 

 

Uploaded by Notion2Tistory v1.1.0

'Computer Sciences > Database' 카테고리의 다른 글

18. System Aspects of SQL  (0) 2021.06.07
16. Primary and foreign Key Constraint  (0) 2021.05.29
15. Index and View  (0) 2021.05.23
14. Transaction  (0) 2021.05.22
13. Database Modifications  (0) 2021.05.22

관련글

댓글 0

티스토리툴바