SQL - 제약과 트리거 1

제약과 트리거는 active element라고 하는데 일반적으로 우리가 sql문을 쓰면 실행시키기 전까지는 실행되지 않지만 제약과 트리거는 조건에 어긋나면 실행되기 때문이다.

<Constraints>

• Integrity constraints (무결성 제약조건)

1. Key constraints: PRIMARY KEY, UNIQUE (Sesion 2.3.6)  (개체 무결성)

2. Foreign-key constraints (Referential integrity constraints): REFERENCES (참조 무결성)

 

• Constraints on attributes, tuples, and relations

1. Attribute constraints: NOT NULL

2. Attribute-based CHECK constraints

3. Tuple-based CHECK constraints

 

• Interrelations constraints (global constraints) (잘 사용하지 않음, 모든 테이블에 대해 검사를 필요로 해 속도 저하 발생)

1. (SQL2-only) Assertions1  

 

• Triggers

1. Oracle Triggers (a substitute for assertions)

2. SQL3 triggers and assertions.

 

Key Constraints - primary key and unique

비슷한 개념이지만 primary key는 null값을 아예 금지하고 unique의 경우 있으면 곤란하긴 해도 이론 상 null값을 가질 수 있다고 한다. 예를 들어 학교를 생각해보면 학생들의 학번과 주민번호가 있을 것이다. 여기서 주민번호는 민감한 개인정보일 수 있으니 학번을 primary key로 선언하고 주민번호를 unique로 사용하는 것이다. 학번이 다루기도 쉽고 주민번호에 비해 짧기때문이기도 하다. 또한, 중복되는 정보가 있을 때 비교해서 확인할 수도 있다.

 

CREATE TABLE MovieStar (

    Name CHAR(30) PRIMARY KEY,

    Address VARCHAR(255) UNIQUE,

    Gender CHAR,

    Birthdate DATE

);

 

Primary key나 unique가 하나일 경우 위와 같이 할 수 있지만 여러 개가 되는 경우 아래와 같이 밖에 사용하지 못한다.

 

CREATE TABLE MovieStar (

    Name CHAR(30),

    Address VARCHAR(255),

    Gender CHAR,

    Birthdate DATE,

    PRIMARY KEY(Name, *),

    UNIQUE(address, *)

);

현재 데이터베이스의 기술로는 위와 같이 선언만 하는 것은 잘 안 될 가능성이 높고 인덱스를 활용해 구현을 해주어야한다고 한다.

CREATE UNIQUE INDEX YearIndex ON Movie(year);

Foreign key 사용하는 방법도 유사하다. 하나의 foreign key만을 선언을 할 것이면 왼쪽과 같이 사용해도 되지만 여러 개가 필요한 경우 오른쪽처럼 사용해야한다.

위와 같이 foreign key를 사용하게 되면 밑의 studio table의 값을 추가하려고 하면 위에 MovieExec에 가서 certNo가 동일한 것이 있는지 찾게 되는 것이다. 없을 경우 추가가 안 되는 것이다. 따라서 초기화나 삭제를 하는 과정에서 순서가 중요하게 된다. 예를 들어 MovieExec에서 101번을 삭제하려고 하는데 studio에서 101번을 참조하고 있으면 Exec에서 101이 삭제되면 참조무결성이 깨져버리므로 삭제를 막게 됩니다. 따라서 삭제를 하기 위해서는 Studio 즉, 참조 중 인 테이블의 값을 먼저 삭제하고 그 뒤에 참조가 되고 있는 테이블의 값을 삭제하는 것이 올바른 순서이다. 초기화의 경우도 똑같이 진행된다. 그리고 초기화를 한 상태에서 값을 입력하려고 하면 Studio부터 입력하는 것이 아닌 Exec부터 입력을 해주어야 입력이 될 것이다. 초기화가 되었다면 Exec는 비어있는 테이블일 것이기 때문이다.

 

1. The Default Policy: Reject violating modifications (수정 불가)

2. The Cascade Policy (참조되고 있는 것을 수정하면 참조한 값도 수정)

3. The Set-Null Policy (참조되고 있는 값을 삭제하면 참조한 값을 null로)

 

CREATE TABLE Studio (

name CHAR(30) PRIMARY KEY,

address VARCHAR(255),

presCNo INT,

FOREIGN KEY (presCNo) REFERENCES

    MovieExec(certNo)

ON DELETE SET NULL

ON UPDATE CASCADE

);

Delete에 대해서는 set null 사용 update에 대해서는 cascade를 사용한다는 뜻이다. 이는 디비 설계자의 마음이 아닌 실제 세계에서 어떤 식으로 돌아가는 지를 확인하고 사용해야한다. 이러다 보면 닭이 먼저냐 달걀이 먼저냐의 문제가 발생하는 상황이 올 수도 있는데 밑의 상황이 그와 같다.

SQL - 제약과 트리거 2에서 계속 하겠다.