MSSQL 데이터 무결성

데이터 무결성

 

  - 데이터베이스에 들어 있는 데이터의 정확성을 보장하기 위해

    데이터의 변경이나 수정을 할 때 제한을 두어 안정성을 저해하는 요소를 막기 위함이다.

  - 데이터 무결성은 관점에 따라 나누는 방법이 다르다.

 

 

○ 무엇(What)을 지켜 주는가

 

1. 실체 무결성 (Entity Integrity)

  - PRIMARY KEY 가 대표적이고 UNIQUE 도 있다. 

  - 행의 존재는 고유해야 한다.

 

2. 영역 무결성 (Domain Integrity)

  - 특정한 범위의 값만이 와야한다.

  - CHECK, DEFAULT 등이 있다.

 

3. 참조 무결성 (Referential Integrity)

  - 다른 컬럼과의 관계를 위반하지 않아야 한다.

  - FOREIGN KEY 가 있다.

 

 

○ 어떻게(How) 지켜 주는가

 

1. 절차적 방법

  - 먼저 정의하고, 다시 바인드하는 식으로 절차를 거쳐 정의하고 사용한다. 재할용 가능하다.

  - INDEX, RULE, DEFAULT 등이 있다.

 

2. 서술적 방법

  - 테이블을 서술할 때 함께 서술한다.

  - 재활용이 불가능하지만 문서화를 하기에 매우 쉽고, 읽기 좋다.

  - CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY 등이 있다.

 

 

* 컬럼에 대한 세가지 속성

○ Not Null (NN)

  - NULL을 허락하지 않는다.

  - NOT NULL, PRIMARY KEY

 

○ No Duplicate (ND)

  - 중복된 값을 허락하지 않는다.

  - UNIQUE, PRIMARY KEY

 

○ No Change (NC)

  - 값의 변경을 허락하지 않는다.

  - FOREIGN KEY로 참조 당할 때

 

 

자, 그럼 하나하나 씩 알아보자..

 

1. PRIMARY KEY

  - 기본 키는 중복된 자료를 받아들이지 않도록 해 줄 뿐만 아니라 NULL 값도 허락하지 않는다.

  - 한 테이블에서 하나의 행을 구분해 줄 수 있는 가장 기본적인 방법이다.

 

예)

CREATE TABLE 테이블1(

    id    INT

    CONSTRAINT  PK_id  PRIMARY KEY

)

--> 서술적 방법으로 컬럼 수준에서 제약을 걸어준 것이다.

 

예)

CREATE TABLE 테이블1(

    id    INT

,   CONSTRAINT  PK_id  PRIMARY  KEY (id)

)

--> 서술적 방법으로 테이블 수준에서 제약을 걸어준 것이다.

 

예)

ALTER TABLE 테이블1

ADD CONSTRAINT PK_id PRIMARY KEY (id)

--> 이미 생성되어 있는 테이블에 기본키를 삽입한다.

 

예)

ALTER TABLE 테이블1

DROP CONSTRAINT PK_id

--> 테이블에서 기본키 제약을 삭제한다.

 

2. UNIQUE

  - 고유 제약은 NULL 값을 허락한다는 것만 빼고는 기본키와 비슷하다.

  - 그렇지만 NULL 값도 고유해야하기 때문에 한 번만 입력할 수 있다.

  - 따라서 NOT NULL 과 UNIQUE 를 함께 선언하면 기본 키와 같은 기능을 한다.

예)

CREATE TABLE 테이블1(

    id  INT    NULL

    CONSTRAINT  U_id  UNIQUE

)

--> 테이블 생성시 UNIQUE 제약을 걸어 줬다.

 

3. DEFAULT

  - 디폴트는 아무 값도 지정하지 않았을 때 SQL 서버가 입력해 주는 값이다.

  - IDENTITY 나 TIMESTAMP 를 가진 컬럼에는 적용시킬 수 없다.

예)

CREATE TABLE 테이블1(

    city  CHAR(10)  NOT  NULL

    DEFAULT  '서울'

)

--> city 라는 컬럼에 아무 값도 지정하지 않으면 기본으로 '서울' 이라는 값이 들어간다.

 

4. RULE

  - 지정된 값이나 범위를 검사하는 제약조건이다.

  - 절차적인 방법으로 제약을 추가, 삭제 한다.

예)

CREATE RULE Rcity

AS

@city in ('서울', '대전', '대구', '부산', '울산')

--> RULE 을 정의 한다.

sp_bindrule Rcity, '테이블1.city'

--> 정의한 RULE을 컬럼과 묶는다 (bind) sp_bindrule 프로시저 사용

sp_unbindrule '테이블1.city'

--> bind 를 푼다. sp_unbindrule 프로시저 사용

DROP RULE Rcity

--> RULE 을 삭제한다.

 

5. CHECK

  - RULE과 마찬가지로 지정된 값이나 범위를 검사하는 제약조건이다.

  - 다른 점으로는 CHECK 는 서술적인 방법으로 추가한다.

예)

CREATE TABLE 테이블1(

    city CHAR(10)

    CHECK ( city in ('서울', '대전', '대구', '부산', '울산'))

)

또는

CREATE TABLE 테이블1(

    city CHAR(10)

    CONSTRAINT  CK_city  CHECK ( city in ('서울', '대전', '대구', '부산', '울산'))

)

 

6. FOREIGN KEY

  - 참조 키는 기본 키나 유일 제약, 유일 색인이 지정된 컬럼만을 참조할 수 있다.

  - 참조 키를 만들면 자동적으로 색인이 만들어지지 않는다. 색인이 필요하면 직접 만들어야 한다.

    반면, 기본 키나 유일 제약은 그 자체만으로 자동적으로 색인이 만들어진다.

  - 참조 키가 설정되면 참조 당하는 컬럼(주로 기본 키)은 자동적으로 NC 속성을 갖게 되며,

    따라서 테이블을 삭제하거나, 컬럼의 값을 삭제/ 변경할 수도 없게 된다.

  - 옵션을 지정할 수 있는데 CASCADE 를 사용하면 참조당하는 테이블의 자료가 변동되었을 때

    이를 참조하는 쪽에서도 자동으로 변동 되도록 설정할 수 있다.

  - 옵션을 지정하지 않거나 NO ACTION 으로 설정하면 참조 당하고 있을 때는 참조 당하는 테이블에서는

    삭제, 수정이 불가능하다.

예)

CREATE TABLE 테이블1(

    id       INT     NOT NULL

,   code  VARCHAR(20)

    FOREIGN  KEY  REFERENCES  테이블2(code)

    ON UPDATE CASCADE

    ON DELETE NO ACTION

)

--> 테이블1 을 생성하면서 code 컬럼에 테이블2 의 code 컬럼을 참조 키로 설정하였다.

      옵션 CASCADE 는 테이블2 의 code 항목이 UPDATE 를 되면 테이블1 의 code 도 자동으로 변하게 주었고

      옵션 NO ACTION 은 테이블2 의 DELETE 항목에 걸어서 삭제가 안되도록 설정 되었다.

 

예)

ALTER TABLE 테이블1

ADD CONSTRAINT FK_code FOREIGN KEY (code)

REFERENCES 테이블2(code)

--> 이미 생성된 테이블에 참조 제약을 추가할 수도 있다.

 

 

* 제약 중지시키기 / 기존 데이터 검사하지 않기

  - 제약을 만들 때 SQL 서버는 항상 기존의 데이터가 이 제약을 위반하지 않는지 검사한다.

  - 제약이 만들어지고 난 후에 들어오는 새로운 데이터들이 제약을 위반하지 않는지도 검사한다.

  - 단, 어떠한 경우에도 중지할 수 없는 제약이 있다. PRIMARY KEY, DEFAULT, UNIQUE 

 

○ NOCHECK

  - 제약이 만들어져 있는 테이블에 새로운 데이터가 들어올 때나 수정될 때 잠시 제약을 중지시킬 때 사용한다.

예)

ALTER TABLE 테이블1

NOCHECK

CONSTRAINT 제약이름

--> 제약이름 이라는 제약을 중지시킨다.

 

예)

ALTER TABLE 테이블1

CHECK

CONSTRAINT 제약이름

--> 제약을 다시 실행시킨다.

 

○ WITH NOCHECK / CHECK

  - 기존에 입력된 데이터가 있는 테이블에 제약을 새로 추가하려면 기존의 데이터에 대해서는 검사하지

    않기를 원할 때 사용한다.

예)

ALTER TABLE 테이블1

WITH NOCHECK

ADD CONSTRAINT 제약이름

--> 기존 데이터에 대해서 검사를 하지 않도록 하고 제약을 추가한다.