데이터베이스 무결성 종류

개체 무결성(Entity Integrity)

• 한 엔터티에서 같은 기본 키(PK)를 가질 수 없거나, 기본 키(PK)의 속성이 NULL을 허용할 수 없는 제약조건

참조 무결성(Referntial Integrity)

• 외래 키가 참조하는 다른 개체의 기본 키에 해당하는 값이 기본 키값이나 NULL이어야 하는 제약 조건

속성 무결성(Attribute Integrity)

• 속성의 값은 기본값, NULL 여부, 도메인(데이터 타입, 길이)이 지정된 규칙을 준수해야 하는 제약 조건

사용자 무결성(User - Defined Integrity)

• 사용자의 의미적 요구사항을 준수해야 하는 제약 조건

키 무결성(Key Integrity)

• 한 릴레이션에 같은 키값을 가진 튜플들을 허용할 수 없는 제약 조건

SQL 쿼리문(MSSQL 문법 기준으로 작성)

1. 제한(Restricted)

CREATE TABLE DEPARTMENT(
DEPTNO INT PRIMARY KEY,	
DEPTNAME VARCHAR(50),
FLOOR INT
);

CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
EMPNAME VARCHAR(50),
DEPTNO INT,
CONSTRAINT FK_EMPLOYEE_DEPT FOREIGN KEY(DEPTNO)
REFERENCES DEPARTMENT(DEPTNO)
);

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
VALUES
(1, '영업', 8),
(2, '개발', 10),
(3, '기획', 9),
(4, '홍보', 8);

INSERT INTO EMPLOYEE(EMPNO, EMPNAME, DEPTNO)
VALUES
(1, '이순신', 1),
(2, '홍길동', 3),
(3, '강감찬', 2),
(4, '권율', 1),
(5, '유성룡', 3);

SELECT * 
FROM DEPARTMENT;

SELECT * 
FROM EMPLOYEE;

**/* 참조 무결성 위배로 삭제 연산 거절 */**
DELETE FROM DEPARTMENT WHERE DEPTNO = 1;

2. 연쇄(Cascade)

CREATE TABLE DEPARTMENT(
DEPTNO INT PRIMARY KEY,	
DEPTNAME VARCHAR(50),
FLOOR INT
);

CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
EMPNAME VARCHAR(50),
DEPTNO INT,
FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE CASCADE
);

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
VALUES
(1, '영업', 8),
(2, '개발', 10),
(3, '기획', 9),
(4, '홍보', 8);

INSERT INTO EMPLOYEE(EMPNO, EMPNAME, DEPTNO)
VALUES
(1, '이순신', 1),
(2, '홍길동', 3),
(3, '강감찬', 2),
(4, '권율', 1),
(5, '유성룡', 3);

SELECT * 
FROM DEPARTMENT;

SELECT * 
FROM EMPLOYEE;

**/* EMPLOYEE 테이블에 DEPTNO = 3인 튜플 연쇄 삭제 */**
DELETE FROM DEPARTMENT WHERE DEPTNO = 3;

SELECT *
FROM DEPARTMENT;

SELECT *
FROM EMPLOYEE;

널 값(Nullify)

CREATE TABLE DEPARTMENT(
DEPTNO INT PRIMARY KEY,	
DEPTNAME VARCHAR(50),
FLOOR INT
);

CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
EMPNAME VARCHAR(50),
DEPTNO INT NULL,
CONSTRAINT FK_EMPLOYEE_DEPT FOREIGN KEY(DEPTNO)
REFERENCES DEPARTMENT(DEPTNO)
ON DELETE SET NULL
);

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
VALUES
(1, '영업', 8),
(2, '개발', 10),
(3, '기획', 9),
(4, '홍보', 8);

INSERT INTO EMPLOYEE(EMPNO, EMPNAME, DEPTNO)
VALUES
(1, '이순신', 1),
(2, '홍길동', 3),
(3, '강감찬', 2),
(4, '권율', 1),
(5, '유성룡', 3);

SELECT * 
FROM DEPARTMENT;

SELECT * 
FROM EMPLOYEE;

**/* EMPLOYEE 테이블의 DEPTNO = 1인 튜플의 DEPTNO 값이 NULL이 됨 */**
DELETE FROM DEPARTMENT WHERE DEPTNO = 1;

SELECT * 
FROM EMPLOYEE;

SELECT * 
FROM DEPARTMENT;

**/* EMPLOYEE 테이블의 DEPTNO 제약조건을 NOT NULL로 변경 */**
DROP TABLE EMPLOYEE;

DROP TABLE DEPARTMENT;

CREATE TABLE DEPARTMENT(
DEPTNO INT PRIMARY KEY,	
DEPTNAME VARCHAR(50),
FLOOR INT
);

CREATE TABLE EMPLOYEE(
EMPNO INT PRIMARY KEY,
EMPNAME VARCHAR(50),
DEPTNO INT NULL,
CONSTRAINT FK_EMPLOYEE_DEPT FOREIGN KEY(DEPTNO)
REFERENCES DEPARTMENT(DEPTNO)
ON DELETE SET NULL
);

INSERT INTO DEPARTMENT(DEPTNO, DEPTNAME, FLOOR)
VALUES
(1, '영업', 8),
(2, '개발', 10),
(3, '기획', 9),
(4, '홍보', 8);

INSERT INTO EMPLOYEE(EMPNO, EMPNAME, DEPTNO)
VALUES
(1, '이순신', 1),
(2, '홍길동', 3),
(3, '강감찬', 2),
(4, '권율', 1),
(5, '유성룡', 3);

SELECT * 
FROM EMPLOYEE;

SELECT * 
FROM DEPARTMENT;

ALTER TABLE EMPLOYEE ALTER COLUMN DEPTNO INT NOT NULL;

**/* EMPLOYEE 테이블의 DEPTNO 제약조건이 NOT NULL이기 때문에 삭제연산 거절 */**
DELETE FROM DEPARTMENT WHERE DEPTNO = 3;