#6_140621_제약(constraint)

# 제약(constraint)

l 제약(constraints)

- 제약을 사용해야 하는 이유 : data의 품질을 높이기 위해서

l 제약의 종류 5가지

1.     primary key : 중복된 data, null 입력 안됨

2.     unique : 중복된 data 입력 안됨

3.     not null : null 입력 안됨

4.     check : 미리 정의된 data만 입력 수정 가능

5.     foreign key : 참조하는 컬럼에 제약

l 테이블 생성시 제약 거는 방법 2가지

1.     table level

              CREATE TABLE emp585(

              empno NUMBER(10),

              ename VARCHAR2(10),

              sal NUMBER(10),

CONSTRAINT emp585_ename_un UNIQUE(ename));

2.     column level

              CREATE TABLE emp590(

              empno NUMBER(10),

              ename VARCHAR2(10),

sal NUMBER(10)CONSTRAINT emp590_sal_nn NOT NULL);

- not null 제약은 column level 로만 가능하다(OCP)

1. primary key

문제237. 아래의 테이블을 생성하는데 empno에 primary key 제약을 걸어서 생성하시오

CREATE TABLE emp581(

empno NUMBER(10) PRIMARY KEY,

ename VARCHAR2(10),

sal NUMBER(10));

문제238. 위에서 생성한 emp581 테이블에 데이터를 입력하는데 empno에 중복된 data와 null을 입력하시오

INSERT INTO emp581 VALUES(1111,'JONE',1234);

INSERT INTO emp581 VALUES(1111,'ALLEN',1234);

문제239. 위에서 생성한 테이블에 사원번호에 중복된 data를 입력할 수 있도록 제약을 삭제하시오

- 제약이름을 확인

SELECT constraint_name

FROM user_constraints

WHERE table_name='EMP581';

- 제약 삭제

ALTER TABLE emp581

DROP CONSTRAINT SYS_C007108;

문제240. 아래와 같이 테이블을 생성하시오

create table emp584(

empno number(10),

ename varcahr2(10),

sal number(10),

constraint emp584_empno_pk primary key(empno));

문제241. emp584 테이블에 empno에 걸린 primary key 제약을 삭제하시오

ALTER TABLE emp584

DROP CONSTRAINT emp584_empno_pk;

문제248. dept 테이블에 deptno 에 primary key 제약 생성

- 이미 만들어진 테이블에 제약을 생성
alter table dept

add constraint dept_deptno_pk primary key(deptno);

2. unique

문제242. 아래 테이블을 생성하시오

테이블명 : emp585

컬럼명 : empno, ename , sal

- unique 제약을 걸어서 생성하시오,

CREATE TABLE emp585(

empno NUMBER(10),

ename VARCHAR2(10),

sal NUMBER(10),

CONSTRAINT emp585_ename_un UNIQUE(ename));

문제243. 위에서 생성한 emp585 테이블에 ename 컬럼에 null 은 중복해서 입력이 되는지 확인해보시오

INSERT INTO emp585 VALUES(1111,NULL,3000);

INSERT INTO emp585 VALUES(1111,NULL,3000);

l null은 알수 없는 값이기 때문에 중복해서 입력이 가능

3. not null

문제244. 아래의 테이블을 생성하시오

테이블명 : emp590

empno

ename

sal        ß not null 제약을 걸어서 생성

CREATE TABLE emp590(

empno NUMBER(10),

ename VARCHAR2(10),

sal NUMBER(10)CONSTRAINT emp590_sal_nn NOT NULL);

4. check

문제245. 아래의 테이블을 생성하시오

CREATE TABLE emp591(

empno NUMBER(10),

ename VARCHAR2(10),

sal NUMBER(10),

CONSTRAINT emp591_sal_ck check(sal BETWEEN 0 AND 8000));

l 위와 같이 테이블을 생성하면 월급에 0~8000 사이의 data만 입력되거나 수정될 수 있다.

문제245. 위에서 생성한 emp591 테이블에 data를 입력하시오

INSERT INTO emp591 VALUES(8484,'ALLEN',9000);

문제247. 아래의 테이블을 생성하시오

테이블명 : dept800

컬럼명 : deptno, loc, dname

# DALLAS, CHICAGO 만 입력되도록 check 제약생성

CREATE TABLE dept800(

deptno NUMBER(10),

loc VARCHAR2(10),

dname VARCHAR2(10),

CONSTRAINT dept800_loc_ck check(loc IN ('DALLAS','CHICAGO')));

INSERT INTO dept800 VALUES(10,'DALLAS','SALES');

문제253. 아래의 테이블을 생성하시오. 이름에 데이터를 입력할 때 성이 김씨와 이씨만 입력되도록 check 제약을 걸어서 생성하시오

                                                                           

테이블명 : emp705

컬럼명 : empno, ename, sal

# check 제약 생성

CREATE TABLE emp705(

empno NUMBER(10),

ename VARCHAR2(10),

sal NUMBER(10),

CONSTRAINT emp705_ename_ck check(ename LIKE '김%' OR ename LIKE '이%'));

insert into emp705 values(9393,’김인호’,4000);

insert into emp705 values(9393,’최인호’,4000);

5. foreign key

문제249. emp 테이블에 deptno에 foreign key 제약을 걸고 dept 테이블에 deptno를 참조해라

ALTER TABLE EMP

ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)

REFERENCES DEPT(deptno);

문제250. emp 테이블에 아래의 데이터를 입력하시오

empno 9382

ename JACK

sal 5999

deptno 80

INSERT INTO EMP VALUES(9382,'JACK',5999,80);

- 부서테이블에 80번이 없기 때문에 들어가지 않는다.

문제251. dept 테이블에 10번 부서번호를 삭제하시오

DELETE FROM dept

WHERE deptno=10;

문제252. dept 테이블에 deptno 에 걸린 primary key 제약을 삭제하시오

ALTER TABLE DEPT

DROP CONSTRAINT DEPT_DEPTNO_PK CASCADE;

l cascade 옵션 : primary key 삭제하면서 연관된 자식키들도 삭제한다.