30일차: Oracle - 제약조건/뷰 2021.07.26

목차

1.     데이터 무결성 제약 조건

2.     뷰(View)

 

 

1. 데이터 무결성 제약 조건 <13장>             → ★2021_07_26_01 참고

 

 

1) primary Key (기본키) 제약 조건

- unique + not null의 형태

- primary key로 설정한 컬럼은 값이 중복되지 않아야 하고, null 값을 허용하지 않는다.

- ex) 부서 테이블(DEPT) - deptno (pk)

      사원 테이블(EMP) - empno (pk)

 

- ex) primary key 설정 예시

       create table emp05( empno number(4) primary key,

                                  ename varchar2(12) not null,

                                  job varchar2(12),

                                 deptno number(2) );

- ex) 제약 조건에 위배되는 경우

       insert into dept values(10, '개발부', '서울');   → unique 제약 조건 위배

       insert into dept values(null, '개발부', '서울');  → not null 제약 조건 위배

 

• DEPT 테이블의 제약조건에 위배되는 경우

insert into dept values(10, '개발부', '서울');

→ ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

   (unique 제약 조건 위배, DEPT 테이블에 이미 10번 부서 존재)

 

insert into dept values(null, '개발부', '서울');

→ ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")

   (not null 제약 조건에 위배)

 

• EMP 테이블의 제약조건에 위배되는 경우

insert into emp(empno, ename) values(7788, '홍길동');

→ ORA-00001: unique constraint (SCOTT.PK_EMP) violated

   (unique 제약 조건 위배, EMP 테이블 SCOTT 사원의 사원 번호와 중복됨)

 

insert into emp(empno, ename) values(null, '홍길동');

→ ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

   (not null 제약 조건에 위배)

 

2) foreign key (외래키) 제약 조건

- 부모 테이블과 자식 테이블의 관계 설정과 관련 있는 제약 조건

- 부모 테이블 : 참조되는 테이블

                    primary key(기본키) 또는 unique(유일키) 제약 조건으로 설정되어 있어야 한다.

- 자식 테이블 : 참조하는 테이블

                    부모 테이블의 부모 키의 값만 참조 가능하다.

- ex) DEPT (부모 테이블) - deptno (pk, 부모키) : 10, 20, 30, 40

       EMP (자식 테이블) - deptno (fk) : 10, 20, 30

 

 

- foreign key 제약 조건이 지정된 사원 테이블(EMP)에 부서 테이블(DEPT)에 존재하지 않는 50번 부서 번호를 저장하게 되면 오류가 발생한다.

ex. insert into emp(empno, deptno) values(1111, 50);

→ ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

    (foreign key 제약 조건 위배, 참조하는 부모키에 50이 존재하지 않음)

- 형식 : 컬럼명 자료형 references 부모 테이블명(컬럼명)

- ex) foreign key 설정 예시

       create table emp06( empno number(4) primary key,

                       ename varchar2(10) not null,

                       job varchar2(10),

                       deptno number(2) references dept(deptno) );

 

3) check 제약조건

- 데이터가 입력될 때, 특정 조건을 만족하는 데이터만 입력되도록 만들어주는 제약 조건

- 조건으로 데이터의 값의 범위나 특정 패턴의 숫자나 문자 값을 설정할 수 있다.

- ex) create table emp07( empno number(4) primary key,

                       ename varchar2(10) not null,

                       sal number(7,2) check(sal between 500 and 5000),

                       gender varchar2(1) check(gender in('M','F') ) );

 

       insert into emp07 values(1111, '홍길동', 3000, 'M');   → 정상적인 데이터 입력

       insert into emp07 values(1112, '전지현', 8000, 'F');    → check 제약 조건 위배

       insert into emp07 values(1113, '송은이', 5000, 'f');    → check 제약 조건 위배

 

4) default 제약 조건

- default 제약 조건이 설정된 컬럼에 값이 입력되지 않으면 default로 설정된 값이 자동으로 입력된다.

- ex) create table dept01( deptno number(2) primary key,

                        dname varchar2(14),

                        loc varchar2(13) default 'SEOUL' );

 

     insert into dept01 values(10, 'ACCOUTING', 'NEW YORK');

     insert into dept01(deptno, dname) values(20, 'RESEARCH');  → loc 컬럼값 누락시킴

 

→ loc에 ‘null’이 아닌 default 제약 조건으로 설정된 ‘SEOUL’이 들어감

 

5) 제약 조건 이름(constraint name) 설정

- 제약 조건 이름을 따로 설정하지 않으면 오라클 서버가 자동으로 제약 조건명을 부여한다.

 (ex. SYS_C007096)

- 제약 조건을 삭제할 때 제약 조건 이름이 필요하다.

- 형식: [테이블명]_[컬럼명]_[제약 조건 유형] (ex. EMP04_EMPNO_UK)

- ex) 제약 조건 이름 설정 예시

       create table emp04( empno number(4) constraint emp04_empno_pk primary key,

                                  ename varchar2(20) constraint emp04_ename_nn not null,

                                  job varchar2(20),

                                 deptno number(2) );

 

6) 제약 조건 설정 방식

① 컬럼 레벨(column level) 방식

- 하나의 컬럼 뒤에 연이어 제약 조건을 지정하는 방식

 

create table emp01( empno number(4) primary  key,

                          ename varchar2(15) not null,

                          job varchar2(10) unique,

                         deptno number(4) references dept(deptno) );

 

② 테이블 레벨(table level) 방식

- 컬럼을 모두 정의하고 생성된 컬럼들에 대한 제약 조건을 따로 한꺼번에 지정하는 방식

- not null은 컬럼 레벨로만 설정 가능

 

create table emp01(

                empno number(4),

                ename varchar2(15) not null,

                job varchar2(10),

                deptno number(4),

                primary key(empno),

                unique(job),

                foreign key(deptno) references dept(deptno) );

 

- 다음의 두 경우에서는 테이블 레벨 방식으로만 제약 조건 설정 가능

① 기본키(primary key)를 복합키로 설정하는 경우(2개 이상의 컬럼을 기본키로 설정하는 경우)

create table member01( id varchar2(20),

                                passwd varchar2(20),

                                primary key(id, passwd) );

 

② alter table로 제약 조건을 추가하는 경우

제약 조건이 없는 테이블 생성

create table emp01 ( empno number(4),

                            ename varchar2(15),

                            job varchar2(10),

                           deptno number(2) );

 

primary key 제약 조건 추가 : empno

alter table emp01 add primary key(empno);

 

not null 제약 조건 추가 : ename

alter table emp01 modify ename not null;

 

unique 제약 조건 추가 : job

alter table emp01 add unique(job);

 

foreign key 제약 조건 추가 : deptno

alter table emp01 add foreign key(deptno) references dept(deptno);

 

7) 제약 조건 삭제

- 제약 조건을 제거하기 위해서는 제약 조건명을 반드시 제시해야 한다.

- 제약 조건명을 constraint문을 사용하여 지정하지 않았을 경우, 시스템에서 부여한 이름을 찾아봐야 한다.

- 형식: alter table 테이블명 drop constraint 제약조건명;

- ex) alter table emp01 drop constraint SYS_C007083;

       alter table emp01 drop primary key;

 

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

- 자식 테이블이 있는 경우 부모 테이블 삭제 불가

(→ 자식 테이블에서 참조하고 있기 때문 - child record found)

- 부모 테이블의 데이터를 삭제하려면, 자식 테이블의 foreign key 제약 조건을 비활성화 시켜야 한다.

- 활성화/비활성화 형식: alter table 테이블명 enable/disable constraint 제약조건명;

- ex) alter table emp01 enable constraint SYS_C007091;

       alter table emp01 disable constraint SYS_C007091;

 

• CASCADE 옵션

- cascade 옵션을 붙여서 부모 테이블의 제약 조건을 ‘비활성화’시키면, 참조하고 있는 자식 테이블의 foreign key 제약 조건도 같이 비활성화 된다.

- ex) alter table dept01 disable constraint SYS_C007087 cascade;

     (→ 부모 테이블인 dept01, 자식 테이블인 emp01 모두 제약 조건이 비활성화 됨)

- cascade 옵션을 붙여서 부모 테이블의 primary key 제약 조건을 ‘삭제’하면, 참조하고 있는 자식의 foreign key 제약 조건도 같이 삭제된다.

- ex) alter table dept01 drop primary key cascade;

    (→ 부모 테이블인 dept01의 primary key가 삭제되면서, 자식 테이블인 emp01의 foreign key도 같이 삭제된다.)

 

2. 뷰(View) <14장>                → ★2021_07_26_02 참고

 

1) 뷰(View)의 개념과 형식

- 물리적인 테이블을 근거한 논리적인 가상 테이블

- 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문이다.

- 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데, 이 테이블을 ‘기본 테이블’이라고 한다.

- 뷰(view)는 ‘보다’라는 의미처럼 실제 테이블에 저장된 데이터를 뷰를 통해 볼 수 있다.

- 뷰에 insert, update, delete 명령문으로 데이터를 조작하면, 기본 테이블의 데이터도 영향을 받는다.

- 사용자마다 특정 객체만 조회할 수 있도록 권한을 부여를 할 수 있어서 동일한 테이블에 접근하는 사용자마다 서로 다르게 보도록 할 수 있다.

 

- 뷰를 통해 정보를 제한적으로 제공할 수 있기 때문에 보안에 유리하다.

- 형식: create view 뷰이름 as 서브쿼리;

- ex) 뷰 생성

       create view emp_view30

       as

       select empno, ename, deptno from emp_copy where deptno=30;

- ex) 뷰 확인

       select * from tab;              → 테이블과 뷰 목록 확인

       select * from user_views;    → 생성된 뷰에 대한 자세한 정보 확인

       desc emp_view30;             → 생성된 뷰의 구조 확인

       select * from emp_view30;  → 생성된 뷰의 내용 확인

 

* SCOTT 계정에 view를 만들 수 있도록 권한을 부여해야 함

(ORA-01031: insufficient privileges → SCOTT 계정에는 뷰를 생성할 권한이 없음)

SYSTEM 계정 연결하여 데이터 제어어(DCL)로 권한 부여

‘grant create view to scott;’

 

2) 뷰(View)의 종류

• 단순 뷰(simple view) : 하나의 기본 테이블로 생성된 뷰

• 복합 뷰(complex view) : 여러 개의 기본 테이블로 생성된 뷰

 

① 단순 뷰(simple view)

Q. 기본 테이블인 EMP_COPY를 이용해서 20번 부서에 소속된 사원들의 사번, 이름, 부서 번호, 직속 상관의 사번을 출력하기 위한 뷰(EMP_VIEW20)를 생성하세요.

create view emp_view20

as

select empno, ename, deptno, mgr from emp_copy where deptno=20;

 

② 복합 뷰(complex view)

Q. 각 부서별(부서명) 최대 급여와 최소 급여를 출력하는 뷰를 sal_view라는 이름으로 생성하세요.

create view sal_view

as

select dname, max(sal) MAX, min(sal) MIN from dept, emp

    where dept.deptno = emp.deptno group by dname;

 

3) 뷰 삭제

- 형식 : drop view 뷰 이름;

- ex) drop view sal_view;