[ORACLE 1일차] DDL

* 외래키 사용하기

 

Create Table DEPT(

    deptno varchar2(4) primary key,

    depname varchar2(20)

);

Create Table EMP(

    empno number(10),

    ename varchar2(20),

    sal number(10,2) default 0,

    deptno varchar2(4) not null,

    createdate date default sysdate,

    constraint emppk primary key(empno),

    constraint deptfk foreign key(deptno) references dept(deptno)

);

외래키가 지정될려면 반드시 마스터 테이블이 생성되어야 한다. 여기서 부서 테이블이 마스터 테이블이 된다.

* cascade 옵션 사용하기

테이블을 생성할 때 cascade 옵션을 설정할 수 있다. cascade 옵션은 참조 관계 (기본키와 외래키의 관계) 가 있을 경우 자동으로 데이터도 삭제될 수 있는 것이다. 예시를 통해 공부해보자.

먼저 마스터 테이블인 DEPT 테이블을 생성한다.

Create Table DEPT(

    deptno varchar2(4) primary key,

    deptname varchar2(20)

);

INSERT INTO DEPT VALUES('1000','인사팀');

INSERT INTO DEPT VALUES('1001','총무팀');

Create Table EMP(

    empno number(10),

    ename varchar2(20),

    sal number(10,2) default 0,

    deptno varchar2(4) not null,

    createdate date default sysdate,

    constraint emppk primary key(empno),

    constraint deptfk foreign key(deptno) references dept(deptno)

    ON DELETE CASCADE

);

INSERT INTO EMP VALUES(100,'준우',1000,'1000',sysdate);

INSERT INTO EMP VALUES(101,'김범',2000,'1001',sysdate);

select * from dept; 를 할 경우.. 

delete from dept where deptno = '1000';

select * from emp; 를 하면 참조하고 있던 외래키가 삭제된 것을 볼 수 있다.

하지만 ON DELETE CASCADE 옵션을 사용하면 참조 무결성을 준수할 수가 있다.

참조 무결성이란 마스터 테이블(DEPT)에는 해당 부서번호가 없는데, 슬레이브 테이블(EMP)에는 해당 부서번호가 있는 경우를 참조 무결성 위배라고 한다.

* ALTER 

Alter table emp rename to new_emp;

 

ALTER TABLE 문은 칼럼추가, 변경, 삭제 등을 할 수 있다.

- 추가

ALTER TABLE EMP ADD (age number(2) default 1); 

- 삭제

ALTER TABLE EMP DROP COLUMN age; 

- 수정

ALTER TABLE EMP MODIFY (ename varchar2(40) not null);

- 칼럼명 변경

ALTER TABLE EMP RENAME COLUMN ename to new_ename;

* DROP

DROP TABLE EMP; 

DROP TABLE EMP CASCADE CONSTRAINT;

참조된 제약사항을 모두 삭제하는 것이다. 즉, 외래키로 지정한 부서번호도 삭제가 된다. 

* VIEW

뷰는 테이블로부터 유도된 가상의 테이블을 말한다.

실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 한다.

뷰는 데이터 딕셔너리에 SQL문 형태로 저장하고 실행 시에 참조된다. 

참조한 테이블이 변경되면 뷰도 변경된다.

뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 발생한다.

특정 칼럼만 조회해서 보안성을 향상시킨다.

한번 생성한 뷰는 변경할 수 없고 변경을 원하면 삭제 후 재생성해야 한다.

ALTER문을 사용하여 VIEW를 변경할 수 없다.

CREATE VIEW T_EMP AS SELECT * FROM EMP;

뷰의 생성은 다음과 같이 하고,

뷰의 조회는

SELECT * FROM T_EMP; 와 같이 테이블을 조회하는 형태로 똑같이 수행한다.

뷰의 삭제는 Drop view를 사용한다.