데이터 무결성 제약 조건 : 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서
테이블을 생성할 때 각 컬럼에 규칙을 걸어두는 것.
| 무결성 제약 조건 | 역할 |
| NOT NULL | NULL을 허용하지 않는다. |
| UNIQUE | 중복된 값을 허용하지 않는다. 항상 유일한 값을 갖도록 한다. |
| PRIMARY KEY | NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL 조건과 UNIQUE 조건을 결합한 형태이다. |
| FOREIGN KEY | 참조되는 테이블의 칼럼의 값이 존재하면 허용한다. |
| CHECK | 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용한다. |
데이터 딕셔너리 뷰
데이터 딕셔너리는 조회가 불가능하므로, 데이터 딕셔너리 뷰를 제공한다.
(제약 조건 정보 확인 가능)
USER _XXXX : 내가 만든 자료만 조회
ALL_XXXX : 내가 만든 거+ 권한을 부여받은 거 조회
DBA_XXXX : DB에 있는 모든 정보를 조회 (DBA 사용자 권한을 가져야 함)
USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건을 볼 수 있다.
DESC USER_CONSTRAINTS;
=>
이름 널? 유형
----------------- -------- -------------
OWNER(사용자명) VARCHAR2(120)
CONSTRAINT_NAME(조건명) NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE(조건유형) VARCHAR2(1)
TABLE_NAME(테이블명) NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(120)
......생략......<제약조건명 확인하기>
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP04');USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있다.
SELECT *
FROM USER_CONS_COLUMNS;
SELECT *
FROM USER_CONS_COLUMNS;
| CONSTRAINT_TYPE | 의미 |
| P | PRIMARY KEY |
| R | FOREIGN KEY |
| U | UNIQUE |
| C | CHECK, NOT NULL |
제약 조건 설정
제약 조건 설정하기(칼럼 레벨 방식)
칼럼명 데이터타입 제약조건
제약 조건명 달기
칼럼명 데이터타입 CONSTRAINT 제약조건명 제약조건
이때 제약조건명은 [테이블명_칼럼명_제약조건] 으로 작성한다.
NOT NULL : NULL값이 저장되지 못하도록 설정.(자료가 꼭 입력되도록 하고싶을 때)
칼럼명 데이터타입 제약조건
CREATE TABLE EMP02(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
UNIQUE : 특정 칼럼에 대해 자료가 중복되지 않게 설정.(지정된 칼럼에는 유일한 값이 수록되게 하는 것)
NULL은 값이 아니므로 중복 저장이 가능하다.
CREATE TABLE EMP03(
EMPNO NUMBER(4) UNIQUE,
ENAME VARCHAR2(10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
PRIMARY KEY(기본키) : 특정 칼럼이 유일하면서도 NULL 값을 허용하지 않도록 설정.
(UNIQUE과 NOT NULL을 모두 갖는 것)
CREATE TABLE EMP05(
EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY ,
ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);
FOREIGN KEY(외래키) : 한 테이블(기준 테이블)을 다른 테이블(참조 테이블)과 연결해줌.
먼저 정의되어야 하는 테이블이 부모 테이블, 나중에 정의되어야 하는 테이블이 자식 테이블이 된다.
칼럼명 데이터타입 CONSTRAINT 제약조건명 REFERENCES 테이블명(칼럼명)
ex)사원 테이블에 부서번호를 입력할 때 부서 테이블에 존재하는 부서번호만 입력하도록 설정.
사원 테이블= 자식 테이블 부서 테이블= 부모 테이블
부모 테이블(부서 테이블)의 칼럼(부서 번호)은 반드시 그 테이블의 기본키나 유일키로 설정되어있어야 함.
CREATE TABLE EMP06(
EMPNO NUMBER(4) CONSTRAINT EMP06_EMPNO_PK PRIMARY KEY ,
ENAME VARCHAR2(10) CONSTRAINT EMP06_ENAME_NN NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2) CONSTRAINT EMP06_DEPTNO_FK REFERENCES DEPT(DEPTNO)
);R_CONSTRAINT_NAME : 참조한 PRIMARY KEY 정보를 알려주는 컬럼
SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP06');
CHECK : 조건으로 설정된 값만 입력할 수 있도록 함.
데이터의 값의 범위나 특정 패턴의 숫자나 문자 값을 설정할 수 있다.
CREATE TABLE EMP07(
EMPNO NUMBER(4) CONSTRAINT EMP07_EMPNO_PK PRIMARY KEY ,
ENAME VARCHAR2(10) CONSTRAINT EMP07_ENAME_NN NOT NULL,
SAL NUMBER(7, 2) CONSTRAINT EMP07_SAL_CK CHECK (SAL BETWEEN 500 AND 5000),
GENDER VARCHAR2(1) CONSTRAINT EMP07_GENDER_CK CHECK (GENDER IN('M', 'F'))
);
DEFAULT : 아무런 값을 입력하지 않았을 때 입력될 값을 설정.
CREATE TABLE DEPT01(
DEPTNO NUMBER(2) PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) DEFAULT 'SEOUL'
);
테이블 레벨 방식으로 제약조건 지정하기
복합키(2개 이상의 컬럼이 하나의 기본키를 구성하는 경우)의 형태로 제약조건을 지정할 때 사용.
*NOT NULL은 칼럼 레벨 방식으로만 조건을 달아줄 수 있다.
CREATE TABLE 테이블명 (
칼럼1 테이터타입1,
칼럼2 테이터타입2, . . .
[CONSTRAINT 제약조건명] 제약조건 (칼럼명)
);
CREATE TABLE EMP03(
EMPNO NUMBER(4)
CONSTRAINT EMP03_ENAME_NN NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(4),
CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY(EMPNO),
CONSTRAINT EMP03_JOB_UK UNIQUE(JOB),
CONSTRAINT EMP03_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO)
);<복합키를 기본키로 지정하기>
CREATE TABLE MEMBER01(
NAME VARCHAR2(10),
ADDRESS VARCHAR2(30),
HPHONE VARCHAR2(16),
CONSTRAINT MEMBER01_COMBO_PK PRIMARY KEY(NAME, HPHONE)
);
제약조건 변경
기존 테이블에 제약 조건 추가하기
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명]
제약조건 (칼럼명);
ALTER TABLE EMP01
ADD CONSTRAINT EMP01_EMPNO_PK PRIMARY KEY(EMPNO);
ALTER TABLE EMP01
ADD CONSTRAINT EMP01_DEPTNO_FK
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO);
기존 테이블에 NOT NULL 제약조건 추가하기
ALTER TABLE 테이블명
MODIFY 칼럼명 CONSTRAINT 제약조건명 NOT NULL;
ALTER TABLE EMP01
MODIFY ENAME CONSTRAINT EMP01_ENAME_NN NOT NULL;
제약조건 제거하기
ALTER TABLE 테이블명
DROP [CONSTRAINT 제약조건명];
ALTER TABLE EMP05
DROP CONSTRAINT EMP05_EMPNO_PK;
CASCADE : 제약 조건을 비활성화시킴.
(조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법)
-부모키가 사라지면 같이 사라지게 설정
alter table 자식테이블명
add constraint 설정할제약조건명
foreign key (외래키로 설정할 키)
references 부모테이블명(외래키로 설정할 키)
on delete cascade;-DISABLE CONSTRAINT : 제약 조건의 일시 비활성화
ALTER TABLE 테이블명
DISABLE [CONSTRAINT 제약조건명];
ALTER TABLE EMP01
DISABLE CONSTRAINT EMP01_DEPTNO_FK;
-ENABLE CONSTRAINT : 비활성화된 제약 조건을 해제하여 다시 활성화
ALTER TABLE 테이블명
ENABLE [CONSTRAINT 제약조건명];
ALTER TABLE EMP01
ENABLE CONSTRAINT EMP01_DEPTNO_FK;이때, 자식테이블에서 외래키 제약조건으로 지정한 칼럼은 비활성화 할 수 없기 때문에
1.자식 테이블의 외래키에 대한 제약조건을 먼저 비활성화 하고
2.부모 테이블의 기본키에 대한 제약조건을 비활성화 해주어야 한다.
CASCADE옵션은 이러한 번거로움을 없애준다.
CASCADE 옵션 : 부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을 때
부모 테이블의 제약 조건을 비활성화하면
이를 참조하고 있는 자식 테이블의 제약 조건까지 같이 비활성화시켜 주는 옵션
제약조건이 삭제될 때에도 같이 삭제된다.
ALTER TABLE DEPT01
DISABLE PRIMARY KEY CASCADE;