[Oracle] 제약조건(constraint)

2019. 10. 8. 19:14

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

제약조건(constraint)

제약조건은 데이터베이스 데이터의 정확성을 유지하기 위한 목적으로 사용하며 테이블에 저장할 데이터를 제약하는 특수한 규칙을 의미한다. 열에 제약조건을 지정하면 조건에 맞지 않는 데이터는 저장할 수 없고, 제약 조건 지정 방식에 따라 기존 데이터의 수정이나 삭제 가능 여부도 영향을 받는다. 데이터 무결성을 보장하기 위해 오라클에서는 아래 다섯 가지의 제약조건을 사용한다. 제약조건은 테이블을 생성할 때(데이터베이스 설계 시점) 주로 지정하지만 테이블 생성 후에도 추가·변경·삭제할 수 있다. 제약조건은 데이터 정의어(DDL)에서 활용한다.

■ 제약 조건의 종류

종 류	설 명
NOT NULL	지정한 열에 NULL 을 허용하지 X NULL 을 제외한 데이터의 중복은 허용
UNIQUE	지정한 열의 값이 중복되지 않아야함 (단, NULL 은 값의 중복에서 제외)
PRIMARY KEY	지정한 열이 유일한 값이면서 NULL 을 허용하지 X PRIMARY KEY 는 테이블에 하나만 지정 가능
FOREIGN KEY	외래키, 외부키라고도 한다. 다른 테이블의 열을 참조하여 존재하는 값만 입력 가능
CHECK	설정한 조건식을 만족하는 데이터만 입력 가능

■ 데이터 무결성(data integrity)

종 류	설 명
영역 무결성 (domain integrity)	열에 저장되는 값의 적정 여부를 확인. 자료형, 적절한 형식의 데이터, NULL 여부같은 정해놓은 범위를 만족하는 데이터임을 규정
개체 무결성 (entity integrity)	테이블 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가지고 있어야 하며 NULL 이 될 수 없고 중복될 수도 없음을 규정
참조 무결성 (referential integrity)	참조 테이블의 외래키 값은 참조 테이블의 기본키로서 존재해야 하며 NULL 이 가능

데이터 무결성은 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장하는 것이며 이를 위해 항상 유지해야 하는 기본 규칙을 가지고 있다. 제약조건은 이러한 데이터의 무결성을 지키기 위한 것이며, 테이블 데이터의 삽입(insert), 수정(update), 삭제(delete)등 모든 과정에서 지켜져야 한다.

■ 제약 조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

지정한 제약 조건 정보를 확인하려면 다음과 같은 USER_CONSTRAINTS 데이터 사전을 활용한다.

열 이름	설 명
OWNER	제약 조건 소유 계정
CONSTRAINT_NAME	제약 조건 이름(지정하지 않을 경우 오라클이 자동으로 지정)
CONSTRAINT_TYPE	제약 조건 종류 C : CHECK , NOT NULL U : UNIQUE P : PRIMARY KEY R : FOREIGN KEY
TABLE_NAME	제약 조건을 지정한 테이블 이름

■ 제약 조건 지정 방식

1. 인라인(inlne) or 열 레벨(column-level) 제약 조건 정의

CREATE TABLE TABLE_NAME(
    COL1 VARCHAR2(20) CONSTRAINT CONSTRAINT_NAME PRIMARY KEY,   -- 제약조건에 이름지정
    COL2 VARCHAR2(20) NOT NULL,     -- 이름을 지정하지 않는 경우
    COL3 VARCHAR2(20)
);

열 바로 옆에 제약 조건을 지정하는 형식이다. 앞으로 나올 방식들은 모두 이 방식이다.

2. 아웃 오브 라인(out-of line) or 테이블 레벨(table-level) 제약 조건 정의

CREATE TABLE TABLE_NAME(
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    COL3 VARCHAR2(20),
    PRIMARY KEY (COL1),  -- 제약조건에 이름을 지정하지 않는 경우
    CONSTRAINT CONSTRAINT_NAME UNIQUE (COL2)    -- 제약조건에 이름을 지정하는 경우
);

열을 먼저 정의한 후에 제약 조건을 테이블 단위로 지정하는 방식이다.

이 방식은 NOT NULL 을 제외한 제약 조건 지정이 가능하다.

요 약

■ 앞으로 나올 제약조건들은 키워드만 다르고 제약조건을 지정하고, 수정, 삭제하는 방법이 거의 동일하다.
■ PRIMARY KEY 와 FOREIGN KEY 는 주로 테이블 설계 단계에서 정해지기 때문에 ALTER 문을 통한 변경이 드물다.

1. NOT NULL

NOT NULL 은 특정 열에 데이터의 중복 여부와는 관계없이 NULL 의 저장을 허용하지 않는 제약조건이다. 반드시 열에 값이 존재해야만 하는 경우에 지정한다.

■ 테이블을 생성하며 제약조건 지정

-- 1) NOTNULL을 이용한 테이블생성
-- [1-1] 이름을 지정하지 X 경우
CREATE TABLE TABLE_NOTNULL(
    LOGIN_ID    VARCHAR2(20) NOT NULL,
    LOGIN_PWD   VARCHAR2(20) NOT NULL,
    TEL         VARCHAR2(20)
);

-- [1-2] 제약조건에 이름을 지정하는 경우 -> CONSTRAINT 키워드를 사용
CREATE TABLE TABLE_NOTNULL2(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
    LOGIN_PWD   VARCHAR2(20) CONSTRAINT TBLNN2_LGNPWNN NOT NULL,
    TEL         VARCHAR2(20)
);


-- 2) 테이블 열정보 조회
DESC TABLE_NOTNULL;

NOT NULL 은 특정 열에 데이터 중복 여부와 관계없이 NULL 의 저장을 허용하지 않는 제약조건이다. 반드시 열에 값이 존재해야만 하는 경우에 지정한다. NOT NULL 은 열 이름과 자료형 뒤에 NOT NULL 키워드를 명시하여 지정한다. 열의 제약 조건으로 NOT NULL 을 지정하고 나면 UPDATE 문을 사용하여 열 값을 NULL 로 수정하는 것도 불가능하다.

-- ex1) NULL 삽입 테스트
-- [1-1] NOT NULL로 지정된 열에 NULL 삽입
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', NULL, '010-1234-5678');


-- [1-2] 제약 조건이 없는 다른 열에 NULL 삽입
INSERT INTO TABLE_NOTNULL(LOGIN_ID, LOGIN_PWD)	-- TEL열을 지정하지 않아서 암시적으로 NULL을 삽입한다.
VALUES('TEST_ID_01', '1234');


-- ex2) 데이터 수정 테스트(NOT NULL로 지정된 열 데이터를 UPDATE문을 이용해서 NULL로 수정)
UPDATE TABLE_NOTNULL
   SET LOGIN_PWD = NULL
 WHERE LOGIN_ID = 'TEST_ID_01';

NOT NULL 로 지정한 LOGIN_PWD 에 NULL 을 저장하려고 했기 때문에 오류가 난다.

NOT NULL 을 지정하지 않은 열은 NULL 이 저장되는 것을 확인할 수 있다.

NOT NULL 로 지정된 LOGIN_PWD 열을 NULL 로 수정하려고 했기 때문에(제약조건에 위배되기 때문에) UPDATE 문이 실행되지 않는다.

■ 제약 조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;

제약조건에 이름을 지정한 경우에는 CONSTRAINT_NAME 에 지정한 이름이 나오지만(파란색 부분) 이름을 지정하지 않은 경우(빨간색 부분)에는 오라클이 임의로 정해준 이름이 붙는 것을 확인할 수 있다. 또한 제약조건들이 NOT NULL 이기 때문에 CONSTRAINT_TYPE 이 모두 C 로 나온다, TABLE_NAME 에는 제약조건을 지정한 테이블명이 나온다.

■ 제약조건 수정

-- 1) 이미 생성된 테이블에 NOT NULL제약조건 추가
--[1-1] 이름을 지정하지 X 경우
ALTER TABLE TABLE_NOTNULL
MODIFY(TEL NOT NULL);	-- TEL열을 NOT NULL로 지정

--[1-2] 이름을 지정하는 경우 -> CONSTRAINT 키워드 사용
ALTER TABLE TABLE_NOTNULL2
MODIFY(TEL CONSTRAINT TBLNN_TEL_NN NOT NULL);

-- 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;


-- 2) 생성한 제약 조건의 이름 변경 -> ALTER 명령어에 RENAME CONSTRAINT ~ TO를 사용
ALTER TABLE TABLE_NOTNULL2
RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;	-- TBLNN_TEL_NN를 TBLNN2_TEL_NN으로 변경

ALTER 명령어로 이미 생성되어 있는 테이블에 NOT NULL 제약조건을 추가할 수 있다.

열에 NOT NULL 을 지정하려면 해당 열의 데이터에 NULL 값이 존재하면 안 된다. 열 데이터에 NULL 값이 존재하는 상태에서 NOT NULL 을 지정하면 위처럼 오류가 발생한다.

-- 추가) 열의 데이터가 제약조건에 위배되는 경우 열의 데이터를 수정한 다음 제약조건을 추가해야한다.
-- 열 데이터 수정
UPDATE TABLE_NOTNULL
   SET TEL = '010-1234-5678'
 WHERE LOGIN_ID = 'TEST_ID_01';
 
 -- 수정된 테이블 확인
 SELECT * FROM TABLE_NOTNULL;

열 데이터 중 제약조건에 위배되는 데이터가 있다면 UPDATE 문으로 수정한 뒤 제약조건을 추가해야 오류가 발생하지 않는다.

■ 제약조건 삭제

ALTER TABLE TABLE_NOTNULL2
DROP CONSTRAINT TBLNN2_TEL_NN;

DESC TABLE_NOTNULL2;

ALTER 명령어에 DROP CONSTRAINT 키워드를 사용하면 제약조건을 삭제할 수 있다.

그런데 가끔 제약조건을 삭제하고 테이블을 삭제해야 되는데 테이블만 삭제한 경우 제약조건들의 이름이 BIN$... 으로 바뀌어 삭제할 수 없는 경우가 있다.

PURGE RECYCLEBIN;

그럴 때 위의 쿼리를 입력하면 RECYCLEBIN 이 비워졌다는 결과와 함께 제약조건들이 삭제된다. (휴지통과 비슷한 기능인 듯)

2. UNIQUE

열에 저장할 데이터의 중복을 허용하지 않을 때 사용한다. 다만 NULL 은 값이 존재하지 않음을 의미하기 때문에 중복 대상에서는 제외된다.

■ 테이블을 생성하며 제약조건 지정

-- 1) UNIQUE를 이용한 테이블생성
-- [1-1] 이름 지정하지 X 경우
CREATE TABLE TABLE_UNIQUE(
    LOGIN_ID VARCHAR2(20) UNIQUE,	-- NOT NULL과 방식은 같다.
    LOGIN_PWD VARCHAR2(20) NOT NULL,
    TEL VARCHAR(20)
);

-- [1-2] 제약조건에 이름을 지정하는 경우 -> CONSTRAINT 키워드를 사용
CREATE TABLE TABLE_UNIQUE2(
    LOGIN_ID VARCHAR2(20) CONSTRAINT TLBUNQ2_LGNID_UNQ UNIQUE,
    LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLUNQ2_LGNPW_NN NOT NULL,
    TEL VARCHAR2(20)
);


-- 2) 테이블 열정보 조회
DESC TABLE_UNIQUE;

UNIQUE 도 마찬가지로 CREATE 문으로 테이블을 생성할 때 지정할 수 있다. 지정하는 방식은 NOT NULL 과 같다.

-- ex1) 중복 데이터 삽입 테스트
-- [1-1] UNIQUE로 지정된 열에 중복된 데이터 삽입(반복실행)
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('TEST_ID_01', 'PWD01', '010-1234-5678');	-- LOGIN_ID가 UNIQUE이기 때문에 반복실행하면 오류가 발생한다.

-- [1-2] UNIQUE로 지정되지 X 열에 중복된 데이터 삽입
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES ('TEST_ID_02', 'PWD01', '010-1234-5678');	-- LOGIN_PWD는 NOT NULL만 되어있어서 중복데이터가 허용된다.

-- [1-3] UNIQUE로 지정된 열에 NULL값 삽입
INSERT INTO TABLE_UNIQUE(LOGIN_ID, LOGIN_PWD, TEL)
VALUES (NULL, 'PWD01', '010-1234-5678');	-- NULL은 중복값이 아니기 때문에(NULL끼리 비교도 불가능) 삽입 가능하다.

-- 삽입 결과 조회
SELECT * FROM TABLE_UNIQUE;


-- ex2) 데이터 수정 테스트(UNIQUE로 지정된 열을 UPDATE문을 이용해 중복된 데이터로 수정)
UPDATE TABLE_UNIQUE
   SET LOGIN_ID = 'TEST_ID_01'
 WHERE LOGIN_ID IS NULL;

[1-1]의 INSERT 문을 실행하면 정상적으로 테이블에 데이터가 추가되지만

[1-1]을 반복 실행하면 UNIQUE 로 지정된 열인 LOGIN_ID 에 중복된 값이 들어오기 때문에 제약조건 위배로 오류가 발생한다.

반면에 LOGIN_PWD 는 NOT NULL 조건만 있기 때문에 중복은 허용된다.

UNIQUE 제약조건에 NULL 저장은 가능하다. NULL 은 존재하지 않는 값 또는 해당 사항이 없다는 의미로 사용되는 특수한 값이기 때문에 NULL 과 NULL 을 비교했을 때는 값이 같은지 확인할 수 없다.

UNIQUE 로 지정된 열의 데이터를 중복되는 데이터로 수정하려고 했기 때문에 제약조건 위배로 오류가 발생한다.

■ 제약 조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%'; 	-- 편의상 방금 생성했던 테이블만 볼수 있도록 조건식을 지정했다.

제약조건이 UNIQUE 이기 때문에 데이터 사전인 USER_CONSTRAINTS 의 CONSTRAINT_TYPE 열 값이 U 로 나온다.

NOT NULL 에서 설명한 것과 마찬가지로 제약조건의 이름을 지정하지 않았을 경우(빨간색) CONSTRAINT_NAME 을 오라클이 자동으로 정해준다.

■ 제약조건 수정

-- 1) 이미 생성된 테이블에 UNIQUE 제약조건 추가
-- [1-1] 이름을 지정하지 X 경우
ALTER TABLE TABLE_UNIQUE
MODIFY(TEL UNIQUE);

--[1-2] 이름을 지정하는 경우 -> CONSTRAINT 키워드 사용
ALTER TABLE TABLE_UNIQUE2
MODIFY(TEL CONSTRAINT TBLUNQ_TEL_UNQ UNIQUE);

-- 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
  FROM USER_CONSTRAINTS
 WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%'; 


-- 2) 생성한 제약 조건의 이름 변경 -> ALTER 명령어에 RENAME CONSTRAINT ~ TO를 사용
ALTER TABLE TABLE_UNIQUE2
RENAME CONSTRAINT TBLUNQ_TEL_UNQ TO TBLUNQ2_TEL_UNQ;

ALTER 명령어로 이미 생성되어 있는 테이블에 UNIQUE 제약 조건을 추가할 수 있다.

[1-1]을 실행하면 TEL 열에 이미 중복 데이터가 있기 때문에 제약조건 위배로 오류가 발생한다.

UPDATE TABLE_UNIQUE
   SET TEL = NULL;

이런 식으로 TEL 열의 데이터를 모두 NULL 로 바꾸거나 중복되지 않는 값으로 바꾼 뒤에 UNIQUE 제약조건을 추가해야 오류가 발생하지 않는다.

■ 제약조건 삭제

ALTER TABLE TABLE_UNIQUE2
DROP CONSTRAINT TBLUNQ2_TEL_UNQ;

-- 삭제된 제약조건 확인
SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
  FROM USER_CONSTRAINTS
 WHERE TABLE_NAME LIKE 'TABLE_UNIQUE%';

UNIQUE 제약조건도 ALTER 명령어에 DROP CONSTRAINT 키워드를 사용하면 제약조건을 삭제할 수 있다.

3. PRIMARY KEY

PRIMARY KEY 는 UNIQUE 와 NOT NULL 의 특성을 모두 가지는 제약조건이다. 테이블에 하나밖에 지정할 수 없기 때문에 테이블의 각 행을 식별하는데 활용된다. 또한 특정 열을 PRIMARY KEY 로 지정하면 해당 열에는 자동으로 인덱스가 만들어진다. (데이터에 따라 주민등록번호와 같은 데이터들은 PRIMARY KEY 에 적합한 특성을 가지고 있어도 PRIMARY KEY 로 지정하지 않는다.)

■ 테이블을 생성하며 제약조건 지정 & 자동 생성되는 인덱스 확인

-- 1) PRIMARY KEY를 이용한 테이블생성
-- [1-1] 이름을 지정하지 X 경우
CREATE TABLE TABLE_PK(
    LOGIN_ID VARCHAR2(20) PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) NOT NULL,
    TEL VARCHAR(20)
);

-- [1-2] 제약조건에 이름을 지정하는 경우 -> CONSTRAINT 키워드를 사용
CREATE TABLE TABLE_PK2(
    LOGIN_ID VARCHAR2(20) CONSTRAINT TBLPK2_LGNID_PK PRIMARY KEY,
    LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLPK2_LGNPW_NN NOT NULL,
    TEL VARCHAR(20)
);


-- 2) 테이블 열정보 조회
DESC TABLE_PK;


-- 3) PRIMARY KEY를 생성할 때 자동 생성된 INDEX 확인
SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME LIKE 'TABLE_PK%';

데이터 사전인 USER_INDEXES 를 조회한 결과이다. PRIMARY KEY 제약조건을 지정한 열에는 자동으로 인덱스가 만들어지며, 인덱스의 이름은 PRIMARY KEY 제약조건과 같은 이름이 붙여진다.

-- ex1) PRIMARY KEY 데이터 삽입 테스트
-- [1-1] PRIMARY KEY로 지정된 열에 데이터 삽입
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', 'PWD01', '010-1234-5678');

-- [1-2] PRIMARY KEY로 지정된 열에 중복된 데이터 삽입
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES('TEST_ID_01', 'PWD02', '010-1234-5678');

-- [1-3] PRIMARY KEY로 지정된 열에 NULL 데이터 삽입(명시적인 NULL삽입)
INSERT INTO TABLE_PK(LOGIN_ID, LOGIN_PWD, TEL)
VALUES(NULL, 'PWD02', '010-1234-5678');

-- [1-4] PRIMARY KEY로 지정된 열에 NULL 데이터 삽입(암시적인 NULL삽입)
INSERT INTO TABLE_PK(LOGIN_PWD, TEL)	-- NULL로 지정할 열을 빼고 삽입하면 암시적으로 NULL이 삽입된다.
VALUES('PWD02', '010-1234-5678');

-- 삽입 결과 조회
SELECT * FROM TABLE_PK;

PRIMARY KEY 제약조건을 지정한 열에는 중복 값과 NULL 이 허용되지 않기 때문에 [1-1]은 문제없이 실행되지만

[1-2]는 LOGIN_ID 가 중복되기 때문에 오류가 발생한다.

마찬가지로 NULL 도 저장할 수 없다.

■ 제약 조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
  FROM USER_CONSTRAINTS
 WHERE TABLE_NAME LIKE 'TABLE_PK%';	-- 편의상 방금 생성했던 테이블만 볼수 있도록 조건식을 지정했다.

제약조건이 PRIMARY KEY 기 때문에 데이터 사전인 USER_CONSTRAINTS 의 CONSTRAINT_TYPE 열 값이 P 로 나온다.

NOT NULL 에서 설명한 것과 마찬가지로 제약조건의 이름을 지정하지 않았을 경우(빨간색) CONSTRAINT_NAME 을 오라클이 자동으로 정해준다.

■ 제약조건 수정 & 삭제

PRIMARY KEY 역시 ALTER 문의 MODIFY , RENAME , DROP 을 통해 추가·수정·삭제 등의 수행이 가능하다. 보통 PRIMARY KEY 제약조건은 테이블의 생성 시점에 확정되는 경우가 대부분이고, 테이블당 하나의 열만 지정이 가능하기 때문에 ALTER 문을 사용하는 경우가 드물다. PRIMARY KEY 도 마찬가지로 제약 조건을 지정하려는 열에 중복 값이나 NULL 이 있을 경우 제약조건이 적용되지 않는다.

4. FOREIGN KEY

FOREIGN KEY 는 외래키, 외부키라고도 하며 서로 다른 테이블 간의 관계를 정의하는 데 사용하는 제약 조건이다. 특정 테이블에서 PRIMARY KEY 제약 조건을 지정한 열을 다른 테이블의 특정 열에서 참조하겠다는 의미로 지정할 수 있다.

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP', 'DEPT');

SELECT * FROM DEPT;

FOREIGN KEY 예시로 USER_CONSTRAINTS 데이터 사전에서 EMP 테이블과 DEPT 테이블의 제약조건을 확인해보자.

CONSTRAINT _TYPE 열 값이 R 이면 FOREIGN KEY 를 의미한다.

R_CONSTRAINT_NAME 의 PK_DEPT 는 DEPT 테이블의 PRIMARY KEY 인 DEPTNO 열을 참조한다는 의미이다.

이렇게 참조 관계를 정의하면 EMP 테이블의 DEPTNO 열에는 DEPT 테이블의 DEPTNO 열에 존재하는 값과 NULL 만 저장할 수 있게 된다. ( DEPTNO 의 값들인 10 , 20 , 30 , 40 과 NULL )

INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES(9999, '홍길동', 'CLERK', '7788', TO_DATE('2017/04/30', 'YYYY/MM/DD'), 1200, NULL, 50);

따라서 위의 INSERT 문처럼 DEPT 테이블의 DEPTNO 열에 없는 값인 50 을 넣으려고 하면 오류가 나서 실행되지 않는다.

부모 키가 없다는 말은 DEPT 테이블의 DEPTNO 열에 50 이 존재하지 않는다는 의미이다.

(참조 대상 테이블을 부모, 참조하는 테이블을 자식으로 표현한다.)

■ FOREIGN KEY 지정

-- 1) FOREIGN KEY 지정하기
CREATE TABLE 테이블 이름(
    ...(다른 열 정의),
    열 자료형 CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 열)
);


-- 2) 제약 조건 이름을 지정하지 않는 경우
CREATE TABLE 테이블 이름(
    ...(다른 열 정의),
    열 자료형 REFERENCES 참조 테이블(참조할 열)
);


-- 3) 열을 모두 정의한 후 제약 조건을 지정 -> CONSTRAINT 키워드 사용
CREATE TABLE 테이블 이름(
    ...(다른 열 정의),
    CONSTRAINT [제약 조건 이름] FOREIGN KEY(열)
    REFERENCES 참조 테이블(참조할 열)
);

+ FOREIGH KEY 확인해보기

-- ex) FOREIGN KEY 확인해보기
-- 1) EMP 및 DEPT 테이블과 열 구성이 같은 테이블 생성
-- [1-1] DEPT 테이블
CREATE TABLE DEPT_FK(
    DEPTNO NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
    NAME VARCHAR2(14),
    LOC	VARCHAR2(13)
);

DESC DEPT_FK;

-- [1-2] EMP 테이블
CREATE TABLE EMP_FK(
    EMPNO   NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
    ENAME   VARCHAR2(10),
    JOB     VARCHAR2(9),
    MGR     NUMBER(4),
    HIREDATE    DATE,
    SAL     NUMBER(7,2),
    COMM    NUMBER(7,2),
    DEPTNO  NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)
);

DESC EMP_FK;


-- 2) 데이터 삽입 테스트
-- DEPT_FK에는 데이터가 없는 상태이기 때문에 EMP_FK에 데이터를 추가할때 DEPTNO를 지정하면 오류가 난다.

-- [2-1] EMP_FK에 데이터 삽입(DEPT_FK에 데이터가 없을 떄)
INSERT INTO EMP_FK
VALUES (9999, 'TEST_NNAME', 'TEST_JOB', NULL,TO_DATE('2001/01/01', 'YYYY/MM/DD'), 3000, NULL, 10);

-- [2-2] DEPT_FK에 데이터 삽입
INSERT INTO DEPT_FK
VALUES(10, 'TEST_DNAME', 'TEST_LOC');

SELECT * FROM DEPT_FK;

-- [2-3] 오류가 났던 [2-1] 다시 실행해보기

DEPT_FK 와 EMP_FK 를 만들고 난 직후에는 데이터가 없기 때문에 EMP_FK 테이블에 데이터를 추가할 때 DEPTNO 를 지정하면 오류가 발생한다. EMP_FK 의 DEPTNO 는 DEPT_FK 의 DEPTNO 를 참조하기 때문에 DEPT_FK 의 DEPTNO 열에 없는 값을 사용할 수 없다.

DEPT_FK 에 있는 값만 EMP_FK 에서 사용 가능한 것을 확인할 수 있다.

■ FOREIGN KEY로 참조된 행 데이터 삭제하기

DELETE FROM DEPT_FK
WHERE DEPTNO = 10;

테이블 DEPT_FK 의 DEPTNO 열에 10 이 저장되어 있고 EMP_FK 에 DEPTNO 10 을 참조하는 데이터가 있는 경우 DEPT_FK 의 DEPTNO 열에 저장된 10 은 삭제할 수 없다.

자식 레코드(삭제하려는 DEPTNO 값을 참조하는 데이터)가 존재하기 때문에 오류가 발생한다.

DEPT_FK 테이블의 데이터를 삭제하려면 다음 방법 중 한 가지를 사용해야 한다.

현재 삭제하려는 열 값을 참조하는 데이터를 먼저 삭제한다.
ex) EMP_FK 테이블의 DEPTNO 가 10 번인 데이터를 삭제한 후 DEPT_FK 의 DEPTNO 10 을 삭제
현재 삭제하려는 열 값을 참조하는 데이터 수정
ex) EMP_ FK 테이블의 DEPTNO 가 10 번인 데이터를 다른 부서 번호나 NULL 로 변경한 후 DEPT_FK 의 DEPTNO 10 을 삭제
현재 삭제하려는 열을 참조하는 자식 테이블의 FOREIGN KEY 제약조건을 해제한다

하지만 위의 3가지 방법은 삭제할 데이터를 참조하는 데이터의 수정 또는 삭제 작업을 선행해야 하므로 번거롭고, FOREIGN KEY 제약 조건을 해제할 수 없는 경우도 있으므로 연결된 데이터의 삭제 작업은 매우 까다롭다. 따라서 제약 조건을 처음 지정할 때 추가 옵션을 지정하는 방법을 사용하기도 한다. 이 방법은 데이터 삭제와 더불어 삭제할 데이터를 참조하는 처리를 어떻게 할지 정할 수 있다.

1. 열 데이터를 삭제할 때 이 데이터를 참조하고 있는 데이터도 함께 삭제

CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 열) ON DELETE CASCADE

DEPT_FK 테이블의 DEPTNO 열 값이 10 인 데이터를 삭제하면 이를 참조하는 EMP_FK 테이블의 DEPTNO 10 인 데이터들도 함께 삭제된다.

2. 열 데이터를 삭제할 때 이 데이터를 참조하는 데이터를 NULL로 수정

CONSTRAINT [제약 조건 이름] REFERENCES 참조 테이블(참조할 열) ON DELETE SET NULL

DEPT_FK 테이블의 DEPTNO 열 값이 10 인 데이터를 삭제하면 이를 참조하는 EMP_FK 테이블의 DEPTNO 열 값이 10 인 데이터들을 NULL 로 수정한다.

■ 제약조건 수정 & 삭제

FOREIGN KEY 도 ALTER 문을 사용한 제약조건의 추가·변경·삭제 등 여러 가지 기능을 사용할 수 있지만 PRIMARY KEY 처럼 테이블을 설계하는 시점에 제약조건이 결정되는 경우가 많기 때문에 변경되는 경우가 드물다.

5. CHECK

열에 저장할 수 있는 값의 범위나 패턴을 정의할 때 CHECK 제약조건을 지정한다.

■ 테이블을 생성하며 제약조건 지정

CREATE TABLE TABLE_CHECK(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLCK_LOGINID_PK PRIMARY KEY,
    LOGIN_PWD   VARCHAR2(20) CONSTRAINT TBLCK_LOGINPW_CK CHECK (LENGTH(LOGIN_PWD) > 3),	-- LOGIN_PWD열 길이가 3이상인 데이터만 저장 가능
    TEL VARCHAR2(20)
);

DESC TABLE_CHECK;

-- ex) 데이터 삽입 테스트
-- 1) CHECK 제약조건에 위배되는 데이터 삽입 
INSERT INTO TABLE_CHECK
VALUES ('TEST_ID', '123', '010-1234-5678');


-- 2) CHECK 제약조건에 맞는 데이터 삽입 
INSERT INTO TABLE_CHECK
VALUES ('TEST_ID', '1234', '010-1234-5678');

CHECK 제약조건에 맞지 않는 데이터를 삽입할 경우 오류가 발생한다.

■ 제약 조건 확인

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME LIKE 'TABLE_CHECK';	-- 편의상 조금전에 생성한 테이블만 볼 수 있도록 조건식을 지정했다.

CHECK 제약조건도 USER_CONSTRAINTS 데이터 사전에서 확인할 수 있다.

CHECK 와 NOT NULL 은 모두 CONSTRAINT_TYPE 이 C 로 나온다.

DEFAULT

제약 조건과는 별개로 DEFAULT 키워드를 사용하면 특정 열에 저장할 값이 지정되지 않았을 경우 사용되는 기본값(default)을 지정할 수 있다.

■ 테이블을 생성하며 제약조건 지정

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

DESC TABLE_DEFAULT;

-- ex) 데이터 삽입 테스트
-- 1) 명시적으로 NULL을 지정한 경우
INSERT INTO TABLE_DEFAULT VALUES('TEST_ID', NULL, '010-1234-5678');

-- 2) 암시적으로 NULL을 지정한 경우
INSERT INTO TABLE_DEFAULT(LOGIN_ID, TEL) VALUES ('TEST_ID2', '010-1234-5678');


-- 결과 확인
SELECT * FROM TABLE_DEFAULT;

명시적으로 NULL 을 지정하면 NULL 이 들어가지만 LOGIN_PWD 열 값을 지정하지 않으면(==암시적으로 NULL 을 삽입하면) 기본값인 1234 가 들어간다.

제약 조건 활성화/비활성화

필요에 따라 제약조건을 비활성화하거나 비활성화되어 있는 제약조건을 다시 활성화할 수 있다. 비활성화에는 DISABLE 절을, 활성화에는 ENABLE 절을 사용한다.

1. 제약조건 비활성화

ALTER TABLE 테이블 이름
DISABLE [NOVALIDATE / VALIDATE(선택)] CONSTRAINT 제약조건이름;

2. 제약조건 활성화

ALTER TABLE 테이블 이름
ENABLE[NOVALIDATE / VALIDATE(선택)] CONSTRAINT 제약조건이름;

저작자표시 비영리 변경금지

'Back end > Database' 카테고리의 다른 글

[Oracle] PL/SQL (0)	2019.10.16
[Oracle] 사용자, 권한, 롤 관리 (0)	2019.10.12
[Oracle] 객체 종류 (0)	2019.09.30
[Oracle] 데이터 정의어 (DDL : Data Definition Language) (0)	2019.09.29
[Oracle] 트랜잭션(transaction)과 TCL, 세션(session) (0)	2019.09.24

pridiot