데이터 무결성을 위한 제약 조건

1) 데이터 무결성 제약 조건 : 
테이블에 부적절한 자료가 입력되는 것을 방지하기 위해
테이블 생성시 각 컬럼에 대해 정의하는 규칙

- DESC 테이블 이름  -> 테이블 구조 확인 가능 , NOT NULL 제약 조건 확인 가능 

- DESC USER_CONSTRAINTS; 
:  제약 조건에 관한 정보 알려줌 

 

OWNER  : 제약 조건을 소유한 사용자명 저장하는 칼럼
CONSTRAINT_NAME : 제약 조건 명
CONSTRAINT_TYPE :  제약 조건 유형 저장

TABLE_NAME : 제약 조건이 어느 테이블 소속인지

 

- USER_CONS_COLUMNS : 제약 조건이 지정된 컬럼 명 알려줌

   

2) NOT NULL 제약 조건 지정 : 
- 컬럼 레벨로 정의 가능

COLUMN_NAME DATA_TYPE CONSTRAINT_TYPE

CREATE TABLE EMP02(
EMPMO NUMBER(4) NOT NULL.
ENAME VARCHAR(10)  NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2));

3) UNIQUE 제약 조건
- 유일한 값만 허용 
- NULL 값은 증복되어 저장 가능 

CREATE TABLE EMP01(
EMPNO NUMBER(4) UNIQUE
ENAME VARCHAR (10) NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2));

NULL은 중복되어 저장 가능

INSERT INTO EMPO01
VALUES(NULL, 'JONES', 'A',20);

INSERT INTO EMPO01
VALUES(NULL, 'JONES', 'B',10);

4) 컬럼 레벨로 제약 조건명 명시해서 제약 조건 설정
(그동안은 제약 조건만 명시 , 오라클 서버가 자동으로 제약 조건명 부여) 

- 제약 조건 위배시 오류 메시지에 조건명만 출력

COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME CONSTRAINT_TYPE

 

제약 조건명(제약조건명)  :   

[테이블명]_[칼럼명]_[제약 조건 유형]
EMP04_EMPNO_UK

 

CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT EMP_EMPNO_UK UNIQUE,
//COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME CONSTRAINT_TY
ENAME VARCHAR2(10) CONSTRAINT EMP_ENAME_NN NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2));

 

생성된 제약 조건 확인
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('EMP');

 

위의 결

            
5) PRIMARY KEY : 데이터 구분을 위한 제약 조건 
- 테이블 내의 해당 행을 다른 행과 구분 할 수 있도록 하는 칼럼은
반드시 존재해야함
- 식별 기능을 갖는 칼럼은 유일하면서 NULL 값 허용 X 
- UNIQUE + NOT NULL 

 

CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT EMP_EMPNO_PK PRIMARY KEY
ENAME VARCHAR2(10) CONSTRAINT EMP_ENMAE_NN NOT NULL,
JOB VARHAR(9),
....;

6) CHECK 제약 조건
- 입력되는 값을 체크하여 설정된 값 이외의 값이들어오면 수행 X 

 

**

SAL NUMBER(7,2)
CONSTRAINT EMP07_SAL_CK CHECK(SAL BETWEEN 500 AND 5000)
GENDER VARCHAR(1)
CONSTRAINT EMP07_GENDER_CK CHECK(GENDER IN ('M' ,'F')

7) DEFAULT 제약 조건
- 아무런 값을 입력 하지 않았을 때 디폴트제약의 값이 입력 

LOC VARCHAR2(13) DEFAULT 'SEOUL'

 

8) FOREIGN KEY 제약 조건

- 참조의 무결성

- 두 테이블 사이의 주종 관계에 의해서 결정

- 주체 : 부모 테이블

- 종속 : 자식 테이블

- 부모 키 :  기본키나 유일키로 설정 되어야 함 

 

 

EX ) 사원은 회사 내에 존재하는 부서에 소속되어야 한다. 

: 자식 테이블인 사원 테이블의 부서번호 칼럼에 부모 테이블인

부모 테이블의 부서번호를 부모 키로 설정하는 것

 

 

R_CONSTRAINT_NAME: 외래키인 경우 어떤 PRIMARY KEY를 참조했는지 나타

 

CREATE TABLE EMP(
EMPNO NUMBER(4)
CONSTRAINT EMP_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10)
CONSTRAINT EMP_ENMAE_NM NOT NULL,
JOB VARCHAR2(8)
DEPTNO NUMBER(2)
CONSTRAINT EMP_DEPTNO_FK REFERENCES DEPT(DEPTNO)
);

// 만약 부모키에 존재하지 않는 부서번호 추가시 오류 발생

 

9) 테이블 레벨 방식으로 재약 조건 지정

: 컬럼을 모두 정의하고 나서 테이블 정의를 마무리 짓기 전에 제약 조건 한번에 지정 

 

- NOT NULL조건은 테이블 레벨로 제약 조건 설정 불가

 

- 복합키로 기본키를 지정할 경우 사용

: 기본키가 2개 이상일 때 

 

- ALTER TABLE로 제약 조건 추가할때 

: 테이블의 정의가 완료되어 이미 테이블의 구조가 결정난 후에 나중에 제약조건 추가할때

 

컬럼 레벨 제약 조건 : 

CREATE TABLE 로 테이블 생성하면서 컬럼 정의 - 하나의 컬럼이 마무리 되기 전에 작성

 

형식 ) 

 

CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT EMP_ENAME_NN NOT NULL,
//NOTNULL은 테이블레벨 형식으로 작성 불가
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
DEPTNO NUMBER(4),
CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO),
CONSTRATNT EMP_JOB_UK UNIQUE(JOB),
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTNO)
REFERECENCS DEPT(DEPTNO)

 

 

 1) 복합키를 기본 키로 지정

이름과 핸드폰 번호를 복합하여 이를 기본 키로 지정
CREATE TABLE MEMBER(
NAME VARCHAR2(10),
ADDRESS VARCHAR2(30),
HPHONE VARCHAR2(16),
CONSTRAINT MEMEBER_COMBO_PK PRIMARY KEY(NAME,HPHONE)
);

 

2) 제약 조건 추가하기

:테이블이 이미 생성된 이후에 테이블의 구조 변경

- 이미 테이블을 생성된 이후에 변경하는 것이기에 ALTER TABLE로 추가

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO)


ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DETPNO);

 

 

[ MODIFY로 NOT NULL 제약 조건 추가]

- NOT NULL 제약 조건은 ADD 대신 MODIFY 명령문 사용 

(NULL 허용 상태에서 NOT NULL 상태로 변경하겠다는 의미로 이해)

 

ALTER TABLE EMP
MODIFY ENAME CONSTRAINT EMP_ENAME_NN NOT NULL;

 

[제약 조건 제거]

- 제약 조건명 반드시 제

 

ALTER TABLE EMP
DROP CONSTRAINT EMP_EMPNO_PK;

 

 

10) 제약조건의 비활성화와 CASCADE

- 제약 조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법

 

 

 

비활성화 형식)

부서 테이블 :
CREATE TABLE DEPT(
DEPTNO NUMBER(2) CONSTRAINT DEPT_DPETNO_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

사원 테이블:
CREATE TABLE EMP(
EMPNO NUMBER(4)
CONSTRAINT EMP_EEMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10)
CONSTRAINT EMP_ENAME_NN NOT NULL,
JOB VARHCAR2(9),
DEPTNO NUMBER(4)
CONSTRAINT EMP_DEPTNO_FK REFERENCES DEPT(DEPNO)
);

//부서 테이블의 10번 부서 삭제시 -> 오류 발생
: 자식 테이블인 사원 테이블은 부모 테이블은 부서 테이블을 참조 하고 있기 때문에 무결성에 위배


//외래키 제약 조건 비활성화 시키기

ALTER TABLE EMP 
DISABLE CONSTRAINT EMP_DEPTNO_FK;


//다시 10번 부서 삽입

INSERT INTO DEPT
VALUES(19,'ACCOUNT','NY');
SELECT * FROM DEPT;


//10번 부서를 새로입력했으니 활성화 가능 (입력 안하면 규칙 위배)

ALTER TABLE DEPT
ENABLE CONSTRAINT EMP_DEPTNO_FK;

 

횔성화 형식)

 

[CASCADE 옵션]

: 부모 테이블의 제약 조건을 비활성/삭제 하면 이를 참조하고 있는 자식 테이블의 제약 조건도

같이 비활성화/ 삭제 

//부모, 자식 한꺼번에 비활성화
ALTER TABLE DEPT
DISABLE PRIMARY KEY CASCADE;

//부모 자식 연속 삭제

ALTER TABLE DEPT
DROP PRIMARY KEY CASCADE;