제약 조건: 데이터베이스 무결성

데이터베이스 무결성 제어

데이터베이스에 대해 알아야 할 또 다른 중요한 사항은 CONSTRAINS입니다. 제약 조건을 사용하여 테이블의 데이터 변경을 제어하고 무결성과 일관성을 유지할 수 있습니다.

데이터베이스에 대해 이야기할 때 데이터 일관성 이란 무엇입니까 ?

직원, 제품 및 작업 테이블이 있는 온라인 상점을 살펴보겠습니다 . 우리는 이미 작업 테이블에 누구에게도 할당되지 않은 작업이 있을 수 있음을 알고 있습니다. 이러한 행의 employee_id는 NULL입니다.

하지만 task 테이블에 employee_id가 예를 들어 115인 항목이 있으면 어떻게 될까요? 결국 우리는 그런 직원이 없습니다. 직원 테이블에 id = 115인 직원이 없습니다. 동시에 이 ID를 가진 직원에 대한 링크가 작업 테이블에 있습니다. 이것은 데이터 불일치 의 예입니다 .

그렇다면 이러한 데이터를 어떻게 조정합니까? 이상적으로는 SQL 서버가 데이터 변경과 함께 이러한 모든 뉘앙스를 제어하도록 하는 것입니다. FOREIGN_KEY라는 기회가 있습니다.

테이블의 일부 열에 숫자뿐만 아니라 다른 테이블의 id 행이 포함되어 있으면 이를 명시적으로 지정할 수 있습니다.

외래 키 추가

이러한 키는 생성 단계와 이후에 ALTER TABLE을 사용하여 테이블에 추가할 수 있습니다. 형식은 근본적으로 다르지 않습니다. 두 가지 옵션을 모두 제시합니다.

이러한 키/규칙의 일반적인 형식은 다음과 같습니다.

FOREIGN KEY (column)
  	REFERENCES table(column)

이 키/규칙을 태스크 테이블에 추가하여 테이블의 모든 employee_id가 직원 테이블의 기존 항목을 참조하도록 합시다 . 이 스크립트는 다음과 같습니다.

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

작업 테이블을 생성할 때 이 규칙을 추가하기로 결정했다면 코드는 다음과 같을 것입니다.

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

그런데 참조하는 문자열에 고유한 복합 키가 있는 경우가 있습니다. 예를 들어 "이름 및 출생 연도" 또는 "productCatogoryId 및 productId"입니다. 그런 다음 FOREIGN KEY는 다음과 같이 작성할 수 있습니다.

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

FOREIGN KEY 및 데이터 변경

이제 직원 테이블의 일부 데이터를 업데이트하기로 결정했고 직원 ID가 변경된 상황을 상상해 보십시오. 작업 테이블의 데이터는 어떻게 됩니까? 맞습니다. 관련성이 없어지고 데이터베이스의 무결성이 침해될 것입니다.

이를 방지하기 위해 직원 테이블의 id가 변경될 때 이 변경된 특정 id를 참조하는 모든 테이블의 모든 행에 대한 employee_id를 변경하도록 SQL Server에 지시할 수 있습니다.

이러한 스크립트를 OnUpdate 및 OnDelete 라고 합니다 . 레코드 ID가 변경되면 어떻게 해야 하고 레코드가 삭제되면 어떻게 해야 합니까?

제거하면 모든 것이 그렇게 간단하지는 않습니다. 데이터베이스에서 서로를 참조하는 문자열로 표시되는 종속 개체가 있는 경우 한 개체를 삭제할 때 다양한 동작 시나리오가 가능합니다.

사이트 사용자를 삭제한다고 가정해 보겠습니다. 즉, 그의 모든 개인 서신을 삭제해야 합니다. 그러나 그의 공개 댓글을 모두 삭제해야 할 것 같지는 않습니다.

또는 직원이 그만 둡니다. 그가 그만두고 동시에 그에게 할당된 모든 작업이 데이터베이스에서 사라지면 이상할 것입니다. 그러나 그들이 그에게 임명되지 않은 상태로 남아 있었다면 그것은 또한 나빴을 것입니다. 직원이 모든 작업을 다른 사람에게 재 할당 한 후 종료 할 수 있도록 만드는 것이 더 정확합니다.

다음은 FOREIGN KEY를 사용하여 이러한 시나리오를 설명하는 방법입니다. 이러한 키/규칙의 일반적인 형식은 다음과 같습니다.

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

레코드를 삭제(ON DELETE)하거나 변경(ON UPDATE)하는 경우 어떻게 해야 합니까? 다음과 같은 각 상황에서 SQL 서버가 작동할 수 있는 총 5가지 옵션이 있습니다.

작업 테이블을 수정하는 방법은 다음과 같습니다.

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

여기에 쓰여진 내용:

ON UPDATE CASCADE : 직원 테이블의 id 키가 변경되면 이를 참조하는 작업 테이블의 employee_id도 변경합니다.

ON DELETE RESTRICT : 행이 직원 테이블에서 삭제되고 작업 테이블에서 참조되는 경우 행이 직원 테이블에서 삭제되지 않도록 합니다.