[SQL] 제약 조건

1. 제약 조건

- 테이블의 특정 칼럼에 지정

- 제약 조건을 지정한 칼럼에는 제약 조건에 부합하지 않는 데이터 저장이 불가능

- 제약 조건 지정 방식에 따라 기존 데이터의 수정이나 삭제 가능 여부 또한 영향받음

- 데이터 베이스 설계 시점, 즉 테이블 생성시 주로 지정

- 테이블 생성 후에도 추가, 변경, 삭제 가능

- 데이터 정의어 DDL 에서 활용

 

2. 데이터 무결성 Data Intergrity

- 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장한다는 의미

- 항상 유지해야 하는 기본 규칙을 가지고 있음

- 무결성을 지키기 위한 안정장치로서 제약조건이 중요한 역할을 함

- 테이블 데이터의 삽입, 수정, 삭제 등 모든 과정에서 지켜야 함

  - 영역(도메인) 무결성 Domain Intergrity : 칼럼에 저장되는 값의 적정 여부를 확인. 자료형, 적절한 형식의 데이터, NULL 여부같은 정해 놓은 범위를 만족하는 데이터임을 규정

  - 개체 무결성 Entity Intergrity : 테이블 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가지고 있어야 하며 NULL이 될 수도, 중복될 수도 없음을 규정

  - 참조 무결성 Referencial Intergrity : 참조 테이블의 외래키 값은 참조 테이블의 기본키로서 존재해야 하며 NULL이 가능

 

3. 제약 조건의 종류

- NOT NULL : 지정한 칼럼에 NULL을 허용하지 않음. NULL을 제외한 데이터의 중복은 허용됨

- UNIQUE : 지정된 칼럼이 유일한 값을 가져야 함. 중복이 허용되지 않음. 단, NULL값은 중복에서 제외됨

- PRIMARY KEY : 지정된 칼럼이 유일한 값이면서 NULL을 허용하지 않음. 테이블에 하나만 지정 가능

- FOREIGN KEY : 다른 테이블의 칼럼을 참조하여 존재하는 값만 입력 가능

- CHECK : 설정한 조건식을 만족하는 데이터만 입력 가능

 

3-1. NOT NULL

- 특정 칼럼에 데이터의 중복 여부와는 상관없이 NULL의 저장을 허용하지 않는 제약 조건

- 칼럼 이름과 자료형 뒤에 NOT NULL 키워드를 명시하여 지정

-- 1.
CREATE TABLE TABLE_NOTNULL(
	LOGIN_ID	VARCHAR2(20) NOT NULL,
    LOGIN_PWD	VARCHAR2(20) NOT NULL,
    TEL			VARCHAR2(20)
);

  (1) 이름이 TABLE_NOTNULL 인 테이블의 두 칼럼 LOGIN_ID와 LOGIN_PWD를 NOT NULL 제약조건으로 생성

 

3-2. UNIQUE

- 칼럼에 저장할 데이터의 중복을 허용하지 않고자 할 때 사용하는 제약조건

- NULL은 값이 존재하지 않음을 의미하기 때문에 중복 대상에서는 제외

-- 1.
CREATE TABLE TABLE_UNQ(
	LOGIN_ID	VARCHAR2(20) UNIQUE,
    LOGIN_PWD	VARCHAR2(20) NOT NULL,
    TEL			VARCHAR2(20)
);

  (1) 이름이 TABLE_UNQ 인 테이블의 칼럼 LOGIN_ID를 UNIQUE 제약조건으로 생성

 

3-3. PRIMARY KEY

- UNIQUE와 NOT NULL 제약 조건의 특성을 모두 가지는 제약 조건

- 데이터 중복을 허용하지 않고, NULL도 허용하지 않음

- 테이블에 하나만 지정 가능

- 특정 칼럼을 PRIMARY KEY로 지정하면 해당 칼럼에는 자동으로 인덱스가 만들어짐

- 테이블 데이터를 식별하는 유일한 값을 뜻하므로 일반적으로 테이블 생성 시점에 확정되기 때문에 ALTER 문을 사용하는 경우가 드뭄

-- 1.
CREATE TABLE TABLE_PK(
	LOGIN_ID	VARCHAR2(20) PRIMARY KEY,
    LOGIN_PWD	VARCHAR2(20) NOT NULL,
    TEL			VARCHAR2(20)
);

  (1) 이름이 TABLE_PK 인 테이블의 칼럼 LOGIN_ID를 PRIMARY KEY 제약조건으로 생성

 

3-4. FOREIGN KEY

- 서로 다른 테이블 간 관계를 정의하는 데 사용하는 제약조건

- 특정 테이블(참조 테이블)에서 PRIMARY KEY 계약 조건을 지정한 칼럼을 다른 테이블의 FOREIGN KEY 계약 조건을 지정한 칼럼에서 참조하겠다는 의미로 지정

- 참조 대상 테이블을 부모, 참조하는 테이블을 자식으로 표현

- 참조 테이블의 참조할 칼럼에 존재하지 않는 값을 해당 테이블의 해당 칼럼에 사용할 수 없음

- 일반적으로 테이블 생성 시점에 확정되기 때문에 ALTER 문을 사용하는 경우가 드뭄

CREATE TABLE 테이블 이름 (
	… (다른 열 정의),
    [칼럼 이름] [칼럼 자료형] CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 칼럼)
);

CREATE TABLE 테이블 이름 (
	… (다른 열 정의),
    [칼럼 이름] [칼럼 자료형] REFERENCES 참조 테이블(참조할 칼럼)
);

CREATE TABLE 테이블 이름 (
	… (다른 열 정의),
    CONSTRAINT [제약 조건 이름] FOREIGN KEY(칼럼 이름) REFERENCES 참조 테이블(참조할 칼럼)
);

-- 1.
CREATE TABLE EMP_FK(
	…,
	DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
);

  (1) EMP_FK 테이블의 DEPTNO 칼럼은 DEPT_FK 테이블의 DEPTNO 칼럼을 참조하는 이름 EMPFK_DEPTNO_FK의 외래키

 

+ 참조 테이블의 데이터를 삭제하는 방법

1. 현재 삭제하려는 칼럼 값을 참조하는 데이터를 먼저 삭제한다

  ex) EMP_FK 테이블의 DEPTNO가 10인 데이터를 먼저 삭제한 후 DEPT_FK의 DEPTNO=10 을 삭제

2. 현재 삭제하려는 칼럼 값을 참조하는 데이터를 수정한다

  ex) EMP_FK 테이블의 DEPTNO가 10인 데이터를 다른 숫자 혹은 NULL로 수정한 후 DEPT_FK의 DEPTNO=10 을 삭제

3. 현재 삭제하려는 칼럼을 참조하는 자식 테이블의 FOREIGN KEY 제약 조건을 해제한다

  ex) DEPT_FK 테이블의 자식 테이블 EMP_FK 테이블의 DEPTNO 칼럼의 FOREIGN KEY 제약 조건을 해제

- 위 방법들이 까다롭고 번거롭기 때문에 제약 조건을 처음 지정할 때 추가 옵션을 지정하는 방법을 사용하기도 함

-- 1.
CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 칼럼) ON DELETE CASCADE

-- 2. 
CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 칼럼) ON DELETE SET NULL

  (1) 칼럼 데이터를 삭제할 때 이 데이터를 참조하고 있는 데이터도 함께 삭제 -> 1번 방법

  (2) 칼럼 데이터를 삭제할 때 이 데이터를 참조하고 있는 데이터를 NULL로 수정 -> 2번 방법

 

3-5. CHECK

- 칼럼에 저장할 수 있는 범위 또는 패턴을 정의할 때 사용하는 제약조건

- 단순 연산 뿐 아니라 함수 활용도 가능

-- 1.
CREATE TABLE TABLE_CHECK(
	LOGIN_ID	VARCHAR2(20) CONSTRAINT TBLCK_LGNID_PK PRIMARY KEY,
    LOGIN_PWD	VARCHAR2(20) CONSTRAINT TBLCK_LGNPW_CK CHECK (LENGTH(LOGIN_PWD) > 3),
    TEL			VARCHAR2(20)
);

  (1) (LENGTH(LOGIN_PWD) > 3) : LOGIN_PWD의 길이가 3 이상인 데이터만 저장 가능

 

4. 제약 조건 확인

- 지정한 제약 조건을 확인하려면 USER_CONSTRAINTS 데이터 사전을 활용

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
  FROM USER_CONSTRAINTS;

칼럼 이름	설명
OWNER	제약 조건 소유 계정
CONSTRAINT_NAME	제약 조건 이름
CONSTRAINT_TYPE	제약 조건 종류  C : CHECK, NOT NULL  U : UNIQUE  P : PRIMARY KEY  R : FOREIGN KEY
TABLE_NAME	제약 조건을 지정한 테이블 이름

 

5. 제약 조건 이름 지정

- 이름을 지정해 주지 않으면 오라클이 자동으로 이름을 지정

-- 1. 제약 조건 이름 지정 : 인라인 inline / 열 레벨 column-level 제약 조건 정의
CREATE TABLE TABLE_CST(
	LOGIN_ID	VARCHAR2(20) PRIMARY KEY,
    LOGIN_PWD	VARCHAR2(20) CONSTRAINT TBLCST2_LGNPW_NN NOT NULL,
    NNAME		VARCHAR2(10) CONSTRAINT TBLCST2_NM_UNQ UNIQUE,
    TEL			VARCHAR2(20)
);

-- 2. 제약 조건 이름 지정 : 아웃오브라인 out-of-line / 열 레벨 table-level 제약 조건 정의
CREATE TABLE TABLE_CST(
	LOGIN_ID	VARCHAR2(20),
    LOGIN_PWD	VARCHAR2(20),
    NNAME		VARCHAR2(10),
    TEL			VARCHAR2(20),
    PRIMARY KEY (LOGIN_ID),
    CONSTRAINT TBLCST2_NM_UNQ UNIQUE (NNAME)
);

  (1) 테이블을 생성할 때 제약조건과 제약조건의 이름을 설정

  (2) 테이블을 생성할 때 제약조건과 제약조건의 이름을 설정. NOT NULL 제약조건은 불가능

 

6. 제약 조건 추가

- ALTER 명령어와 MODIFY 키워드 사용

- 해당 칼럼에 제약조건에 맞지 않는 데이터가 존재하면 제약 조건 추가는 실행되지 못함

-- 1. 제약 조건 추가
ALTER TABLE TABLE_CST
MODIFY(TEL NOT NULL);

-- 2. 제약 조건에 이름 지정해서 추가
ALTER TABLE TABLE_CST
MODIFY(TEL CONSTRAINT TBLCST_TEL_NN NOT NULL);

-- 3. 제약 조건 이름 변경
ALTER TABLE TABLE_CST
RENAME CONSTRAINT TBLCST_TEL_NN TO TBLCST2_TEL_NN;

  (1) TABLE_CST 테이블의 TEL 칼럼에 NOT NULL 제약조건을 추가. 이전에 TEL 칼럼에 NULL값이 존재하면 오류가 발생한다. NULL값을 가진 데이터가 존재하지 않을 때만 오류 없이 제약 조건 추가가 가능

  (2) 제약 조건을 추가할 때 이름을 지정

  (3) TABLE_CST 테이블의 TEL 칼럼에 추가한 제약조건 TBLCST_TEL_NN의 이름을 TBLCST2_TEL_NN로 변경

 

7. 제약 조건 삭제

- ALTER 명령어에 DROP CONSTRAINT 키워드를 사용

-- 1.
ALTER TABLE TABLE_CST
DROP CONSTRAINT TBLCST2_TEL_NN;

  (1) TABLE_CST 테이블의 TEL 칼럼에 추가한 제약조건 TBLCST2_TEL_NN을 삭제

 

8. DEFAULT 키워드

- 제약 조건과는 별개로 특정 칼럼에 저장할 값이 지정되지 않았을 경우 기본값을 지정할 수 있음

- 기본값이 지정된 칼럼은 값을 지정하지 않으면 NULL이 아닌 기본값이 저장됨 (암시적 NULL 입력)

CREATE TABLE TABLE_DEFAULT(
	LOGIN_ID	VARCHAR2(20) CONSTRAINT TBLDF_LGNID_PK PRIMARY KEY,
    LOGIN_PWD	VARCHAR2(20) DEFAULT '1234',
    TEL			VARCHAR2(20)
);

-- 1.
INSERT INTO TABLE_DEFAULT (LOGIN_ID, TEL) VALUES ('TEST_ID', '010-1234-5678');

  (1) LOGIN_PWD를 생략해서 데이터를 추가하면 해당 칼럼에 지정된 기본값 '1234'가 추가된다