DB에서 데이터 무결성

Interview/DB

DB에서 데이터 무결성

김 정출 2024. 10. 14. 16:08

DB에서 데이터 무결성

데이터 무결성(Data Integrity)는 데이터베이스 내의 데이터가 정확하고, 일관되며, 신뢰할 수 있는 상태를 유지하도록 보장하는 개념입니다. 데이터 무결성은 데이터의 품질을 유지하고, 데이터베이스의 효율적인 운영을 위해 필수적입니다. 이를 위해 다양한 무결성 제약을 사용하며, 데이터의 저장, 수정, 삭제 등이 이루어질 때에도 데이터의 정확성과 일관성을 유지하는 것이 중요합니다.

1. 데이터 무결성

데이터 무결성은 크게 세 가지 유형으로 나눌 수 있습니다.

개체 무결성(Entity Integrity): 각 테이블의 행을 고유하게 식별할 수 있는 키(예: Primary Key)를 사용하여, 중복되지 않는 고유한 데이터를 보장합니다. 예를 들어, 하나의 레코드가 중복되거나 누락되지 않도록 해야 하며, 기본 키는 NULL 값을 가질 수 없습니다.
참조 무결성(Referential Integrity): 데이터베이스에서 두 테이블 간의 관계를 정의하는 외래 키(Foreign Key)를 사용하여 데이터 간의 관계가 일관되도록 보장합니다. 외래 키가 참조하는 값은 반드시 참조되는 테이블에 존재해야 하며, 참조 무결성을 유지하지 못하면 데이터 불일치가 발생할 수 있습니다.
도메인 무결성(Domain Integrity): 테이블 내의 각 열이 허용된 값 범위 내에서만 데이터를 가질 수 있도록 보장합니다. 예를 들어, 특정 열이 날짜 값을 저장하는 열이라면, 그 열에는 올바른 날짜 형식의 값만 허용됩니다. 이를 위해 데이터 타입, 제약 조건(CHECK 제약), 기본값 등을 정의할 수 있습니다.

2. 개체 무결성

개체 무결성(Entity Integrity)은 데이터베이스에서 각 개체(테이블의 각 행)가 고유하게 식별될 수 있도록 보장하는 원칙입니다.
이는 주로 기본 키(Primary Key) 제약 조건을 통해 구현됩니다. 기본 키는 테이블의 각 행을 고유하게 식별하며, 중복된 값이나 NULL 값을 가질 수 없습니다.

다음은 MySQL 데이터베이스에서 개체 무결성을 보장하는 예시입니다.

예시: 학생 테이블

학생 테이블 생성
- 아래 SQL 쿼리는 학생 정보를 저장하는 students 테이블을 생성합니다. 여기서 student_id는 기본 키로 설정되어 있습니다.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY, -- 기본 키
    name VARCHAR(100) NOT NULL,                -- 학생 이름
    age INT NOT NULL,
    email VARCHAR(100) UNIQUE                   -- 이메일은 유일해야 함
);

데이터 삽입
- 다음은 학생 데이터를 삽입하는 예시입니다.

INSERT INTO students (name, age, email) VALUES ('Alice', 20, 'alice@example.com');
INSERT INTO students (name, age, email) VALUES ('Bob', 22, 'bob@example.com');

무결성 보장
- student_id는 각 학생을 고유하게 식별하는 기본 키입니다. 중복된 student_id 값을 삽입하려고 하면 오류가 발생합니다.
- email 필드는 UNIQUE 제약 조건이 있어, 같은 이메일을 가진 두 학생을 추가할 수 없습니다.
- name과 age 필드는 NOT NULL 제약 조건을 가지고 있으므로 이 필드에 NULL 값을 삽입할 수 없습니다.
무결성 위반 예시
기본 키나 고유 제약 조건을 위반하려는 시도가 어떻게 오류를 발생시키는지 보여주는 예시입니다.

-- 중복된 student_id 삽입 시도 (이 경우 오류 발생)
INSERT INTO students (student_id, name, age, email) VALUES (1, 'Charlie', 21, 'charlie@example.com'); -- student_id가 이미 존재

-- 중복된 email 삽입 시도 (이 경우 오류 발생)
INSERT INTO students (name, age, email) VALUES ('David', 23, 'alice@example.com'); -- email이 이미 존재

이와 같은 방식으로 MySQL 데이터베이스에서 개체 무결성을 유지하면 데이터의 정확성과 일관성을 보장할 수 있습니다.

3. 참조 무결성

참조 무결성(Referential Integrity)은 데이터베이스에서 외래 키(Foreign Key) 제약 조건을 통해 테이블 간의 관계가 유효하고 일관되도록 보장하는 원칙입니다. 외래 키는 한 테이블의 필드가 다른 테이블의 기본 키를 참조할 때 사용됩니다. 이를 통해 부모-자식 관계를 설정하고, 자식 데이터가 부모 데이터와 일치하는지 확인할 수 있습니다.

다음은 MySQL 데이터베이스에서 참조 무결성을 보장하는 예시입니다.

예시: 학생과 수업 테이블

테이블 생성

아래 SQL 쿼리는 students 테이블과 courses 테이블을 생성하고, enrollments라는 중간 테이블을 만들어 학생과 수업 간의 관계를 설정합니다.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,  -- 학생 ID, 기본 키
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,    -- 수업 ID, 기본 키
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY, -- 수강 ID, 기본 키
    student_id INT,                                -- 학생 ID, 외래 키
    course_id INT,                                 -- 수업 ID, 외래 키
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE, -- 참조 무결성
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE      -- 참조 무결성
);

데이터 삽입
- 학생과 수업 데이터를 삽입한 후, 중간 테이블에 학생이 수업에 등록되도록 데이터를 삽입합니다.

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO students (name) VALUES ('Bob');

INSERT INTO courses (course_name) VALUES ('Mathematics');
INSERT INTO courses (course_name) VALUES ('Science');

-- 학생이 수업에 등록하는 경우
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);  -- Alice가 Mathematics 수업에 등록
INSERT INTO enrollments (student_id, course_id) VALUES (1, 2);  -- Alice가 Science 수업에 등록
INSERT INTO enrollments (student_id, course_id) VALUES (2, 1);  -- Bob이 Mathematics 수업에 등록

참조 무결성 보장
- enrollments 테이블의 student_id는 students 테이블의 student_id를 참조합니다. 따라서 students 테이블에 존재하지 않는 student_id를 enrollments 테이블에 삽입할 수 없습니다.
- 마찬가지로 course_id는 courses 테이블의 course_id를 참조합니다.
무결성 위반 예시
- 존재하지 않는 학생 ID나 수업 ID를 삽입하려고 할 경우 오류가 발생합니다.

-- 존재하지 않는 학생 ID 삽입 시도 (이 경우 오류 발생)
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);  -- student_id가 students 테이블에 없음

-- 존재하지 않는 수업 ID 삽입 시도 (이 경우 오류 발생)
INSERT INTO enrollments (student_id, course_id) VALUES (1, 3);  -- course_id가 courses 테이블에 없음

삭제 동작
- 외래 키 제약 조건에서 ON DELETE CASCADE를 설정하면, 부모 테이블의 레코드가 삭제될 때 자식 테이블의 관련 레코드도 자동으로 삭제됩니다. 예를 들어, 학생이 삭제될 경우 그 학생과 관련된 모든 수강 기록도 자동으로 삭제됩니다.

DELETE FROM students WHERE student_id = 1;  -- Alice 삭제

이 예시를 통해 참조 무결성을 어떻게 설정하고 유지할 수 있는지를 보여줍니다. 참조 무결성은 데이터베이스의 무결성을 보장하는 중요한 요소로, 데이터 간의 관계를 효과적으로 관리하는 데 도움을 줍니다.

4. 도메인 무결성

도메인 무결성(Domain Integrity)은 데이터베이스에서 각 필드(열)가 특정한 도메인(유효한 값의 집합)에 속하는지 확인하는 원칙입니다. 이는 데이터 타입, 형식, 범위, NULL 여부 등을 통해 관리됩니다. 도메인 무결성은 각 필드에 적합한 값만 저장되도록 보장함으로써 데이터의 정확성과 일관성을 유지하는 데 기여합니다.

다음은 MySQL 데이터베이스에서 도메인 무결성을 보장하는 예시입니다.

예시: 직원 테이블

직원 테이블 생성
- 아래 SQL 쿼리는 직원 정보를 저장하는 employees 테이블을 생성합니다. 이 테이블은 각 필드에 대한 도메인 무결성을 설정합니다.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,   -- 직원 ID, 기본 키
    name VARCHAR(100) NOT NULL,                   -- 직원 이름, NULL 불가
    age INT CHECK (age >= 18 AND age <= 65),      -- 나이, 18세 이상 65세 이하
    email VARCHAR(100) UNIQUE,                    -- 이메일, 고유해야 함
    salary DECIMAL(10, 2) CHECK (salary >= 0),    -- 급여, 0 이상
    hire_date DATE NOT NULL,                      -- 고용 날짜, NULL 불가
    department VARCHAR(50) CHECK (department IN ('HR', 'IT', 'Finance', 'Sales'))  -- 부서, 정해진 값만 가능
);

데이터 삽입

아래 쿼리는 유효한 데이터를 삽입하는 예시입니다.

INSERT INTO employees (name, age, email, salary, hire_date, department)
VALUES ('Alice', 30, 'alice@example.com', 60000.00, '2024-01-15', 'IT');

INSERT INTO employees (name, age, email, salary, hire_date, department)
VALUES ('Bob', 25, 'bob@example.com', 50000.00, '2024-02-20', 'HR');

도메인 무결성 보장
- 데이터 타입: 각 열의 데이터 타입이 올바르게 지정되었습니다. 예를 들어, salary는 DECIMAL로 설정되어 금액을 정확하게 저장할 수 있습니다.
- NULL 제약: NOT NULL 제약 조건이 설정된 열(예: name, hire_date)은 NULL 값을 가질 수 없습니다.
- CHECK 제약 조건:
  - age는 18세 이상 65세 이하의 값만 허용됩니다.
  - salary는 0 이상의 값만 허용됩니다.
  - department는 정해진 값들(HR, IT, Finance, Sales) 중 하나만 허용됩니다.
무결성 위반 예시
- 아래는 도메인 무결성을 위반하려는 시도에서 발생하는 오류를 보여줍니다.

-- 나이 범위 위반 (이 경우 오류 발생)
INSERT INTO employees (name, age, email, salary, hire_date, department)
VALUES ('Charlie', 17, 'charlie@example.com', 40000.00, '2024-03-01', 'Finance');  -- 나이가 18세 미만

-- 급여 범위 위반 (이 경우 오류 발생)
INSERT INTO employees (name, age, email, salary, hire_date, department)
VALUES ('David', 35, 'david@example.com', -5000.00, '2024-04-01', 'Sales');  -- 급여가 음수

-- 유효하지 않은 부서 (이 경우 오류 발생)
INSERT INTO employees (name, age, email, salary, hire_date, department)
VALUES ('Eva', 28, 'eva@example.com', 45000.00, '2024-05-01', 'Marketing');  -- 부서가 지정된 값에 없음

이 예시를 통해 도메인 무결성을 설정하고 유지하는 방법을 알 수 있습니다. 도메인 무결성은 데이터베이스의 데이터 정확성을 높이고, 잘못된 데이터 입력을 방지하여 데이터의 일관성을 유지하는 데 중요한 역할을 합니다.

데이터 무결성을 보장하기 위해서

데이터 무결성을 보장하기 위해 개발자가 개발할 때 사용할 수 있는 여러 가지 방법이 있습니다. 이러한 방법들은 데이터가 정확하고 일관되며 신뢰할 수 있는 상태로 유지되도록 돕습니다. 아래는 데이터 무결성을 유지하기 위한 주요 방법들입니다.

1. 제약 조건(Constraints) 설정

기본 키(Primary Key): 각 레코드를 고유하게 식별하는 열을 설정하여 중복을 방지합니다.
외래 키(Foreign Key): 다른 테이블의 기본 키와 관계를 설정하여 데이터 간의 무결성을 유지합니다.
유니크 제약(Unique Constraint): 특정 열의 값이 고유하도록 보장하여 중복 값을 방지합니다.
체크 제약(Check Constraint): 특정 열의 값이 특정 조건을 만족하는지 확인하여 데이터의 유효성을 검증합니다.
널(NOT NULL): 특정 열이 NULL 값을 가질 수 없도록 설정하여 데이터 누락을 방지합니다.

2. 정규화(Normalization)

데이터베이스 설계를 정규화하여 데이터의 중복을 줄이고, 관련 데이터를 분리하여 일관성을 높입니다. 정규화는 데이터의 구조를 체계적으로 조직하여 무결성을 향상시키는 데 도움을 줍니다.

3. 트랜잭션 관리(Transaction Management)

ACID 속성을 준수하여 데이터베이스 트랜잭션이 원자적이며 일관성 있게 실행되도록 보장합니다. 트랜잭션을 통해 데이터의 일관성을 유지하고, 실패 시에는 롤백을 통해 이전 상태로 되돌릴 수 있습니다.

4. 유효성 검사(Validation)

사용자 입력 데이터에 대한 유효성 검사를 수행하여 올바른 데이터만 데이터베이스에 저장되도록 합니다. 예를 들어, 이메일 형식 확인, 숫자 범위 확인 등을 통해 유효한 데이터만 수용합니다.

5. 비즈니스 규칙 구현

비즈니스 로직에 따라 데이터가 올바르게 입력되고 수정되도록 하여 데이터의 무결성을 유지합니다. 예를 들어, 재고 관리 시스템에서 재고 수량이 음수로 내려가지 않도록 하는 규칙을 설정할 수 있습니다.

6. 정기적인 데이터 감사 및 모니터링

데이터 무결성을 유지하기 위해 정기적으로 데이터베이스를 감사하고, 비정상적인 데이터나 오류를 식별하여 수정합니다. 데이터 품질 관리를 위한 프로세스를 설정할 수 있습니다.

7. 애플리케이션 레벨의 데이터 검증

애플리케이션 코드 내에서 데이터 검증 및 변환을 수행하여 사용자 인터페이스에서 입력되는 데이터의 품질을 높입니다. 예를 들어, 특정 포맷으로 날짜를 입력하도록 요구하는 등의 방법을 사용할 수 있습니다.

8. 예외 처리(Exception Handling)

데이터베이스 작업 중 발생할 수 있는 예외를 적절하게 처리하여 데이터 무결성을 보장합니다. 예외 상황에서 적절한 롤백이나 오류 로그를 기록하여 시스템의 일관성을 유지할 수 있습니다.

9. 백업 및 복구 전략

데이터베이스의 정기적인 백업을 통해 데이터 손실이나 오류 발생 시 신속하게 복구할 수 있도록 합니다. 데이터 손상이나 무결성 문제가 발생했을 때 데이터를 이전 상태로 복구할 수 있는 계획을 수립해야 합니다.

10. 권한 관리 및 보안

데이터베이스에 대한 접근 권한을 제한하여 인증된 사용자만 데이터에 접근하고 수정할 수 있도록 합니다. 사용자 권한 관리를 통해 무결성을 유지하는 것도 중요한 방법입니다.

결론

이러한 방법들을 통해 개발자는 데이터 무결성을 보장하고, 데이터베이스의 품질을 유지할 수 있습니다. 데이터 무결성 관리는 단순히 기술적인 문제를 넘어 비즈니스의 신뢰성과 직결되는 중요한 요소입니다.

저작자표시 비영리