오라클 기초#8(제약조건:constraint)

[ 제약조건 ]

- 테이블의 해당 컬럼에 원치않는 데이터를 입력/변경/삭제되는 것을 방지하기위해 설정하는 조건

 

1) 종류

(1) PRIMARY KEY(식별키)
  - 하나의 테이블에 오직 하나만 존재되며 하나의 ROW를 대표하는 컬럼

(2) FOREIGN KEY(참조키/외래키)
  - 부모테이블의 PK를 참조하는 컬럼

(3) UNIQUE KEY(유일키)
  - 컬럼의 모든 값이 유일해야 하는 경우.즉 중복데이터를 허용하지 않는컬럼.

  - PK와의 차이점
  a. PK는 NULL을 허용하지 않지만 유일키는 NULL허용함.
  b. PK는 하나의 테이블에 하나만 존재하지만 유일키는 여러개존재가능.

(4) CHECK
- 조건에 맞는 데이터만 입력되도록 조건을 부여한 제약조건

 

(5) NOT NULL
- NULL이 입력되어서는 안되는 컬럼에 부여하는 제약조건
- COLUMN LEVEL로만 제약조건 부여 가능.

 

 

2) 제약조건 예

 

(1) 제약조건에 이름을 부여하지 않은 경우

 

SQL> CREATE TABLE MYDEPT
2  (
3     DEPTNO NUMBER(2) PRIMARY KEY,  -- PK로 설정
4     DNAME VARCHAR2(15) DEFAULT '영업부', -- 값이 안들어오면 자동으로 '영업부'로 채워짐
5     LOC CHAR(9) CHECK(LOC IN('서울','부산','대구'))-- LOC컬럼은 서울,부산,대구만 저장될수있음
6  );

 

SQL> CREATE TABLE MYEMP
2  (
3     EMPNO NUMBER(4) PRIMARY KEY, -- PK로 설정
4     ENAME VARCHAR2(15) NOT NULL, -- NULL허용안함
5     JUMIN CHAR(13) UNIQUE, -- 중복값 허용안함
6     DEPTNO NUMBER(2) REFERENCES MYDEPT(DEPTNO) -- 외래키설정
7  );

 

 

-- 제약조건 보기

 

SQL> SELECT * FROM USER_CONSTRAINTS;--제약조건 전체 정보보기

 

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS;--제약조건이름보기

 

CONSTRAINT_NAME
----------------------
PK_DEPT
PK_EMP
FK_DEPTNO
SYS_C007010
SYS_C007013
SYS_C007014
...

 

 

SQL> DESC MYDEPT;

Name Null? Type
------------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(15)
LOC CHAR(9)

 

 

SQL> INSERT INTO MYDEPT(DEPTNO,LOC)
2 VALUES(70,'제주');

INSERT INTO MYDEPT(DEPTNO,LOC)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C007015) violated
==> CHECK제약조건이 맞지 않음

 

SQL> INSERT INTO MYDEPT(DEPTNO,LOC)
2 VALUES(70,'서울');

 

1 row created.

 

 

SQL> SELECT * FROM MYDEPT;

 

DEPTNO DNAME LOC
------ ------- ---------
70 영업부 서울

 

 

SQL> INSERT INTO MYEMP
2 VALUES(1,'홍길동','1111111111111',71);

INSERT INTO MYEMP
*

ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C007020) violated - parent key not
found
==> 부모테이블에 71부서가 존재하지 않으므로 에러!!!

 

 

SQL> INSERT INTO MYEMP
2 VALUES(1,'홍길동','1111111111111',70);

 

-- 테이블 삭제 : 자식테이블먼저 삭제하고 부모테이블 삭제하기

SQL> DROP TABLE MYEMP;

 

SQL> DROP TABLE MYDEPT;

 

 

 

  

(2) 제약조건에 이름을 부여한 경우

- 컬럼레벨 제약조건

CREATE TABLE MYDEPT
(
DEPTNO NUMBER(2) CONSTRAINT PK_MYDEPT_DEPTNO PRIMARY KEY,
DNAME VARCHAR2(15) DEFAULT '영업부',
LOC VARCHAR2(15) CONSTRAINT CK_MYDEPT_LOC CHECK(LOC IN('서울','부산'))
);

 

- 테이블 레벨 제약조건 : 하나의 컬럼에 두개 이상의 제약조건을 주고자 할때 또는 두개의 컬럼이 합쳐서 하나의 PK를 만들고자 할때

예)

CREATE TABLE MYEMP
(
 EMPNO NUMBER(4),
 ENAME VARCHAR2(15),
 ...
 CONSTRAINT PK_MYEMP_EMPNO PRIMARY KEY(EMPNO,ENAME),
 CONSTRAINT UQ_MYEMP_JUMIN UNIQUE(JUMIN),
 ...
);

...

 

SQL> CREATE TABLE MYEMP
2  (
3     EMPNO NUMBER(4),
4     ENAME VARCHAR2(15) CONSTRAINT NN_MYEMP_ENAME NOT NULL,--NOT NULL조건은 컬럼레벨만 가능!
5     JUMIN CHAR(13),
6     DEPTNO NUMBER(2),
7     CONSTRAINT PK_MYEMP_EMPNO PRIMARY KEY(EMPNO),
8     CONSTRAINT UQ_MYEMP_JUMIN UNIQUE(JUMIN),
9     CONSTRAINT FK_MYEMP_DEPTNO FOREIGN KEY(DEPTNO)
                            REFERENCES MYDEPT(DEPTNO)
10  );

 

 

## ON DELETE CASCADE 옵션

 

- 부모테이블의 데이터가 삭제되면 참조하는 자식테이블의 데이터도 자동으로 같이 삭제됨

 

예)

CREATE TABLE MYEMP
(
EMPNO NUMBER(4),
   ..
DEPTNO NUMBER(2),
   ..
CONSTRAINT FK_MYEMP_DEPTNO FOREIGN KEY(DEPTNO)
          REFERENCES MYDEPT(DEPTNO) ON DELETE CASCADE
);

 

 

-- 제약조건 삭제하기
SQL> ALTER TABLE MYDEPT DROP CONSTRAINT PK_MYDEPT_DEPTNO;

-- 제약조건 추가하기
SQL> ALTER TABLE MYDEPT ADD CONSTRAINT PK_MYDEPT PRIMARY KEY(DEPTNO);