무결성이란?
20대 초중반의 남자들은 자유로운 생활을 살다가 군대를 가게 된다. 군대라는 집단에 속해진 남자들은 그동안의 생활을 뒤로하고 군인으로써 지켜야 할 규칙과 규약 아래 놓이게 된다. 로마에 가면 로마법을 따르듯, 어느 집단에 속해진 구성원은 그 집단의 법을 지키며 살아가야 집단의 구성원으로써 인정받는다. 그렇지 않으면 감옥에 들어가 집단에서 제외된다.
데이터 베이스도 이와 같다. 데이터가 데이터베이스에 들어와 데이터로써 인정받으려면 데이터베이스가 제공하는 제약조건을 지켜야한다. 제약조건을 지킨 데이터를 무결성이 있다고 말한다. 하지만 제약조건을 위반하게 되면 해당 데이터는 데이터로 인정받지 못한다.
실생활의 데이터를 모순없이 저장하고 여러 사람이 동시에 사용할 수 있는 데이터베이스를 만들려면 DBMS는 데이터의 제약조건 위반여부를 지속적으로 관리해야한다. 이번 포스팅에서는 데이터베이스가 갖는 제약조건에 대해서 다루어 볼 것이다.
기본적 무결성 제약
관계형 데이터베이스에서 갖는 기본적인 무결성 제약 조건을 알아보겠다.
1. 기본 키 무결성 제약
기본 키로 사용되는 '데이터'는 레코드를 구분할 수 있도록 반드시 식별 가능해야한다. 그러므로 아래와 같은 제약조건이 적용된다.
"기본키는 널 값을 가질 수 없으며 기본키의 값이 동일한 레코드가 하나의 테이블에 동시에 두 개 이상 존재할 수 없다. "
기본키 설정 방법 (Oracle)
1-1) CONSTRAINT 이용하기
CREATE TABLE student (
stu_id VARCHAR2(10) NOT NULL,
stu_name VARCHAR2(10) NOT NULL,
CONSTRAINT pk_stu PRIMARY KEY (stu_id)
)
제약 : CONSTRAINT
제약식명 : pk_stu
제약조건 : PRIMARY KEY
제약대상 : stu_id
1-2) CONSTRAINT 이용 안 하기
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL
)
이와 같이 단순히 제약대상이 되는 필드 뒤에 PRIMARY KEY 제약 조건을 명시해주어도 된다.
1-3) 테이블 생성 후 기본키 설정 방법
ALTER TABLE student ADD CONSTRAINT pk_stu PRIMARY KEY(stu_id);
레코드가 아닌 테이블 스키마와 관련된 수정이므로 UPDATE 명령어가 아닌 ALTER 명령어를 사용해준다.
ALTER TABLE student DROP CONSTRAINT pk_stu;
제약식 명을 삭제함으로써 기본키 설정을 해제하는 것이다.
2. 참조 무결성 제약
한 테이블에서 다른 테이블을 참조할 때 사용되는 '데이터(외래키)'에 적용되는 제약이 참조 무결성 제약이다. 참조 무결성 제약의 목적은 다른 테이블에 존재하지 않은 데이터를 참조하지 않도록 보장하기 위함이다.
그러므로 외래키는 다른 테이블에 반드시 존재하는 레코드의 기본키를 데이터로 갖거나 NULL 값을 가져 아예 어떤 레코드도 가리키지 않도록 해야한다. 만약 외래키가 가리킨 레코드가 실제로 존재하지 않는다면 이는 제약조건 위반으로 해당 외래키는 무결성이 없는 데이터로 취급받는다. 그래서 다른 테이블이 외래키로 갖는 기본키를 갖는 테이블의 레코드를 삭제하려는 시도를 하면 DBMS가 이를 막는다. 만약 해당 레코드를 삭제하면 다른 테이블의 외래키가 삭제된 레코드를 가리켜 참조 무결성 제약에 위배가 되기 때문이다.
외래키 설정방법(Oracle)
2-1) Constraint 이용하기
CREATE TABLE student (
stu_ id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10),
department_id VARCHAR2(10),
CONSTRAINT fk_dept FOREIGN KEY(department_id)
REFERENCES department(department_id)
)
CONSTRAINT
제약식명 : fk_dept
제약조건 : FOREIGN KEY
제약대상 : department_id
참조대상 : department (department _id)
(department 테이블의 department_id를 참조한다는 의미이다.)
2-2) CONSTRAINT 이용 안 하기
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL,
department_id FOREIGN KEY
REFERENCES deparment(dept_id)
)
2-3) 테이블 생성 후 외래키 지정하기
ALTER TABLE student ADD CONSTRAINT fk_dept FOREIGN KEY(department_id) REFERENCES department(dept_id);
ALTER TABLE student DROP CONSTRINT fk_dept;
테이블 무결성 제약
출처 입력
지금까지는 가장 대표적으로 기본키와 외래키로 사용되는 데이터에 적용되는 제약조건을 알아보았다. 이번에는 기본키와 외래키가 아닌 일반적인 데이터에 제약조건을 걸어보는 방법을 알아보겠다.
1) NOT NULL
NOT NULL은 NULL 값을 허용하지 않는 것이다. 만약 데이터로 NULL값이 들어오면 제약조건에 위배된다.
CREATE TABLE student (
stu_id VARCHAR2(20) NOT NULL
)
stu_id 필드에는 NULL 데이터가 들어가면 안 된다. PRIMARY KEY 제약조건 안에는 NOT NULL의 개념이 들어가 있기에 NOT NULL을 지정 안 해도 된다.
2) UNIQUE
UNIQUE 제약조건이 적용된 필드는 중복된 데이터를 갖지 못 한다. 단, PRIMARY KEY 제약조건과 달리 NULL 값을 허용한다. 그러므로 NULL값을 받기 싫다면 NOT NULL을 지정해주어야 한다. 만약 UNIQUE와 NOT NULL이 같이 쓰인다면 후보키로써 자격이 생긴다.
2-1) CONSTRAINT 이용하기
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMAR KEY,
stu_name VARCHAR2(10),
CONSTRAINT uc_stuname UNIQUE (stu_name)
)
2-2) CONSTRAINT 이용 안 하기
CREATE TABLE student(
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) UNIQUE
)
2-3) 두 개 필드 UNIQUE 제약조건 적용하기
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMAR KEY,
stu_name VARCHAR2(10),
stu_address VARCHAR2(20),
CONSTRAINT uc_stuinfo UNIQUE (stu_name,stu_address)
)
2-4) 테이블 생성후 UNIQUE 적용하기
ALTER TABLE student ADD CONSTRAINT uc_stuinfo UNIQUE(stu_name,stu_address);
ALTER TABLE student DROP CONSTRAINT uc_stuinfo;
3) CHECK
기본적으로 필드는 도메인이라는 제약조건을 갖는다. 데이터 타입으로 정해진 도메인 제약조건에 CHECK를 사용하여 세부적인 제약조건을 적용할 수 있다.
3-1) CONSTRAINT 이용하기
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL,
year INT NOT NULL,
CONSTRAINT chk_year CHECK ( year >= 1 and year <= 4 )
)
CHECK와 조건식을 통해 year필드에 세부적인 제약조건을 부여했다.
3-2) CONSTRAINT 이용 안하기
CREATE TABLE student(
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL,
year INT NOT NULL CHECK (year >=1 and year <= 4)
)
3-3) 조건식 심화
CREATE TABLE student (
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL,
year INT NOT NULL,
address VARCHAR2(10) NOT NULL,
CONSTRAINT chk1 CHECK (year >= 1 AND year <= 4 AND
address IN ('서울','부산') )
)
CHECK는 조건식을 사용하다보니깐 조건을 나타내는 연산자와 같이 쓰인다. AND, OR, NOT, IN 연산자가 주로 사용된다. 그 중에서 IN 연산자는 조건식이 성립되는 범위를 나타낸다. ('서울','부산') 과 같이, 성립되는 조건을 나열하는 방식을 주로 사용하는데 나열된 조건 중 하나라도 일치하면 참으로 처리한다. 그러므로 이는 OR 연산자와 원리가 같다.
[ address IN ('서울','부산') ] 은 [ address='서울' OR address='부산' ]과 같은 조건식이다. IN을 사용하면 관리도 쉽고 연산속도도 빠르기에 WHERE 조건에서는 OR보다는 IN을 더 자주 사용한다.
3-4) 테이블 생성 후, CHECK 제약 조건 적용하기
CHECK 제약조건 적용하기
ALTER TABLE student ADD CONSTRIANT chk1 CHECK ( year>=1 AND year<=1 AND address IN ('서울','부산');
CHECK 제약조건 제거하기
ALTER TABLE student DROP CONSTRAINT chk1;
4) DEFAULT
레코드 삽입시 NOT NULL 제약조건이 적용되지 않은 필드의 경우, 값이 없다면 NULL이 삽입된다. 이때 NULL 대신 다른 값을 삽입되도록 하고 싶다면 DEFAULT 제약조건으로 지정해주면 된다.
4-1) 테이블 생성 시, DEFAULT 적용
CREATE TABLE student(
stu_id VARCHAR2(10) PRIMARY KEY,
stu_name VARCHAR2(10) NOT NULL,
year INT DEFAULT 1
)
4-2) 테이블 생성 후, DEFAULT 적용
- SQL 표준
DEFAULT 적용
ALTER TABLE student ALTER COLUMN year SET DEFAULT 1;
DEFAULT 해제
ALTER TABLE student ALTER COLUMN year DROP DEFAULT;
- ORACLE 방식
DAFAULT 적용
ALTER TABLE student MODIFY ( year INT DEFAULT 1 );
DEFAULT 해제
ALTER TABLE student MODIFY ( year INT DEFAULT NULL);
기타 무결성 제약
주장(Assertion)
지금까지는 한 가지 테이블 안에서 적용되는 제약조건을 알아보았다. 두 가지 이상의 테이블에 적용되는 제약조건을 정의하는 방법을 주장(Assertion)이라고 한다. 대부분의 상용 DBMS에서 사용하지 않고 있다.
CREATE ASSERTION <주장이름> CHECK < 조건식 >
조건식은 EXISTS나 NOT EXISTS가 주로 사용된다.
트리거(Trigger)
지금까지는 제약조건이 필드에 적용되었다. 트리거는 이와 달리, DB에서 발생하는 이벤트(Event) ( DELETE , INSERT , UPDATE )에 제약조건을 명시하는 것이다. 특정 조건(Condition)을 만족하는 이벤트가 발생하면 트리거에 명시된 제약 조건을 따라 동작(Action)한다. (E-C-A 규칙)
응용프로그램에서의 무결성 제약
지금까지는 테이블을 생성하는 관리자가 제약조건을 명시했다. 이번에는 데이터베이스 사용자가 무결성을 보장하는 코드를 직접 삽입하는 방법이다. DBMS가 제공하는 기능으로 해결하지 못하는 제약조건인 경우 유용하고 DBMS가 일일이 검증을 안 해도되는 부담을 줄 일 수 있다.
제약 설정의 유의점
출처 입력
이렇게 다양한 제약조건을 남발하면 문제가 생긴다. 지나치게 많은 제약조건 탓에 데이터 검사가 많아져 전체적인 성능을 떨어트린다. 그리고 예외적인 데이터도 발생한다.
EX)
외국인은 주민번호가 없지만 주민번호 필드는 NOT NULL 제약조건이 걸려 있어서 외국인 레코드를 등록할 수 없다.
참고자료
Understanding of Database
저자 : 이상구, 장재영, 김한준, 정재헌
출판 : 이한미디어발매2012.08.20.
'DB > DB Basic' 카테고리의 다른 글
| [DB] 데이터베이스 권한(ROLE) [Oracle] (0) | 2021.06.25 |
|---|---|
| [DB] 데이터베이스 권한(GRANT, REVOKE) [Oracle] (0) | 2021.06.25 |
| [DB] SQL3 (Oracle) (0) | 2021.06.25 |
| [DB] SQL2 (Oracle) (0) | 2021.06.25 |
| [DB] SQL1 (Oracle) (0) | 2021.06.25 |