[SQL] Oracle, 제약 조건

제약조건

: 테이블의 특정 열에 지정하는 것, 무결성을 보장하기 위해 제공된다.

주로 테이블 생성 시 지정

 

※ 무결성

: 영역 무결성, 개체 무결성, 참조 무결성

---

1. NOT NULL

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

CREATE TABLE TABLE_NOTNULL2(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
    LOGIN_PWD   VARCHAR2(20) CONSTRAINT TBLNN2_LGNPW_NN NOT NULL,
    TEL         VARCHAR2(20)
);

SELECT * FROM USER_CONSTRAINTS;

- NOT NULL 로 수정

--제약 조건 수정1
ALTER TABLE TABLE_NOTNULL
    MODIFY(TEL NOT NULL);

--제약 조건 수정2
ALTER TABLE TABLE_NOTNULL2
    MODIFY(TEL CONSTRAINT TBLNN_TEL_NN NOT NULL);
    
--제약 조건 이름 변경    
ALTER TABLE TABLE_NOTNULL2
    RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;

- NOT NULL 삭제

ALTER TABLE TABLE_NOTNULL2
	DROP CONSTRAINT TBLNN2_TEL_NN;

---

2. UNIQUE

: 데이터의 중복을 허용하지 않고자 할 때

 

CREATE TABLE TABLE_UNIQUE(
    LOGIN_ID    VARCHAR2(20) UNIQUE,
    LOGIN_PWD   VARCHAR2(20) NOT NULL,
    TEL         VARCHAR2(20)
);

CREATE TABLE TABLE_UNIQUE2(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLUNQ2_LGNID_UNQ UNIQUE,
    LOGIN_PWD   VARCHAR2(20) CONSTRAINT TBLUNQ2_LGNPW_NN NOT NULL,
    TEL         VARCHAR2(20)
);

- UNIQUE로 수정

--제약 조건 수정1
ALTER TABLE TABLE_UNIQUE
    MODIFY(TEL UNIQUE);

--제약 조건 수정2
ALTER TABLE TABLE_UNIQUE2
    MODIFY(TEL CONSTRAINT TBLUNQ_TEL_UNQ UNIQUE);
    
--제약 조건 이름 변경    
ALTER TABLE TABLE_UNIQUE2
    RENAME CONSTRAINT TBLUNQ_TEL_UNQ TO TBLUNQ2_TEL_UNQ;

- UNIQUE 삭제

ALTER TABLE TABLE_UNIQUE2
	DROP CONSTRAINT TBLUNQ2_TEL_UNQ;

---

3. PRIMARY KEY

: UNIQUE + NOT NULL

• 행을 식별하는 데 사용
• 테이블 당 하나만 지정
• 인덱스 자동 생성

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

CREATE TABLE TABLE_PK2(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLPK2_LGNID_PK UNIQUE,
    LOGIN_PWD   VARCHAR2(20) CONSTRAINT TBLPK2_LGNPW_NN NOT NULL,
    TEL         VARCHAR2(20)
);

SELECT * FROM USER_INDEXES WHERE TABLE_NAME LIKE 'TABLE_PK%';

 

※ 열레벨 제약조건 정의 VS 테이블레벨 제약조건 정의

CREATE TABLE TABLE_PK2(
    LOGIN_ID    VARCHAR2(20) CONSTRAINT TBLPK2_LGNID_PK PRIMARY KEY,
    LOGIN_PWD   VARCHAR2(20) NOT NULL,
    TEL         VARCHAR2(20)
);

CREATE TABLE TABLE_PK2(
    LOGIN_ID    VARCHAR2(20),
    LOGIN_PWD   VARCHAR2(20),
    TEL         VARCHAR2(20),
    CONSTRAINT TBLPK2_LGNID_PK PRIMARY KEY (LOGIN_ID),
    NOT NULL (LOGIN_PWD)
);

---

4. FOREIGN KEY

: 서로 다른 테이블 간 관계를 정의하는 데 사용

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

- FOREIGN KEY 지정

CREATE TABLE DEPT_FK(
    DEPTNO  NUMBER(2) CONSTRAINT DEPTFK_DEPTNO_PK PRIMARY KEY,
    DNAME   VARCHAR2(14),
    LOC     VARCHAR2(13)
);

CREATE TABLE EMP_FK(
    EMPNO   NUMBER(4) CONSTRAINT EMPFK_EMPNO_PK PRIMARY KEY,
    ENAME VARCHAR2(10),
    DEPTNO NUMBER(2) CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO)
);

※ EMP_FK에 데이터 삽입 시, DEPT_FK에 상응하는 데이터가 있어야 EMP_FK에 데이터가 삽입된다.

 

- 열 데이터(DEPT_FK의 DEPTNO) 삭제시 주의

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

CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO) ON DELETE CASCADE 

2. 참조하고 있는 데이터를 NULL로 수정

CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK(DEPTNO) ON DELETE SET NULL

---

5. CHECK

...
LOGIN_PWD VARCHAR2(20) CONSTRAINT TBLCK_LOGINPK_CK CHECK (LENGTH(LOGIN_PWD) > 3),
...

---

6. DEFAULT

...
LOGIN_PWD VARCHAR2(20) DEFAULT '1234',
...

 

- 종합예제

CREATE TABLE DEPT_CONST(
    DEPTNO  NUMBER(2) CONSTRAINT DEPTCONST_DEPTNO_PK PRIMARY KEY,
    DNAME   VARCHAR2(14) CONSTRAINT DEPTCONST_DNAME_UNQ UNIQUE,
    LOC     VARCHAR2(13) CONSTRAINT DEPTCONST_LOC_NN NOT NULL
);

CREATE TABLE EMP_CONST(
    EMPNO   NUMBER(4) CONSTRAINT EMPCONST_EMPNO_PK PRIMARY KEY,
    ENAME   VARCHAR2(10) CONSTRAINT EMPCONST_ENAME_NN NOT NULL,
    JOB     VARCHAR2(9),
    TEL     VARCHAR2(20) CONSTRAINT EMPCONST_TEL_UNQ UNIQUE,
    HIREDATE    DATE,
    SAL     NUMBER(7,2) CONSTRAINT EMPCONST_SAL_CHK CHECK (SAL BETWEEN 1000 AND 9999),
    COMM    NUMBER(7,2),
    DEPTNO  NUMBER(2) CONSTRAINT EMPCONST_DEPTNO_FK REFERENCES DEPT_CONST(DEPTNO)
);

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE 
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME IN ( 'EMP_CONST', 'DEPT_CONST' ) 
ORDER BY CONSTRAINT_NAME; 

※ 참고

• P: PRIMARY KEY
• R: FOREIGN KEY
• U: UNIQUE
• C: NOT NULL 또는 CHECK