[ Oracle Database ] 제약 조건

[ 제약조건(CONSTRAINT) ]

1. 제약조건이란?

 1) 데이터 무결성 : 데이터베이스에 저장되어 있는 데이터가 손상되거나 원래의 의미를 잃지 않고 유지하는 상태.

 2) 데이터 무결성 제약 조건 : 데이터의 무결성을 보장하기 위해 오라클에서 지원하는 방법.

  - eg) 유효하지 않은 데이터 입력 방지, 유효한 범위에서만 데이터 변경/삭제 작업 허용.

 

2. 제약조건 종류

 1) NOT NULL : 해당 컬럼에 NULL을 포함하지 않도록 한다.(컬럼 라벨)

 2) UNIQUE : 해당 컬럼 또는 컬럼 조합 값이 유일하도록 한다.(컬럼, 테이블 라벨)

 3) PRIMARY KEY : 각 행(열X)을 유일하게 식별할 수 있도록 한다.(컬럼, 테이블 라벨)

  - [ PRIMARY KEY = NOT NULL + UNIQUE ]

 4) REFERENCE table ( column_name ) : 해당 컬럼이 참조하고 있는 테이블(주테이블 = 부모테이블)의 특정 컬럼 값들과 일치하거나 NULL이 되도록 보장한다.(컬럼, 테이블 라벨)

 5) CHECK : 해당 컬럼에 특정 조건을 항상 만족시키도록 한다.(컬럼, 테이블 라벨)

 

3. 제약조건의 특징

 1) 이름으로 관리된다 : 문자로 시작, 길이는 30자, 이름일 지정하지 않으면 자동 생성

  eg) 이름 지정하지 않았을 때, SYS_C00000형식으로 자동 생성

 2) 생성 시기 : 테이블 생성과 동시, 테이블을 생성한 후(추가 가능)

 3) 컬럼 레벨 또는 테이블 레벨에서의 정의(단, NOT NULL은 컬럼 레벨에서만 사용 가능하다.)

 4) 컬럼 여러 개를 조합하는 경우에는 '테이블 레벨'에서만 가능하다.

 

4. 제약조건 예시

Q1) NOT NULL을 확인해보자.

CREATE TABLE TABLE_NOTNULL

(ID CHAR(3) NOT NULL,

SNAME VARCHAR2(20));

 

INSERT INTO TABLE_NOTNULL VALUES('100', 'ORACLE');

INSERT INTO TABLE_NOTNULL VALUES(NULL, 'ORACLE');

 

Q2) NOT NULL을 확인해보자.
CREATE TABLE TABLE_NOTNULL
(ID CHAR(3) NOT NULL,
SNAME VARCHAR2(20));

INSERT INTO TABLE_NOTNULL VALUES('100', 'ORACLE');
INSERT INTO TABLE_NOTNULL VALUES(NULL, 'ORACLE'); -- 계정 테이블 컬럼

CREATE TABLE TABLE_NOTNULL
(ID CHAR(3) NOT NULL,
SNAME VARCHAR2(20),
CONSTRAINT TN_IN_NN NOT NULL(ID)--테이블 이름을 약어로 주고 컬럼의 이름을 주고 제약조건의 단축
);--ORA-00904: : invalid identifier -> NOT NULL은 컬럼 레벨만 존재

 

Q3) UNIQUE(식별값 : 중복 데이터 허용 불가, NULL)을 확인해보자.(단일 컬럼)

CREATE TABLE TABLE_UNIQUE

(ID CHAR(3) UNIQUE,

SNAME VARCHAR2(20));

 

INSERT INTO TABLE_UNIQUE VALUES('100', 'ORACLE');

INSERT INTO TABLE_UNIQUE VALUES('100', 'ORACLE');

-> ORA-00001: unique constraint (TEST.SYS_C007079) violated : 'ID' 컬럼에 중복 값을 입력하려고 했기 때문에 발생

 

Q4) 제약 조건 테이블을 확인하자
DESC USER_CONSTRAINTS

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

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'TABLE_NOTNULL'

 

Q5) EMP테이블의 제약조건을 확인해보자.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME ='EMP';

 

Q6) UNIQUE(식별값 : 중복 데이터 허용 불가, NULL)을 확인해보자.(조합 컬럼 = 테이블 레벨)
CREATE TABLE TABLE_UNIQUE2
(ID CHAR(3),
SNAME VARCHAR2(20),
SCODE CHAR(2),
CONSTRAINT TN2_ID_UN UNIQUE(ID, SNAME));

INSERT INTO TABLE_UNIQUE2 VALUES('100', 'ORACLE', '01');
INSERT INTO TABLE_UNIQUE2 VALUES('200', 'ORACLE', '01');
INSERT INTO TABLE_UNIQUE2 VALUES('200', 'ORACLE', '02');

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

 

Q7) UNIQUE(식별값 : 중복 데이터 허용 불가, NULL)을 확인해보자.(단일 컬럼) : NULL은 무한정 허용한다.
CREATE TABLE TABLE_UNIQUE3
(ID CHAR(3) UNIQUE,
SNAME VARCHAR2(20) UNIQUE,
SCODE CHAR(2));

INSERT INTO TABLE_UNIQUE3 VALUES('100', 'ORACLE', '01');
INSERT INTO TABLE_UNIQUE3 VALUES('200', 'ORACLE', '01');
-- 'ID' 컬럼과 'SNAME' 컬럼에 각각 제약조건이 설덩되었기 때문에, 중복된 'SNMAE' 컬럼값이 입력될 수 없음
-- 만약에 두 컬럼의 조합결과를 유일하게 하려면 '테이블 레벨'에서 생성되어야 한다.

 

Q8) UNIQUE(식별값 : 중복 데이터 허용 불가, NULL)을 확인해보자.(단일 컬럼) : NULL은 무한정 허용한다.
CREATE TABLE TABLE_UNIQUE4
(ID CHAR(3) CONSTRAINT TN4_ID_UN UNIQUE,
SNAME VARCHAR2(20) CONSTRAINT TN5_ID_UN UNIQUE,
SCODE CHAR(2));

 

제약조건의 이름이 같다고 묶이지 않는다는 것을 보여주는 예시

Q9) PRIMARY KEY = UNIQUE + NOT NULL, 테이블 당 1개만 생성

CREATE TABLE TABLE_PK
(ID CHAR(3) PRIMARY KEY,
SNAME VARCHAR2(20));

 

INSERT INTO TABLE_PK VALUES('100', 'ORACLE');

INSERT INTO TABLE_PK VALUES('100', 'ORACLE');

INSERT INTO TABLE_PK VALUES(NULL, 'ORACLE');

 

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

 

Q10) PRIMARY KEY = UNIQUE + NOT NULL, 테이블 당 한 개만 생성 가능
CREATE TABLE TABLE_PK2
(ID CHAR(3),
SNAME VARCHAR2(20)

SCODE CHAR(2),

CONSTRAINT TP2_PK PRIMARY KEY(ID, SNAME));

INSERT INTO TABLE_PK2 VALUES('100', 'ORACLE', '02');

INSERT INTO TABLE_PK2 VALUES('100', 'ORACLE', '02');

-- 중복 데이터 때문에 오류

INSERT INTO TABLE_PK2 VALUES(NULL, 'ORACLE', '02');
-- ->조합되는 개별 컬럼에 NULL은 허용되지 않는다.

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

 

Q11) PRIMARY KEY = UNIQUE + NOT NULL, 테이블 당 한 개만 생성 가능 -> 중복 불가능
CREATE TABLE TABLE_PK3
(ID CHAR(3) PRIMARY KEY,
SNAME VARCHAR2(20) PRIMARY KEY,
SCODE CHAR(2));

 

Q12) EMPLOYEE 테이블에서 사원번호, 이름, 부서 아이디를 출력해보자
SELECT EMP_ID, EMP_NAME, DEPT_ID
FROM EMPLOYEE;

-- DEPARTMENT 테이블에서 부서 아이디, 부서명을 출력해보자.
SELECT DEPT_ID, DEPT_NAME
FROM DEPARTMENT;

 

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN( 'EMPLOYEE', 'DEPARTMENT');

-- 두개의 테이블의 참조형을 가진 상태

-- DEPT_ID -> FOREIGN KEY 컬럼

-- DEPARTEMENT 테이블의 DEPT_ID 컬럼에 존재하지 않은 값이 EMPLOYEE에 포함되면 데이터 무결성에 문제가 발생했다라고 판단 후 오류 발생

 

Q13) FOREIGN KEY : 참조 테이블(주테이블)의 컬럼값과 일치하거나 NULL 상태를 유지하도록 하는 제약 조건

TABLE_FK(종 테이블) 테이블을 생성하면서 LOCATION 테이블(주 테이블)을 참조하려고 한다.

 1) 주테이블의 구조를 확인한다

 2) 주테이블의 제약 조건을 확인한다.

 3) 주 테이블의 참조 컬럼의 제약 조건이 반드시 PK이어야 한다.

 

DESC LOCATION;

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

-- 컬럼 레벨
CREATE TABLE TABLE_FK(
ID CHAR(3),
SNAME VARCHAR2(2),
LID CHAR(2) REFERENCES LOCATION(LOCATION_ID)); -- LOCATION = 참조 테이블, LOCATION_ID = 참조 컬럼

---> 참조 테이블만 기술하고 참조 컬럼을 생략하면 해당 참조 테이블의 PRIMARY KEY 컬럼을 참조하게 된다.

 

CREATE TABLE TABLE_FK(
ID CHAR(3),
SNAME VARCHAR2(2),
LID CHAR(2) REFERENCES LOCATION); -- LOCATION = 참조 테이블

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

 

Q14) FOREIGN KEY를 이용해서 데이터를 입력해보자.

INSERT INTO TABLE_FK VALUES('200', 'ORACLE', 'C1');

---> 참조 테이블 LOCATION = 'C1' 값이 없음.

 

INSERT INTO TABLE_FK VALUES('200', 'ORACLE', 'A1');

INSERT INTO TABLE_FK VALUES('200', 'ORACLE', 'A2');

INSERT INTO TABLE_FK VALUES('200', 'ORACLE', NULL);

 

SELECT * FROM TABLE_FK;

 

Q15) FOREIGN KEY를 이용해서 테이블 레벨에서 생성해보자.

--테이블 생성시는 'FOREIGN KEY'가 추가된다.

CREATE TABLE TABLE_FK2(
ID CHAR(3),
SNAME VARCHAR2(20),
LID CHAR(2),

FOREIGN KEY (LID) REFERENCES LOCATION(LOCATION_ID));

-- FROREIGN KEY = 추가 키워드, LID = 설정 컬럼, LOCATION은 참조테이블, LOCATION_ID = 참조 컬럼

 

-- 이름을 넣어주자.

CREATE TABLE TABLE_FK3(
ID CHAR(3),
SNAME VARCHAR2(20),
LID CHAR(2),

CONSTRAINT TFK3_MYKEY FOREIGN KEY (LID) REFERENCES LOCATION(LOCATION_ID));

 

# 주의 : 부모 테이블의 컬럼에 PK를 참조하는 것이 원칙이나 만일에 참조하는 컬럼을 명시할 때는 PRIMARY KEY/UNIQUE 제약조건이 설정된 컬럼만 참조가 가능하다.

cf) FOREIGN KEY는 같은 값 또는 NULL값을 가질 수 있다.

 

Q16) 참조 키를 확인해보자.

CREATE TABLE TABLE_NOPK(

ID CHAR(3),

SNAME VARCHAR2(20));

 

CREATE TABLE TABLE_FK4(
ID CHAR(3) REFERENCES TABLE_NOPK); ---> 부모의 PK를 찾는다.

CREATE TABLE TABLE_FK4(
ID CHAR(3) REFERENCES TABLE_NOPK(ID));---> 부모의 PK를 찾고 컬럼 명을 명시하게 되면, PK, U를 찾는다.

 

Q17) 참조 키를 생성할 때 주의할 점. - FOREIGN KEY : DELETE OPTION

-- FOREIGN KEY 제약 조건을 생성할 때, 참조 컬럼 값이 삭제 되는 경우에 FOREIGN KEY 컬럼 값을 어떻게 처리할 지를 지장하는 옵션

 1) 구문

  - [ CONSTRAINT constraint_name ] constraint_type ON DELETE SET NULL

    : 참조 컬럼 값이 삭제될 때, FOREIN KEY 컬럼 값을 NULL로 변경

  - [ CONSTRAINT constraint_name ] constraint_type ON DELETE SET CASCADE

    : 참조 컬럼 값이 삭제될 때, FOREIN KEY 컬럼 값도 함께 삭제

 

 

Q18) 참조 키 조합 컬럼

CREATE TABLE TABLE_PK5
(ID CHAR(3),
SNAME VARCHAR2(20),
SCODE CHAR(2),
CONSTRAINT TF5_FK FOREIGN KEY (ID, SNAME) REFERENCES TABLE_PK2);

INSERT INTO TABLE_PK5 VALUES('100', 'ORACLE', '03');
INSERT INTO TABLE_PK5 VALUES(NULL, 'ORACLE', '03');
INSERT INTO TABLE_PK5 VALUES(NULL, NULL, '03');
INSERT INTO TABLE_PK5 VALUES('100', NULL, '03');

SELECT * FROM TABLE_PK5;

 

Q19) CHECK 제약조건

CREATE TABLE TABLE_CHECK(

EMP_ID CHAR(3) PRIMARY KEY,

SALARY NUMBVER CHECK(SALARY > 0),

MARRIAGE CHAR(1),

CONSTRAINT CHK_MGR CHECK(MARRIAGE IN ('Y,', 'N')));

 

Q20) 서브쿼리를 이용해서 생성할 수 있다.
CREATE TABLE TABLE_SUBQUERY1
AS
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING(JOB_ID);

DESC TABLE_SUBQUERY1;

SELECT * FROM TABLE_SUBQUERY1;

Q21) 서브쿼리를 이용해서 생성할 수 있다.(컬럼명 변경 가능)
CREATE TABLE TABLE_SUBQUERY2(EID, ENAME, SAL, DNAME, JTITLE)
AS
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING(JOB_ID);

SELECT * FROM TABLE_SUBQUERY2;

Q22) 서브쿼리를 이용해서 생성할 수 있다.(테이블 생성시 제약조건을 생성할 수 있다.)
CREATE TABLE TABLE_SUBQUERY3(EID, ENAME, SAL CHECK (SAL > 20000000),DNAME, JTITLE DEFAULT 'N/A' NOT NULL)
AS
SELECT EMP_ID, EMP_NAME, SALARY, DEPT_NAME, JOB_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING(JOB_ID)
WHERE SALARY > 20000000;

 

Q23)
SELECT CONSTRAINT_TYPE, SEARCH_CONDITION, R_CONSTRAINT_NAME, DELETE_RULE
FROM USER_CONSTRAINTS;

 

 # CONSTRAINT_TYPE : P (PRIMARY KEY), U (UNIQUE), R(REFERENCES), C(CHECK, NOT NULL)

 # DELETE_RULE : 참조 테이블의 PK가 삭제될 때 적용되는 규칙
   - "No Action", "SET NULL", "CASCADE"

 

Q24) DESC USER_CONS_COLUMNS 제약 조건 확인
SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
SEARCH_CONDITION AS 내용,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING(CONSTRAINT_NAME, TABLE_NAME);