16. Primary and foreign Key Constraint

Constraints

• 기존의 SELECT, UPDATE 과 같은 절은 입력해주어야 실행됨

• 제약 조건은 한 번 설정을 해 두면 상황에 따라 적절하게 실행됨

Integrity constraint

1. 키 제약 조건: PRIMARY KEY, UNIQUE

2. 외래 키 제약 조건 (Referential integrity constraints): REFERENCES

Constraints on attributes, tuples, and relations

1. 속성 제약 조건: NOT NULL

2. Attribute-based CHECK constraints

3. Tuple-based CHECK constraints

Interrelations constraints(global constraints)

1. (SQL2-only) Assertions

Triggers

1. Oracle Triggers

2. SQL3 triggers and assertions

Key Constraints

Primary Key

• 테이블에서 튜플을 구분하는 키

• 해당 속성은 해당 테이블에서 유일한 값을 가짐을 보장함

• NULL 값 불가능

CREATE TABLE MovieStar (
     name CHAR(30) PRIMARY KEY,
     address VARCHAR(255) UNIQUE,
     gender CHAR,
     birthdate DATE
);

Unique

• 해당 속성값의 유일함을 보장하는 면에서 Primary Key와 동일

• NULL 가능

CREATE TABLE MovieStar (
    name CHAR(30), 
    address VARCHAR(255),
    gender CHAR,  
    birthdate DATE,
    PRIMARY KEY(name), 
    UNIQUE(address)
);

• 현재 기술로 PRIMARY KEY나 UNIQUE 내부적인 구현은 대부분 인덱스를 통해 구현하는 경우가 많음

CREATE INDEX YearIndex ON Movie(year); CREATE UNIQUE INDEX YearIndex ON Movie(year);

Foreign Keys

CREATE TABLE MovieExec (
    name CHAR(30),
    address VARCHAR(255), 
    certNo INT PRIMARY KEY,
    netWorth INT
);

CREATE TABLE Studio (  
    name CHAR(30) PRIMARY KEY,  
    address VARCHAR(255),  
    presCNo INT REFERENCES MovieExec(certNo)
);

// 또는 아래와 같이 지정할 수 있음
CREATE TABLE Studio ( 
    name CHAR(30) PRIMARY KEY,
    address VARCHAR(255),
    presCNo INT, 
    FOREIGN KEY (presCNo) REFERENCES MovieExec(certNo)
); 
    

• MovieExec과 Studio는 다대일 관계

• MovieExec의 키를 Studio에 넘겨준 모양과 같아짐

• Studio에 데이터를 추가하려고할 때 MovieExec의 certNo에 presCNo가 없다면 참조 무결성 위배로 리젝트됨

• 즉, Studio에 튜플을 추가하기 위해선 먼저 MovieExec의 certNo에 값이 먼저 있어야 함

• 그리고 MovieExec의 튜플을 지우려면 Studio에서 해당 키를 참조하고 있는 컬럼을 먼저 지워야 함

• DB 초기화 시 주의해야 함 → MovieExec에 데이터가 없으면 Studio에 입력되는 모든 INSERT 절은 리젝트되기 때문

외래 키 제약 조건이 위배됐을 때 일어나는 일(부모 테이블에서 일어남)

1. default policy: 위배된 수정 작업을 리젝트함

2. Cascading policy: 관계된 데이터를 함께 변경함

3. Set-Null policy: Null로 변경함

정책 선택

• 문법

  ON [DELETE, UPDATE] [CASCADE, SET NULL, NO ACTION, SET DEFAULT]

• 용례

  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 절 실행 시엔 NULL로 세팅함
  • UPDATE 절 실행 시엔 Cascading함
  • 이 기능은 적극적으로 사용되지는 않음

설계 결정에서 맞는 정책

• 상황에 맞게 정책을 설립해야 함

제약 조건의 확인 미루기

• 두 테이블간에 서로 외래 키 제약조건을 설정한 경우 서로 테이블에 데이터가 없을 경우 두 테이블 모두 데이터를 입력할 수 없는 상황이 발생함

Circular Constraints

CREATE TABLE MovieExec (
    name CHAR(30),
    address VARCHAR(255), 
    certNo INT PRIMARY KEY REFERENCES Studio(presCNo),
    netWorth INT
);

CREATE TABLE Studio (
    name CHAR(30) PRIMARY KEY, 
    address VARCHAR(255), 
    presCNo INT UNIQUE REFERENCES MovieExec(certNo)
);

• 일반적인 경우 위와 같은 테이블 생성은 문제가 됨

솔루션

1. Studio와 MovieExec의 두 삽입 절을 하나의 트랜잭션으로 그룹핑함

2. SQL 시스템에 트랜잭션이 커밋된 후에 제약 조건을 체크하도록 알림

DEFERRABLE

• 기본 키, 외래 키 또는 다른 제약 조건에 DEFERRABLE 또는 NOT DEFERRABLE(default) 선언

DEFERRABLE [INITIALLY IMMEDIATE]

• DEFERRABLE 제약 조건은 DEFERRED로 변경할 수 있음

예시

CREATE TABLE chicken (cID INT PRIMARY KEY, eID INT);
CREATE TABLE egg     (eID INT PRIMARY KEY, cID INT);

ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
    FOREIGN KEY (eID) REFERENCES egg(eID)
    DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE egg ADD CONSTRAINT eggREFchicken
    FOREIGN KEY (cID) REFERENCES chicken(cID)
    DEFERRABLE INITIALLY DEFERRED;
    
START TRANSACTION;
INSERT INTO chicken VALUES (1, 2);
INSERT INTO egg VALUES (2, 1);
COMMIT;

• 위와 같이 INITIALLY DEFERRED로 DEFERRABLE을 선언하면 트랜잭션에서 처음 INSERT 절을 평가할 때 제약 조건을 체크하지 않고 데이터를 삽입함

• 그리고 COMMIT하기 직전에 각 테이블을 확인하여 제약 조건을 만족하는지를 평가함

• 만약 DEFFERED가 아니라 IMMEDIATE라면 트랜잭션 내부에서 실행되더라도 바로 제약 조건을 평가하므로 주의

 

 

Uploaded by Notion2Tistory v1.1.0