[Database] 무결성 제약조건

Computer Science/DB & SQL

[Database] 무결성 제약조건

HJChung 2021. 2. 24. 20:25

1. 무결성 제약조건이란

데이터 무결성: 데이터의 정확성 또는 유효성을 의미
무결성 제약조건: 데이터베이스 상태가 만족시켜야 하는 조건이자 사용자에 의한 데이터 베이스 갱신이 데이터베이스의 일관성을 깨지 않도록 보장하는 수단
무결성 제약조건의 목적: 일관된 데이터 베이스 상태를 정의하는 규칙들을 명시적으로 정의하여서 권한을 가진 사용자들로부터 데이터베이스의 정확성을 지키는 것
무결성 제약조건의 장점: 스키마를 정의할 때 일관된 데이터베이스 상태를 정의하는 규칙들을 한 번만 명시하면 되고, 데이터베이스가 갱신 될 때 DBMS가 자동적으로 이러한 일관성 조건을 검사해주므로 응용 프로그램단에서 이를 일일이 신경써주지 않아도 된다.

2. 무결성 제약조건의 종류

도메인 제약조건: 각 애트리뷰터 값이 반드시 원자값이어야 하며, 데이터 형식 등 값들의 유형을 정해줄 수 있다. 도한 애트리뷰터의 default 값을 정하고, null을 허용할 것인지 아닌지 등도 정할 수 있다. 이렇게 도메인의 조건을 명시해주면 DBMS는 튜플이 삽입되거나 수정 될 대마다 도메인 제약조건을 검사하여, 도메인 제약조건을 위배하는 연산은 거절한다.
키 제약조건: 키 애트리뷰트에 중복된 값이 존재해서는 안된다는 조건. 즉, 릴레이션을 정의할 때 키본 키로 정의하거나 UNIQUE를 명시한 애트리뷰트에는 중복된 값이 허용되지 않는다.
기본 키와 엔티티 무결성 제약조건: 기본 키를 구성하는 애트리뷰트가 null 값을 가지면 각 튜플들을 식별 할 수 없데 되므로 두 개 이상의 튜플이 동일한 기본 키 값을 가질 수 없으며 릴레이션의 기본 키를 구성하는 어떤 애트리뷰트도 null 값을 가질 수 없다.
외래 키와 참조 무결성 제약조건: 릴레이션 R2의 외래 키(Foreign Key)가 릴레이션 R1의 기본 키를 참조할 때, 참조 무결성 제약조건은 두 릴레이션의 연관된 튜플들 사이의 일관성을 유지하는데 필요하며 다음의 두 가지 조건 중 하나를 만족해야 한다.
1. 외래 키의 값은 R1의 어떤 튜플의 기본 키 값과 같다.
2. 외래 키가 자신을 포함하고 있는 릴레이션의 기본 키를 구성하고 있지 않으면 null 값을 가진다.

3. DBMS의 무결성 제약조건 유지

앞서 DBMS가 무결성 제약조건을 검사해준다고 하였다. DBMS는 각각의 갱신 연산(CRUD)에 대하여 데이터베이스가 무결성 제약조건들을 만족하도록 필요한 조치를 취한다. DBMS는 어떠한 조치를 취해서 무결성을 유지하는 것일까?

대표적인 예시로, 14. Python ORM - Flask-SQLAlchemy에서 사용해 본 적이 있는 아래의 관계 데이터베이스 인스턴스를 사용하여 살펴보자.

post 릴레이션의 user_id 애트리뷰트가 user 릴레이션의 기본 키인 id를 참조하는 외래키이다. 그러므로 user가 참조된 릴레이션, post가 참조하는 릴레이션이 된다.

1) INSERT 인 경우(삽입)

릴레이션에 새로 삽입되는 데이터는 기본 키 값에 따라서 도메인 제약조건, 키 제약조건, 엔티티 무결성 제약조건 등을 위배할 수 있다. 특히 참조하는 릴레이션(post)의 경우에는 위의 3가지 제약조건 뿐만 아니라 참조 무결성 제약조건도 위배할 가능성이 있다. 이 조건들을 위배하는 삽입 연산은 DBMS가 거절함으로써 무결성을 유지한다.

2) DELETE인 경우(삭제)

참조하는 릴레이션(post)는 제약조건 위배가 되지 않지만 참조되는 릴레이션(user)는 참조 무결성 제약조건을 위배할 수 있다. 왜냐하면 post에서 user의 특정 튜플을 참고하고 있는 것이 있다면 이게 삭제되어버리면 더 이상 존재하지 않는 user의 id를 참조하고 있는 셈이 되기 때문이다. 그래서 이럴 경우 DBMS는 몇 가지 옵션을 제공한다.

제한 (restricted): 위배를 일으킬 수 있는 삭제 연산은 거절한다.
연쇄(cascade): 참조되는 릴레이션에서 튜플을 삭제하고, 참조하는 릴레이션에서 이 튜플을 참조하는 튜플들도 다 삭제한다.
널값(nullify): 참조되는 릴레이션에서 튜플을 삭제하고, 참조하는 릴레이션에서 이 튜플을 참조하는 튜플들의 외래키에 null값을 넣는다.
디폴트값 넣기: 참조되는 릴레이션에서 튜플을 삭제하고, 참조하는 릴레이션에서 이 튜플을 참조하는 튜플들의 외래키에 default값을 넣는다.

3) UPDATE인 경우(수정)

만약 기본 키나 외래 키를 수정하고자 하는 것이면 2)DELETE인 경우 옵션과 같은 방식이 적용된다.

또는 기본 키나 외래 키 이외의 애트리뷰트 값을 수정하고자 하는 것이면 참조 무결성 제약조건을 위배하지 않기 때문에 나머지 제약조건을 만족하는지만 검사한다.

4, 데이터 정의어와 무결성 제약조건

SQL의 구성요소에는 데이터 정의어, 데이터 조작어, 데이터 제어어 등이 있다. 이 중 사용자는 데이터 정의어를 통해 무결성 제약조건을 포함하여 스키마를 생성할 수 있다. 그래서 데이터 정의어를 하나씩 살펴보면서 어떻게 데이터 정의어로 무결성 제약조건을 명시할 수 있는지 배워보았다.

1) 애트리뷰트의 제약 조건

릴레이션의 정의시에 다양한 제약 조건을 추가할 수 있다.

NOT NULL: 애트리뷰트는 기본적으로 null 값을 가지기 때문에 null값을 허용하지 않으려면 NOT NULL을 명시해야한다.
UNIQUE: 동일한 애트리뷰트 값을 갖는 튜플이 두 개 이상 존재하지 않도록 보장한다.
DEFAULT: 애트리뷰트에 null값 대신 특정 값을 default로 지정할 수 있다.
CHECK: 한 애트리뷰트가 가질 수 있는 값의 범위를 지정한다.

CREATE TABLE EMPLOYEE
	(EMPNO INTEGER NOT NULL, #NOT NULL
    EMPNAME CHAR(10) UNIQUE, #UNIQUE
    TITLE CHAR(10) DEFAULT '사원', #DEFAULT
    SALARY INTEGER CHECK (SALARY < 6000000), #CHECK
    DNO INTEGER CHECK (DNO IN (1, 2, 3, 4, 5)), #CHECK
    )

2) 기본 키 제약조건

릴레이션을 생성 할 대 기본 키 제약조건은 해당 릴레이션의 기본 키가 어떤 애트리뷰트인지 명시하고, 기본 키는 널 값을 가지지 않도록 명시해주어야 한다.

CREATE TABLE EMPLOYEE
	(EMPNO INTEGER NOT NULL,
    EMPNAME CHAR(10) UNIQUE,
    TITLE CHAR(10) DEFAULT '사원',
    SALARY INTEGER CHECK (SALARY < 6000000),
    DNO INTEGER CHECK (DNO IN (1, 2, 3, 4, 5)),
	PRIMARY KEY(EMPNO) #기본 키 제약조건
    )

3) 참조 무결성 제약조건

참조 무결성 제약조선은 릴레이션을 정의하면서 명시하며 한 릴레이션에 들어 있는 Foreign key의 수만큼 참조 무결성 제약 조건을 명시 할 수 있다.

또한 위에서 살펴본 것처럼 DBMS의 무결성 제약조건 유지를 위해 참조되는 릴레이션의 튜플이 삭제되거나 수정 될 때, 참조하는 릴레이션에서 어떻게 동작할 것인가를 명시한다.

CREATE TABLE EMPLOYEE
	(EMPNO INTEGER NOT NULL,
    EMPNAME CHAR(10) UNIQUE,
    TITLE CHAR(10) DEFAULT '사원',
    SALARY INTEGER CHECK (SALARY < 6000000),
    DNO INTEGER CHECK (DNO IN (1, 2, 3, 4, 5)),
	PRIMARY KEY(EMPNO),
    FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DEPTNO) #참조 무결성 제약조건
    	ON DELETE SET DEFAULT
        ON UPDATE CASCADE;
    )

#참조 무결성 제약조건을 위한 데이터 정의어
ON DELETE NO ACTION
ON DELETE CASCADE
ON DELETE SET NULL
ON DELETE SET DEFAULT

ON UPDATE NO ACTION
ON UPDATE CASCADE
ON UPDATE SET NULL
ON UPDATE SET DEFAULT

4) 무결성 제약조건의 추가 및 삭제

릴레이션이 생성 된 후에도 여러 제약조건을 추가하거나 기존 제약 조건을 삭제할 수 있다.

#무결성 제약조건 추가
ALTER TABLE [릴레이션 명] ADD [제약조건]

#무결성 제약조건 삭제
ALTER TABLE [릴레이션 명] DROP [제약조건]

5. 트리거(trigger)와 주장(assertion)

위에서 테이블을 정의할 때 데이터베이스 스키마의 한 부분으로서 무결성 제약조건을 명시하는 것을 알아보았다. 이제 테이블 정의와 별도로 데이터베이스의 무결성을 시행하는 메커니즘인 트리거와 주장에 대해서 정리해보고자 한다.

1) 트리거

트리거는 데이터베이스이 갱신될 때마다 DBMS가 자동적으로 수행하도록 사용자가 정의하는 문이다. 트리거는 테이블 정의시 표현할 수 없는 규칙들을 시행하며, 무결성 제약조건을 유지하기 위해 데이터베이스 갱신을 모니터링하고, 갱신을 진행한다.

트리거는 마치 조건문처럼 한 릴레이션에 대해서 INSERT, DELETE, UPDATE 이벤트가 발생하면 트리거가 활성화되면 트리거의 조건이 참이 되면 트리거와 연관된 동작이 데이터베이스에 대해 실행되고, 그렇지 않으면 아무 동작도 수행되지 않는다.

이처럼 트리거는 제약조건과 유사하게 데이터베이스의 일관성을 유지하는데 유용하다. 그러나 트리거를 많이 사용하면 트리거들이 연쇄되어 복잡한 상호 의존성을 야기할 수도 있다.

트리거를 명시하는 방법은

이벤트(Event): 트리거를 활성화시키는 사건인 이벤트
조건(Condition): 트리거가 활성화되었을 때 수행되는 테스트인 조건
동작(Action): 트리거가 활성화되고 조건이 참일 때 수행되는 문

CREATE TRIGGER [트리거 이름]
ALTER [트리거를 유발하는 이벤트들이 OR로 연결된 리스트] ON [릴레이션 이름] #이벤트
(WHEN [조건]) #조건
BEGIN [SQL문들] END #동작

2) 주장

제약조건을 위반하는 연산이 수행되지 않도록 주장의 조건이 그 조건을 위배할 가능성이 있는 각 이벤트문들마다 검사된다.

주장의 명시하는 방법은

CREATE ASSERTION [주장 이름]
CHECK [조건]

주장은 데이터베이스가 항상 만족하기를 바라는 조건을 직접적으로 표현한 것으로 보통 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용된다.

그러나 주장이 복잡하면 유효성검사에서도 많은 오버헤드가 발생할 수 있으므로 신중하게 사용해야 한다.

주장의 예) ENROLL 릴레이션에 들어 있는 STNO는 반드시 STUDENT 릴레이션에 들어 있는 어떤 학생의 STNO를 참조하도록 하는 주장을 정의 ( = 다시 말해 STUDENT릴레이션에 없는 학생의 학번(STNO)이 ENROLL릴레이션에 들어가면 안된다.는 주장)

CREATE ASSERTION EnrollStudentIntegrity
CHECK (NOT EXISTS
	(SELECT *
         FROM ENROLL
         WHERE STNO NOT IN
    	    (SELECT STNO FROM STUDENT)));

reference

MS SQL Server 기반 데이터베이스 배움터 - 홍의경 지음

'Computer Science > DB & SQL' 카테고리의 다른 글

[Database] Database Index (0)	2021.03.01

현재글[Database] 무결성 제약조건

Grace's Tech Blog