14. 제약 조건constraint

14.1 제약 조건이란?

 : 테이블 열에 저장될 데이터의 특성과 조건을 지정. 보호를 위한 최소한의 제약 조건

• NOT NULL : 지정 열에 NULL을 허용하지 않음. 데이터 중복 허용.
• UNIQUE : 지정한 열이 유일한 값을 가져야 함. 중복 불허. NULL은 중복에서 제외.
• PRIMARY KEY. : 중복을 허용하지 않으면서 NULL을 허용하지 않음. 테이블에 하나만 지정 가능.
• FOREIGN KEY : 다른 테이블의 열을 참조하여 존재하는 값만 입력 가능.
• CHECK : 조건식을 만족하는 데이터만 입력 가능.

 

14.2. NOT NULL : NULL을 저장하지 않는다

CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)
,        ENAME		VARCHAR2(10)	NOT NULL
,        JOB		VARCHAR2(9)	NOT NULL
,        MGR		NUMBER(4)
,        SAL		NUMBER(7, 2)	NOT NULL
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)
);

ENAME, JOB, SAL 열은 NULL을 허용하지 않는, 테스트용 테이블 EMP2를 만들었다.

[제약조건 확인 : USER_CONSTRAINTS]

• OWNER : 제약조건 소유 계정
• CONSTRAINT_NAME : 제약 조건 이름
• CONSTRAINT_TYPE : 제약 조건 종류
  • C
  • U
• TABLE_NAME : 제약 조건을 지정한 테이블 이름

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

보기 안 좋으니까 다음과 같이 COL 명령어를 써준다.

COL TABLE_NAME FORMAT A10 
COL CONSTRAINT_NAME FORMAT A15
COL CONSTRAINT_TYPE  FORMAT A15

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

테이블 생성 시 제약조건의 이름을 설명하지 않아, ORACLE이 임의의 제약조건 이름 SYS_C~~를 붙였다.

제약조건의 관리를 위해 테이블 생성 시 제약조건명을 설정해주는 것이 좋다.

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	CONSTRAINT EMP2_JOB_NN NOT NULL
,        MGR		NUMBER(4)
,        SAL		NUMBER(7, 2)	CONSTRAINT EMP2_SAL_NN NOT NULL
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)
);

기존 EMP2 테이블을 삭제 후 재생성

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

제약조건명이 설정 되었다.

DESC EMP2

제약조건이 설정된 EMP2 테이블을 확인

INSERT INTO EMP2
VALUES (1111, 'KIMHUGO', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

INSERT INTO EMP2
VALUES (2222, 'KT', 'CLERK', NULL, NULL, NULL, SYSDATE, 20);

김휴고는 테이블에 잘 저장되지만, SAL이 NULL인 케이티는 저장되지 않았다.

14.3. UNIQUE : 중복을 허락하지 않는다, NULL은 허용

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	CONSTRAINT EMP2_EMPNO_UQ UNIQUE
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)
);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

U : UNIQUE 제약조건으로 만들어진 열이 있다.

INSERT INTO EMP2
VALUES (1111, 'KIMHUGO', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

INSERT INTO EMP2
VALUES (NULL, 'KT', 'CLERK', NULL, NULL, NULL, SYSDATE, 20);

INSERT INTO EMP2
VALUES (1111, 'RURU', 'ANALYST', NULL, 2000, NULL, SYSDATE, 30);

김휴고와 무급봉사자 케이티는 정상적으로 추가. 애널리스트 루루는 사원번호가 중복되어 추가되지 않았다.

14.4 PRIMARY KEY : 유일하게 하나만 있으며 NULL을 허용하지 않는다.

• 중복 X
• NULL 저장 X
• 자동으로 인덱스 생성

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)
);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP2';

P라는 제약 조건이 설정되었다.

 

EMPNO가 NOT NULL로 설정되어 있다.

INSERT INTO EMP2
VALUES (1111, 'KIMHUGO', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

INSERT INTO EMP2
VALUES (NULL, 'KT', 'CLERK', NULL, NULL, NULL, SYSDATE, 20);

INSERT INTO EMP2
VALUES (1111, 'RURU', 'ANALYST', NULL, 2000, NULL, SYSDATE, 30);

케이티도 루루도 추가되지 않는다.

14.5 FOREIGN KEY : 다른 테이블과 관계를 맺는다

• 다른 테이블의 PRIMARY KEY를 참조
• 참조하고 있는 키의 데이터와 NULL만 참조
• 부모-자식 테이블 중 (당연히) 부모 테이블이 먼저 생성되어야 하며
• 자식이 참조하는 부모의 컬럼은 유일해야 한다.

CREATE TABLE DEPT2 (
	DEPTNO		NUMBER(2)	CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY
,	DNAME		VARCHAR2(10)
,	LOC		VARCHAR2(10)
);

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)	CONSTRAINT EMP2_DEPTNO_FK REFERENCES DEPT2 (DEPTNO)
);

DEPT2와 EMP2 테이블은 명시적 관계로 연결, 생성되었다.

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP2', 'DEPT2');

EMP2에 R 타입, 곧 foreign key 제약 열이 생겼다.

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)	CONSTRAINT EMP2_MGR_FK REFERENCES EMP2 (EMPNO)
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)	CONSTRAINT EMP2_DEPTNO_FK REFERENCES DEPT2 (DEPTNO)
);

MGR열 또한 셀프 조인처럼 자기 자신을 참조하여 포린키로 설정할 수 있다.

 

• 부모는 자식이 존재하는 한, 함부로 변경되거나 삭제될 수 없다.
• 자식은 존재하지 않는 부모를 가질 수 없다.(당연한 거 아닌가?)

INSERT INTO DEPT2
VALUES (10, '개발부', '서울');

INSERT INTO EMP2 (EMPNO, ENAME, JOB, SAL, HIREDATE, DEPTNO)
VALUES (1111, 'KIMHUGO', 'MANAGER', 3000, SYSDATE, 10);

DELETE FROM DEPT2
WHERE DEPTNO = 10;

10번 부서는 삭제될 수 없다. 자식이 있기 때문.

UPDATE EMP2
SET DEPTNO = NULL
WHERE EMPNO = 1111;

FOREIGN KEY는 NULL, 곧 부모가 없을 수 있다.

DELETE FROM DEPT2
WHERE DEPTNO = 10;

다시 삭제를 시도하면 성공한다.

INSERT INTO EMP2 (EMPNO, ENAME, JOB, MGR, SAL, HIREDATE, DEPTNO)
VALUES (2222, 'RURU', 'ANALYST', 1111, 2500, SYSDATE, 20);

20번 부서가 없어서 루루를 채용할 수 없다.

INSERT INTO DEPT2
VALUES (20, '인사부', '장유');

INSERT INTO EMP2 (EMPNO, ENAME, JOB, MGR, SAL, HIREDATE, DEPTNO)
VALUES (2222, 'RURU', 'ANALYST', 1111, 2500, SYSDATE, 20);

장유에 인사부를 만들어 루루를 채용했다. 직무와 부서가 맞지 않는 거 같지만...ㅎ

14.6. CHECK : 데이터의 형태와 범위를 정한다

WHERE절에서 썼던 조건문 형식을 그대로 쓴다

DROP TABLE EMP2;
CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	CONSTRAINT EMP2_JOB_CK CHECK (JOB IN ('CLERK', 'ANALYST',
						'MANAGER', 'SALESMAN', 'PRESIDENT'))
,        MGR		NUMBER(4)	CONSTRAINT EMP2_MGR_FK REFERENCES EMP2 (EMPNO)
,        SAL		NUMBER(7, 2)	CONSTRAINT EMP2_SAL_CK CHECK (SAL BETWEEN 700 AND 9999)
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)	CONSTRAINT EMP2_DEPTNO_FK REFERENCES DEPT2 (DEPTNO)
);

JOB, SAL에 CHECK 조건을 주었다

INSERT INTO EMP2
VALUES (1111, 'KIMHUGO', 'MANAGER', NULL, 5000, NULL, SYSDATE, 20);

INSERT INTO EMP2
VALUES (2222, 'KT', '개발자', NULL, 4000, NULL, SYSDATE, 20);

INSERT INTO EMP2
VALUES (3333, 'RURU', 'CLERK', NULL, 650, NULL, SYSDATE, 20);

케이티와 루루는 각각 제약조건을 만족하지 못 해 채용 실패했다.

 

14.7. COLUMN Level Constraint VS TABLE Level Constraint

1) COLUMN Level Constraint : 테이블을 만들면서 제약조건을 함께 설정

DROP TABLE EMP2;
DROP TABLE DEPT2;

CREATE TABLE DEPT2 (
	DEPTNO		NUMBER(2)		CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY
,	DNAME		VARCHAR2(10)		CONSTRAINT DEPT2_DNAME_UQ	UNIQUE
,	LOC		VARCHAR2(10)		CONSTRAINT DEPT2_LOC_NN NOT NULL
);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT2';

2) TABLE Level Constraint : 테이블을 만들어 두고, 나중에 제약조건을 추가

단, NOT NULL 제약 조건은 나중에 추가할 수 없다.

DROP TABLE DEPT2;

CREATE TABLE DEPT2 (
	DEPTNO		NUMBER(2)
,	DNAME		VARCHAR2(10)
,	LOC		VARCHAR2(10)		CONSTRAINT DEPT2_LOC_NN NOT NULL
,	CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY (DEPTNO)
,	CONSTRAINT DEPT2_DNAME_UQ	UNIQUE (DNAME)
);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'DEPT2';

왜 두가지 방식이 존재하나?

두 개의 컬럼에 대해서 하나의 복합 제약 조건(복합 키)이 존재할 때,

컬럼 레벨에서는 제대로 제약을 할 수 없다.

 

예) 주민등록번호 : 앞자리와 뒷자리의 조합이 유일해야 한다.

CREATE TABLE MEMBER (
	JUMIN1	NUMBER(6)
,	JUMIN2	NUMBER(7)
,	CONSTRAINT MEMBER_JUMIN12_UQ UNIQUE (JUMIN1, JUMIN2)
);

 

DROP TABLE DEPT2;
DROP TABLE EMP2;

CREATE TABLE DEPT2 (
	DEPTNO		NUMBER(2)
,	DNAME		VARCHAR2(10)
,	LOC		VARCHAR2(10)		CONSTRAINT DEPT2_LOC_NN NOT NULL
,	CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY (DEPTNO)
,	CONSTRAINT DEPT2_DNAME_UQ	UNIQUE (DNAME)
);

CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)	
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE
,        DEPTNO		NUMBER(2)	
,	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY (EMPNO)
,	CONSTRAINT EMP2_JOB_CK CHECK (JOB IN ('CLERK', 'ANALYST',
						'MANAGER', 'SALESMAN', 'PRESIDENT'))
,	CONSTRAINT EMP2_MGR_FK FOREIGN KEY (MGR) REFERENCES EMP2 (EMPNO)
,	CONSTRAINT EMP2_SAL_CK CHECK (SAL BETWEEN 700 AND 9999)
,	CONSTRAINT EMP2_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT2 (DEPTNO)
);

테이블 레벨에서 FOREIGN 키를 설정하는 방법에 유의

 

14.8. DEFAULT : 기본값을 설정하는 옵션

DROP TABLE EMP2;

CREATE TABLE EMP2 (
	EMPNO		NUMBER(4)	
,        ENAME		VARCHAR2(10)	CONSTRAINT EMP2_ENAME_NN NOT NULL
,        JOB		VARCHAR2(9)	
,        MGR		NUMBER(4)	
,        SAL		NUMBER(7, 2)	
,        COMM		NUMBER(7, 2)
,        HIREDATE	DATE		DEFAULT SYSDATE
,        DEPTNO		NUMBER(2)	
,	CONSTRAINT EMP2_EMPNO_PK PRIMARY KEY (EMPNO)
,	CONSTRAINT EMP2_JOB_CK CHECK (JOB IN ('CLERK', 'ANALYST',
						'MANAGER', 'SALESMAN', 'PRESIDENT'))
,	CONSTRAINT EMP2_MGR_FK FOREIGN KEY (MGR) REFERENCES EMP2 (EMPNO)
,	CONSTRAINT EMP2_SAL_CK CHECK (SAL BETWEEN 700 AND 9999)
,	CONSTRAINT EMP2_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT2 (DEPTNO)
);

INSERT INTO DEPT2
VALUES (10, '개발부', '서울');

INSERT INTO EMP2 (EMPNO, ENAME, JOB, SAL, DEPTNO)
VALUES (1111, 'KIMHUGO', 'CLERK', 2000, 10);

SELECT * FROM EMP2;

HIREDATE에 현재 시스템 날짜가 자동으로 입력되었다.

14.9 기존 테이블에 제약조건 추가 및 삭제

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

기본 샘플 테이블이 가진 제약조건

 

ALTER TABLE EMP
DROP CONSTRAINT SYS_C008315;

DESC EMP

NULL조건이 없네?

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

여기도 없다

다음의 제약조건을 추가해보자.

• DEPT DEPTNO - PRIMARY KEY
• DEPT DNAME - UNIQUE
• DEPT LOC - NOT NULL
• EMP EMPNO - PRIMARY KEY
• EMP ENAME - NOT NULL
• EMP JOB - CHECK (CLERK, ANALYST, MANAGER, SALESMAN, PRESIDENT)
• EMP MGR - FOREIGN KEY (DEPT.DEPTNO)
• EMP SAL - CHECK (700 ~ 9999)
• EMP HIREDATE - DEFAULT (SYSDATE)
• EMP DEPTNO FOREIGN KEY DEPT (DEPTNO)

ALTER TABLE DEPT
ADD CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO)