22-11-16 제약조건, 뷰, 시퀀스

<제약조건>

-종류 정확히 잘 알아둬야함. 가장 기본적인 개념.

-------------테이블 생성과 제약조건--------------
--primary key (테이블의 고유 키, 중복x, null허용x)
--unique (중복x)
--not null (null을 허용하지 않음)
--foreign key (참조하는 테이블의 pk를 저장하는 컬럼, 참조테이블의 pk에 업삳면 등록x. null허용)
--check (정의된 형식만 저장되도록 허용)

*NOT NULL 

*UNIQUE KEY : 중복허용x, null허용
Unique + not null = primary key라고 보면됨.

*primary key : 한 테이블에 한 컬럼만 있어야함.
   또 두개의 컬럼이 합쳐져서 하나의 PK컬럼이 될 수도있음(이것이 super key 확인해보기)

*CHECK : 들어갈 수 있는 데이터를 구별하기 위해 조건을 걸어준다.

*foreign key : pk에 없는 값은 fk에 못 들어감 ->

                      참조무결성 제약 : 참조하는 테이블의 주키가 아니면 foreign key에 못 들어감   중복,null - 가능
                      fk를 지정할 떄는 데이터타입과 사이즈 그리고 이름? 을 동일하게 맞춰서 지정해주는게 좋음.
   

#제약 조건 정의하는 법
-default 키워드 : 내가 값을 주지 않았을 때 기본으로 넣어주는 값.

*열 레벨 제약조건 정의하는 법
 -일반적으로 열 레벨에서 제약조건을 정의를 많이 함.

select * from user_constraints; --constraints 는 제약조건

create table dept2 (
    dept_no NUMBER(2)       CONSTRAINT dept2_no_pk PRIMARY KEY, --constraint 는 내가 직접 이름지정할 때 사용.
    dept_name VARCHAR2(15)  CONSTRAINT dept2_name_nn not null,
    loca NUMBER(4)          CONSTRAINT dept2_loca_locaid_fk REFERENCES locations(location_id),
    dept_date DATE          default sysdate,
    dept_bonus NUMBER(10)   default 0,
    dept_phone VARCHAR2(20) CONSTRAINT dept2_phone_uk UNIQUE,
    dept_gender CHAR(1)     CONSTRAINT dept2_gender_ck check(dept_gender in('M', 'F'))
);

--열레벨 제약(constraints 생략가능)
drop table dept2;

create table dept2 (
    dept_no NUMBER(2)       PRIMARY KEY, --constraint 는 내가 직접 이름지정할 때 사용.
    dept_name VARCHAR2(15)  not null,
    loca NUMBER(4)          REFERENCES locations(location_id),
    dept_date DATE          default sysdate,
    dept_bonus NUMBER(10)   default 0,
    dept_phone VARCHAR2(20) UNIQUE,
    dept_gender CHAR(1)     check(dept_gender in('M', 'F'))
);

*테이블 레벨 제약조건 정의하는 법
 - not null은 칼럼 줄에 넣어줌.★
 - constrains 생략이 안됨.
★SUPER KEY는 테이블 레벨 혹은 ALTER 키로만 지정이 가능. -> 하지만 SUPER KEY를 사용하는 경우가 많지는 않음.

--테이블 레벨 (not null만 열레벨 사용) 
drop TABLE dept2;

create table dept2 (
    dept_no NUMBER(2)       , --constraint 는 내가 직접 이름지정할 때 사용.
    dept_name VARCHAR2(15)  not null,
    loca NUMBER(4)          ,
    dept_date DATE          default sysdate,
    dept_bonus NUMBER(10)   default 0,
    dept_phone VARCHAR2(20) ,
    dept_gender CHAR(1)     ,
    
    CONSTRAINT dept2_no_pk PRIMARY KEY (dept_no/*, dept_name*/), --슈퍼키
    CONSTRAINT dept2_loca_locaid_fk FOREIGN KEY (loca) REFERENCES locations(location_id),
    CONSTRAINT dept2_phone_uk UNIQUE(dept_phone),
    CONSTRAINT dept2_gender_ck check(dept_gender in('M', 'F'))
);

#제약 조건의 위배

desc employees; --EMPLOYEES 테이블 제약조건 확인하는 명령
--개체무결성 위배(null, 중복값 pk에 들어가지 못함)
INSERT into employees(employee_id, last_name, email, hire_date, job_id)
values(100, 'test', 'test', sysdate, 'test');
--참조 무결성 위배(참조 테이블의 pk로 존재해야 fk에 들어갈 수 있음)
INSERT into employees(employee_id, last_name, email, hire_date, job_id, department_id)
values(501, 'test', 'test', sysdate, 'test', 5);
--도메인 무결성 위배(컬럼에 정의된 값만 들어갈 수 있음)
INSERT into employees(employee_id, last_name, email, hire_date, job_id, salary)
values(501, 'test', 'test', sysdate, 'test', -10);

<제약조건 관리>

-제약조건은 수정은 안되고 Alter을 통해서 추가 또는 삭제만 가능.

--제약조건 추가, 삭제 (변경불가)
Drop table dept2;
desc dept2;
create table dept2 (
    dept_no NUMBER(2)       , 
    dept_name VARCHAR2(15)  ,
    loca NUMBER(4)          ,
    dept_date DATE          default sysdate,
    dept_bonus NUMBER(10)   default 0,
    dept_phone VARCHAR2(20) ,
    dept_gender CHAR(1)     
);
--pk추가
alter table dept2 add constraints dept_no_pk primary key (dept_no);
--fk추가
alter table dept2 add constraints dept_loca_fk foreign key (loca) references locations(location_id);
--unique추가
alter table dept2 add constraints dept_phone_uk unique (dept_phone);
--check추가 - dept_gender
alter table dept2 add constraints dept_gender_ck check (dept_gender in('Y','N'));
--not null - 컬럼변경문으로
alter table dept2 modify dept_name varchar2(15) not null;

--제약조건 삭제 (제약조건명)
alter table dept2 drop constraints dept_loca_fk;

< 뷰(View) >

 

*뷰는 무엇인가 ?

view는 제한적인 자료만 보기 위해 사용할 수 있는 가상테이블의 개념이다
뷰는 기본테이블로 유도된 가상테이블 이기 때문에 필요한 컬럼만 저장해 두면 관리가 용이해집니다.
뷰는 가상테이블로 실제 데이터가 물리적으로 저장된 형태는 아닙니다.
뷰를 통해서 데이터에 접근하면 원본 데이터는 안전하게 보호할 수 있습니다.

- 실재로 존재하지 않는 가상의 테이블. 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블.
-단순 뷰 – 한 개의 원본테이블을 사용해서 생성
-복합 뷰 – 두 개 이상의 이본 테이블에 의해 생성( 조인을 통해 생성된..)
-뷰를 만들기 위해서는 뷰를 생성할 수 있는 권한이 있어야함.

 

★뷰를 사용하는 이유 : 뷰를 이용하면 데이터를 손쉽게 조회가능

 

#뷰를 통한 DML

*단순 뷰

-뷰를 사용할 때 주의할 점이 있다.

 뷰의 컬럼 이름은 함수같은 가상표현식이면 안됨.

--단순 뷰
--뷰의 컬럼 이름은 함수같은 가상표현식이면 안됩니다.
create or replace view view_emp
as (select employee_id,
           first_name ||' '|| last_name as name,
           job_id,
           salary
    from employees
    where department_id = 60
);

*복합 뷰

2개이상의 테이블이 있을 때를 말함.

--복합 뷰
--여러 테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해서 사용
create or replace view view_emp_dept_job
as (select
        e.employee_id,
        first_name ||' '|| last_name as name,
        d.department_name,
        j.job_title
        from employees e
        left outer join departments d
        on e.department_id = d.department_id
        left outer join jobs j
        on e.job_id = j.job_id)
        order by employee_id;
        
select * from view_emp_dept_job;

*뷰의 수정

--뷰의 수정(동일이름으로 만들면 수정됩니다)
create or replace view view_emp_dept_job
as (select
        e.employee_id,
        first_name ||' '|| last_name as name,
        e.salary, --추가
        d.department_name,
        j.job_title
        from employees e
        left outer join departments d
        on e.department_id = d.department_id
        left outer join jobs j
        on e.job_id = j.job_id)
        order by employee_id;

*뷰에 데이터를 INSERT를 하려면 제한이 많음

--뷰를 통한 DML은 제한이 많습니다.
--가상열 컬럼이 있다면 허용되지 않음
insert into view_emp_dept_job(name, employee_id) values('xxx', 300);
--원본테이블의 null을 허용하지 않는 경우도 안됩니다
insert into view_emp_dept_job(employee_id, salary) values(300, 10000);
--join된 뷰의 경우도 허용되지 않습니다
insert into view_emp_dept_job(employee_id, job_title) values(300, 'xxx');

*뷰의 삭제

drop view 뷰이름;

#뷰의 옵션
 -with check option -조건 컬럼 값을 변경하지 못하게 하는 옵션.
 -with read only - select만 허용하는 옵션

--뷰의 옵션
--with check option - 조건컬럼 제약
create or replace view view_emp_test
as (select employee_id, first_name, department_id
    from employees
    where department_id IN (60,70,80)
)
WITH check option;

--with read only - 읽기전용뷰(조회만 가능)
create or replace view view_emp_test
as (select employee_id, first_name, department_id
    from employees
    where department_id IN (60,70,80)
)
WITH read only;

#인라인 뷰

 
<SEQUENCE>

- CYCLE 는 보통 NOCYCLE로 설정. 이유는 Primary Key값이 max값을 찍고나서 다시 처음부터 시작되기 때문.

-Cache도 보통 NoCache로 설정함.

#NEXTVAL
 -다음 사용 가능한 시퀀스 값을 리턴

#CURRVAL
 -현재 시퀀스 값을 리턴
 -CURVAL이 참조되기 전에 NEXTVAL이 먼저 한번은 이용되어야 함.

 

--시퀀스(순차적으로 증가하는 값 - pk에 많이 사용됩니다)
select * from user_sequences;

--테이블
CREATE TABLE DEPT3(
    DEPT_NO NUMBER(2) PRIMARY KEY,
    DEPT_NAME VARCHAR2(20),
    LOCA VARCHAR2(20),
    DEPT_DATE DATE
);
--시퀀스 생성
Create sequence DEPT3_SEQ
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 10
    MINVALUE 1
    NOCYCLE 
    NOcache;
--시퀀스 삭제
DROP SEQUENCE DEPT3_SEQ;
--시퀀스 생성(기본값으로 생성)
CREATE SEQUENCE DEPT3_SEQ NOCACHE; -- nocache로 해주는게 좋음. 문제생기고 싶지 않으면.
--시퀀스 사용 CURRVAL, NEXTVAL
SELECT DEPT3_SEQ.CURRVAL FROM DUAL; --CURRBAL는 NEXTVAL한번 이후에 사용가능
SELECT DEPT3_SEQ.NEXTVAL FROM DUAL;

INSERT INTO DEPT3(DEPT_NO, DEPT_NAME, LOCA, DEPT_DATE)
VALUES (DEPT3_SEQ.NEXTVAL, 'TEST', 'TEST', SYSDATE);

--시퀀스 수정
ALTER SEQUENCE DEPT3_SEQ NOCACHE;
ALTER SEQUENCE DEPT3_SEQ MAXVALUE 1000;
ALTER SEQUENCE DEPT3_SEQ INCREMENT BY 10;

--시퀀스 테이블에서 사용되고 있다면 DROP하면 안됩니다
--시퀀스값을 초기화 하려면?
--1. 현재시퀀스 확인
SELECT DEPT3_SEQ.CURRVAL FROM DUAL;
--2. 증가값을 -현재시퀀스
ALTER SEQUENCE DEPT3_SEQ MINVALUE 0;
ALTER SEQUENCE DEPT3_SEQ INCREMENT BY -140;
--3. NEXTVAL로 실행
select DEPT3_SEQ.NEXTVAL FROM DUAL;
--4. 증가값을 1로 변경
ALTER SEQUENCE DEPT3_SEQ INCREMENT BY 1;
--5.실행
SELECT DEPT3_SEQ.NEXTVAL FROM DUAL;
--------------------------------------------------------------------------------
--시퀀스 사용의 응용
CREATE TABLE DEPT4(
    DEPT_NO VARCHAR2(30) PRIMARY KEY,
    DEPT_NAME VARCHAR2(30)
);
CREATE SEQUENCE DEPT4_SEQ NOCACHE;
--LPAD('값, '맥스길이', '채울값')를 이용해서 PK에 적용하는 값을 (년-0000시퀀스) 형태로 INSERT

INSERT INTO DEPT4 VALUES( TO_CHAR(SYSDATE,'YYYY')||LPAD(DEPT4_SEQ.NEXTVAL, 5,'0'), 'TEST');
SELECT * FROM DEPT4;