[DataBase] 무결성

무결성

데이터베이스에서 데이터의 정확성, 일관성, 유효성을 유지하는 것

정확성(Accuracy)

• 데이터가 현실 세계의 사실이나 예상된 값과 정확히 일치하는 정도
• 예로 사용자의 주소, 전화번호와 같은 정보는 실제 정보와 일치해야 함 

일관성(Consistency)

• 데이터베이스 내의 모든 데이터가 논리적으로 일관된 상태를 유지하는 것
• 예로 두 테이블 간의 관계에서 참조 무결성이 유지되어야 하며, 동일한 데이터는 모든 곳에서 동일한 값을 가져야 
• 일관성은 데이터베이스의 다양한 부분 간의 모순이 없음을 보장함

유효성(Validity)

• 데이터가 정해진 규칙, 제약 조건, 도메인 등에 맞게 입력되었는지의 여부
• 예를 들어, 이메일 필드에는 올바른 형식의 데이터(~@~.~)만 입력되어야 하며, '성별' 필드에는 '남', '여' 와 같은 유효한 값만 포함되어야 한다.
• 유효성은 데이터가 특정 목적에 적합하도록 보장함

무결성을 위한 제약 조건

 

도메인 제약조건(Domain constraint)

• 모든 필드의 데이터가 정의된 도메인(허용값 범위, 허용 타입)만 허용

엔티티 제약조건(Entity integrity constraint)

• 각 테이블의 기본 키(primary key)가 유일하고(null이 아닌) 값으로 구성되어야 함
• 각 데이터를 고유하게 식별하기 위함

키 무결성 제약조건(Key integrity constraint)

• 각 테이블에는 최소 하나의 키가 반드시 존재해야 함

참조 무결성 제약조건(Referential integrity constraint)

• 외래 키(foreign key)는 값이 참조하는 테이블의 기본 키 값과 일치하거나 null일 경우만 허용
• 테이블 간의 관계가 일관되게 유지하기 위함

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

• 애플리케이션에 필요한 비즈니스 규칙에 맞게 설정된 제약 조건

무결성 제약조건의 유지 방법

 

데이터 입력 시 검증

• 데이터를 데이터베이스에 입력하기 전에 입력 데이터가 무결성 제약조건을 충족하는지 미리 검증
• 예를 들어, 특정 필드가 숫자만을 받아야 한다면, 입력 데이터가 숫자인지 확인

데이터베이스 트리거 사용

• 데이터베이스 트리거는 데이터가 추가, 수정, 삭제될 때 자동으로 실행되는 SQL 코드 조각
• 트리거를 사용하여 데이터 변경 시 무결성 제약조건을 자동으로 확인하고 유지

• 데이터 베이스 트리거 예시
• EmployeeSalary 테이블의 급여 정보가 변경될 때마다 이전 급여(OLD.salary)와 새 급여(NEW.salary)가 다를 경우, 변경 사항을 SalaryChangeLog 테이블에 기록

CREATE TRIGGER AfterSalaryUpdate
AFTER UPDATE ON EmployeeSalary
FOR EACH ROW
BEGIN
   IF OLD.salary <> NEW.salary THEN
      INSERT INTO SalaryChangeLog(EmployeeID, OldSalary, NewSalary, ChangeDate)
      VALUES (OLD.EmployeeID, OLD.salary, NEW.salary, NOW());
   END IF;
END;

• ImportantData 테이블에서 데이터를 삭제하려고 할 때 마다 오류 메세지와 함께 삭제를 방지

CREATE TRIGGER PreventDeletion
BEFORE DELETE ON ImportantData
FOR EACH ROW
BEGIN
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deletion is not allowed for ImportantData table.';
END;

 

 

 

데이터베이스 제약조건 설정

• DBMS에서 도메인 무결성, 엔티티 무결성, 참조 무결성 등을 직접 설정

정기적인 데이터 검사

• 정기적으로 데이터를 검사하여 무결성 제약조건을 위반하는 데이터가 없는지 확인하고, 문제가 발견되면 적절히 수정

사용자 권한 관리

•  접근 권한을 적절히 관리하여, 데이터를 변경할 수 있는 사용자를 제한함으로써 데이터 변경으로 인한 무결성 위반을 방지

응용 프로그램 레벨에서의 검증

•  데이터베이스 무결성을 유지하기 위해, 응용 프로그램 레벨에서도 데이터 검증 로직을 구현하여, 데이터베이스에 입력되기 전에 데이터가 무결성 제약조건을 충족하는지 사전에 검증

참조 무결성 제약조건을 만족시키기 위해서 DBMS가 제공하는 옵션

 

CASCADE

• 참조되는 행이 업데이트되거나 삭제될 때, 참조하는 외래 키 행도 같이 업데이트되거나 삭제
• 예를 들어, 부모 테이블의 행이 삭제되면, 참조하는 자식 테이블의 관련 행들도 삭제
• 예시: Employee 테이블(부모)과 EmployeeDetails 테이블(자식)이 있을 때, Employee 테이블의 직원이 삭제되면 해당 직원의 세부 정보도 EmployeeDetails 테이블에서 삭제

ALTER TABLE EmployeeDetails
ADD CONSTRAINT fk_employee
FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
ON DELETE CASCADE;

 

SET NULL

• 참조되는 행이 업데이트되거나 삭제될 때, 참조하는 외래 키 행의 해당 칼럼 값을 NULL로 설정
• 옵션은 외래 키 칼럼이 NULL을 허용할 때만 사용
• 예시: Order 테이블에서 고객 정보를 참조하는 경우, 고객 정보가 삭제되면 Order 테이블의 해당 고객 ID는 NULL로 설정

ALTER TABLE Order
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
ON DELETE SET NULL;

SET DEFAULT

• 참조되는 행이 업데이트되거나 삭제될 때, 참조하는 외래 키 행의 해당 칼럼 값을 기본값으로 설정
• 외래 키 칼럼에 기본값이 설정되어 있을 때 사용
• 예시: Order 테이블의 Status 칼럼이 외래키로 OrderStatus 테이블을 참조할 때, 특정 OrderStatus가 삭제되면 관련 Order 테이블의 Status 칼럼은 'Pending'과 같은 기본값으로 설정

ALTER TABLE Order
ADD CONSTRAINT fk_order_status
FOREIGN KEY (StatusID)
REFERENCES OrderStatus(StatusID)
ON DELETE SET DEFAULT;

NO ACTION

• 트랜잭션의 처음부터 참조 무결성을 위반하는 업데이트나 삭제가 시도될 때, 아무런 조치도 취하지 않고 오류를 발생
• 연관된 행을 삭제하거나 업데이트하려는 시도가 있을 때 즉시 제약 조건을 검사
• 무결성 제약 조건을 위반한다면, 작업은 바로 거부
• 예시: Customer 테이블과 Order 테이블이 있고, Order 테이블에서 CustomerID를 외래키로 사용하는 경우, 해당 고객에 대한 주문이 존재하는 상태에서 Customer 테이블의 고객 정보를 삭제하려고 하면 에러가 발생

ALTER TABLE Order
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
ON DELETE NO ACTION;

RESTRICT

• 트랜잭션이 종료된 후 커밋을 시도할 때 참조 무결성을 위반하는 업데이트나 삭제가 확인되면 오류를 발생
• 데이터베이스에 변경을 적용하기 전에 최종 검사를 수행하고 참조 무결성을 확인
• 트랜잭션 내에서 참조 무결성을 위반하는 변경이 있다면, 그 시점에도 오류가 발생하고 트랜잭션은 롤백
• NO ACTION과 거의 유사하지만, 제약 조건을 확인하는 시점 차이가 있는 경우가 아주 드물게 발생

ALTER TABLE Order
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID)
REFERENCES Customer(CustomerID)
ON DELETE RESTRICT;