[PostgreSQL] 제약조건, Constraint(NOT NULL, UNIQUE, PRIMARY KEY(기본키), FOREIGN KEY(외래키))

테이블 작성시에 Constraint를 사용해서 입력하는 자료에 대해서 제약, 즉 규칙을 정해줄 수 있는데요.
이때 정해진 제약에 따라서 데이터가 입력이 됩니다. 제약에 배반된다면 자료 입력이 거부되면서 오류가 납니다.

 즉, 데이터 무결성(Data Integrity)을 지키기 위한 데이터 무결성 제약조건(Integrity Constraint)에 대해서 알아볼까 합니다.
참고로, 네이버 지식백과에서는 데이터 무결성에 대해서 다음과 같이 밝히고 있는데요.

"데이터를 인가되지 않은 방법으로 변경할 수 없도록 보호하는 성질" 

쉽게 말하면, 권한이 부여된 계정이나 사람만이 접근 가능하고, 정확하고 완전한 데이터들이 저장되어 있는 상태라고 생각하시면 되겠습니다.
여기서 제약조건은 이러한 데이터 무결성을 보장함으로써 쓰레기 데이터가 저장되지 않도록 하는 기능을 제공합니다.

데이터의 무결성을 위해서 데이터베이스에서는 5가지 제약조건(Constraint)를 제공하고 있습니다.

NOT NULL	'필수 입력 사항'을 의미
UNIQUE	중복성 배제 즉, '유일한 값'으로 존재해야 함을 의미
PRIMARY KEY(기본키)	NOT NULL + UNIQUE로, '테이블에서 대표되는 컬럼'을 의미
FOREIGN KEY(외래키)	'참조하는 테이블에서 존재하는 값만 사용 가능'을 의미
CHECK	'주어진 조건에 해당하는 값만 입력 가능'을 의미

차례대로 구문 형식과 특징을 알아보도록 하겠습니다.

1. NOT NULL
  - 구문형식

 CREATE TABLE 테이블명
  (컬럼명 1  데이터 타입  NOT NULL
  ,컬럼명 2  데이터 타입 NULL
  ,컬럼명 3  데이터 타입 );

  
    ① NOT NULL은 INSERT 시, 즉 데이터 입력시에 누락이 되어서는 안되는 부분입니다. NULL값이 들어가게 되면 오류가 납니다.
    ② NULL 값이 기본값(Default) 이므로, NULL을 명시해주는 컬럼 2와 NULL을 명시해주지 않은 컬럼 3은 동일하다고 보시면 됩니다.
    ③ INSERT 시에 컬럼 2와 컬럼 3에 NULL값을 넣기 위해서는 'NULL' 또는 빈칸없이 '' 으로 해주시면 됩니다. 
        안에 공백을 넣게 되면 NULL 값이 아닌 공백 값으로 들어갑니다.

2. UNIQUE
  - 구문형식

CREATE TABLE 테이블명
 (컬럼명 1 데이터 타입 UNIQUE
 ,컬럼명 2 데이터 타입 
 ,컬럼명 3 데이터 타입,
 ,컬럼명 4 데이터 타입, UNIQUE
 CONSTRAINTS 제약명 UNIQUE (컬럼2, 컬럼3));

    ① UNIQUE는 해당테이블에 있어서 존재하는 값이 유일해야 합니다. 
        만일 INSERT 또는 UPDATE 시, 제약이 걸려있는 컬럼에 동일한 데이터가 존재한다면 오류가 납니다.
    ② 이때, NULL 값에 대해서는 UNIQUE 제약이 적용되지 않습니다. 
        즉, NULL 값은 데이터로 인식하지 않기때문에 해당 컬럼에 NULL 데이터행이 여러개 존재 가능합니다.
    ③ 테이블을 만들때 제일 밑에 CONSTRAINTS 제약명 UNIQUE (컬럼2, 컬럼3) 과 같이 CONSTRAINTS의 이름을 지정이 가능합니다.
        UNIQUE 제약으로 들어가는 컬럼들은 그들의 조합이 유일해야 합니다. 각 컬럼의 데이터의 유일함은 의미가 없고, 조합이 유일해야 합니다.
    ⑤ ③을 사용시에 조합을 예로들면, 컬럼2와 컬럼3에 각각 1,2 그리고 1,3 (컬럼2의 1이라는 값이 동일)의 데이터는 존재가 가능합니다.
       컬럼2가 같다고 하더라도 그 조합이 다르기 때문에 오류를 일으키지 않습니다.

3. PRIMARY KEY(기본키)
  - 구문형식

CREATE TABLE 테이블명
 (컬럼명 1 데이터 타입 PRIMARY KEY
 ,컬럼명 2 데이터 타입 );

 OR

CREATE TABLE 테이블명
 (컬럼명 1 데이터 타입 
 ,컬럼명 2 데이터 타입 
 CONSTRAINTS 기본키 이름 PRIMARY KEY (컬럼1));

    ① PRIMARY KEY 즉, 기본키는 하나의 테이블에 있는 데이터들을 식별하기 위한 기준으로 인식되는 제약조건입니다.
    ② 그렇기 때문에 UNIQUE와는 달리 한개의 테이블에 하나만 생성이 가능합니다.
    ③ 기본키는 NOT NULL + UNIQUE 의 속성을 가집니다. 즉, NULL값이 있으면 안되고 해당 컬럼의 데이터는 중복되지 않고 유일해야 합니다.
    ④ 기본키를 명시하는 방법은 위와 같이 두가지가 있으며 동일합니다. 다만 아래쪽은 키본키의 이름을 지정해 줄 수 있습니다.

4. FOREIGN KEY(외래키)
  - 구문형식

CREATE TABLE 테이블명
  (컬럼명 1 데이터 타입 CONSTRAINT 외래키 이름
  REFERENCES 참조테이블명(참조 컬럼)
  ,컬럼명 2 데이터 타입 );

  OR

CREATE TABLE 테이블명
  (컬럼명 1 데이터 타입 
  ,컬럼명 2 데이터 타입 
  CONSTRAINT 외래키 이름 
  FOREIGN KEY (컬럼1)
  REFERENCES 참조테이블명(참조 컬럼));

    ① FOREIGN KEY 즉, 외래키는 해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용한다는 의미의 제약조건입니다.
        만일에 참조하는 테이블에 해당하는 값이 없을시에는 INSERT시나 UPDATE시에 오류가 나게 됩니다.
    ② 외래키는 여러개의 컬럼에 중복적으로 적용 가능합니다.
    ③ 외래키로 두 테이블이 부모테이블과 자식테이블로 관계를 맺고 있을시에, 
         자식테이블이 참조하는 데이터는 부모 테이블에서 삭제가 불가능합니다.
    ④ 외래키를 명시하는 방법은 위와 같이 두가지가 있으며 동일합니다. 다만 아래쪽은 외래키의 이름을 지정해 줄 수 있습니다.
    ⑤ 외래키에 대한 자세한 설명은 다음글에서 확인 가능합니다. 

5. CHECK
  - 구문형식

CREATE TABLE 테이블명
  (컬럼명 1 데이터 타입 
    CONSTRAINTS 제약명 CHECK (조건)
  ,컬럼명 2 데이터 타입);

    ① CHECK는 조건에 부합하는 데이터만 입력이 가능하도록 하는 제약조건입니다.
    ② 조건에는 기본연산자나 비교연산자, IN, NOT IN 등등이 사용 가능합니다.

펌: http://dhna.tistory.com/199

참조

SQL FOREGIN KEY Constraint 란 무엇인가?

1. SQL Constraints 중 하나이다.

2. 테이블의 외래키(FORGEGIN KEY)는 다른 테이블의 PRIMAKRY KEY 를 가리켜야 하는 제약을 뜻한다.

- 이것은 다른 테이블의 레코드를 쉽게 참조할 수 있는 C++ Reference 정도로 이해하면 되겠다.

특이 사항은 무엇인가?

외래키(FOREIGN KEY) 제약은 테이블간 링크가 무너지는 액션을 방지하려고 사용한다.

외래키(FOREIGN KEY) 제약은 외래키 칼럼에 잘못된 값이 삽입되는 것을 방지한다.

- 왜래키에 칼럽에 삽입되는 값은 가리킨 테이블에서 사용하는 값이어야 하기 때문이다.

TEST_ORG 테이블 생성

DROP TABLE TEST_ORG;

CREATE TABLE TEST_ORG

(

  org_id integer not null primary key,

  org_name varchar(100)

);

TEST_ORG 레코드 삽입

insert into test_org (org_id, org_name) values (1,'개발부');

insert into test_org (org_id, org_name) values (2,'운영부');

insert into test_org (org_id, org_name) values (3,'총무부');

TEST_USER 테이블 생성

DROP TABLE TEST_USER;

CREATE TABLE TEST_USER

(

  USER_ID varchar(30) not null,

  FK_ORG_ID integer not null,

  CONSTRAINT test_user_pk1 PRIMARY KEY (USER_ID),

  CONSTRAINT test_user_fk1 FOREIGN KEY (FK_ORG_ID) REFERENCES test_org (ORG_ID) 

  MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION

  -- 상단구분은 기본적으로 선언하지 않으면 NO ACTION을 따라간다.

);

ON UPDATE NO ACTION은 현재테이블의 외래키 fk_org_id 에서 참조하는 다른테이블의 키를 업데이트 할려고 할때

오류를 발생시키고 UPDATE가 롤백되도록 지정하는 기능이다.

ON DELETE NO ACTION은 현재테이블의 외래키에서 참조하는 TEST_ORG 테이블의 ORG_ID 키를 삭제 할려고 할때

오류를 발생시키고 DELETE가 롤백되도록 지정하는 기능이다.

ON DELETE NO ACTION은 다른 테이블에 있는 기존 행의 외래 키에서 참조하는

키가 있는 행을 삭제하려고 할 때 오류를 발생시키고 DELETE가 롤백되도록 지정하는

기능이지요.. 기본적으로 선언을 하지 않으면 NO ACTION을 따라갑니다.

insert into test_user (user_id, fk_org_id) values ('admin',1);

insert into test_user (user_id, fk_org_id) values ('bana1',1);

insert into test_user (user_id, fk_org_id) values ('dapa9',2);

-- 상단쿼리 정상실행

insert into test_user (user_id, fk_org_id) values ('sane11',4);

-- 상단쿼리 에러발생, fk_org_id 컬럼에 4을 넣으면 참조하는 테이블 TEST_ORG의 ORG_ID컬럼에 존재하는지 체크한다.

-- 존재한다면 정상실행되지만 존재하지 않으므로 에러발생

ERROR:  insert or update on table "test_user" violates foreign key constraint "test_user_fk1"

DETAIL:  Key (fk_org_id)=(4) is not present in table "test_org".

UPDATE TEST_USER SET FK_ORG_ID = '2' WHERE FK_ORG_ID = '1';

-- 정상실행, 변경하는 FK_ORG_ID 컬럼값은 TEST_ORG 테이블의 ORG_ID 컬럼에 존재하는 값이다.

UPDATE TEST_USER SET FK_ORG_ID = '4' WHERE FK_ORG_ID = '2';

-- 에러발생, FK_ORG_ID 컬럼값을 4로 변경시 참조하는 테이블 TEST_ORG의 ORG_ID 컬럼값에 4가 존재하지 않아서

-- 에러가 발생한다.

DELETE FROM TEST_ORG;

-- 에러발생, TEST_ORG 테이블의 ORG_ID 컬럼을 참조하는 TEST_USER 테이블에 외래키가 있는 레코드가 존재하면 에러발생

ERROR:  update or delete on table "test_org" violates foreign key constraint "test_user_fk1" on table "test_user"

DETAIL:  Key (org_id)=(2) is still referenced from table "test_user".

-- NO ACTION 때문에 삭제안됨

DELETE FROM TEST_ORG WHERE ORG_NAME = '경리부';

-- 정상실행, 경리부에 해당하는 레코드는 TEST_ORG 테이블에서 외래키로 참조하는 레코드가 없기 때문이다.

DELETE FROM TEST_ORG WHERE ORG_NAME = '운영부';

-- 에러발생, 운영부에 해당하는 레코드는 TEST_ORG 테이블에서 외래키로 참조하는 레코드가 존재하기 때문에 삭제안됨

-- NO ACTION 때문에 삭제안됨

ERROR:  update or delete on table "test_org" violates foreign key constraint "test_user_fk1" on table "test_user"

DETAIL:  Key (org_id)=(2) is still referenced from table "test_user".

DELETE FROM TEST_USER WHERE USER_ID = 'admin';

-- 정상실행, TEST_USER 테이블에서 admin에 해당하는 레코드를 다른 테이블에서 참조하는 외래키가 없으므로 삭제된다.

DELETE FROM TEST_USER;

-- 정상실행, TEST_USER 테이블의 컬럼을 참조하는 다른 테이블에 외래키가 없어서 정상 실행된다.

※ 외래키를 사용한 테이블을 삭제할때는 제약조건에 어긋나지 않게 순서대로 삭제해야 삭제된다. @.@

※ 레코드 삭제순서는 참조하는 레코드 -> 참조당하는 레코드

※ 테이블 드롭순서는 참조하는 테이블 -> 참조당하는 테이블