DATABASE Constraint, View, Sequence

Let's Just Go·2022년 5월 8일

Database oracle

DATABASE

목록 보기

6/11

DATABASE

Constraint

Constraint
- 테이블의 해당 COLUMN에 사용자가 입력 데이터에 제약을 설정하여 무분별한 데이터 변경을 방지하기 위한 조건
- 유형
  - NOT NULL : COLUMN에 NULL값이 들어갈 수 없음
  - UNIQUE KEY : 테이블의 모든 행에 대해 유일해야 하는 값을 가진 열 또는 열의 조합을 지정 NULL 허용
  - PRIMARY KEY : 유일하게 테이블의 각 행을 식별 NULL 허용하지 않음
  - FOREIGN KEY : 열과 참조된 테이블의 열 사이의 외래키 관계를 적용하고 설정
  - CHECK : TRUE여야 하는 조건을 지정

테이블과 제약 조건을 동시에 생성

테이블과 제약 조건을 동시에 추가

CREATE TABLE dept2 (
    dept_no     NUMBER(2)
        CONSTRAINT dept2_deptno_pk PRIMARY KEY,
    dept_name   VARCHAR2(14) NOT NULL
        CONSTRAINT dept2_deptname_uk UNIQUE,
    loc         NUMBER(4)
        CONSTRAINT dept2_loca_locid_fk
            REFERENCES locations ( location_id ),
    -- 외래키로 누구를 참조하고 있는지 확인
    dept_bonus  NUMBER(10),
    dept_gender VARCHAR2(1) NOT NULL
        CONSTRAINT dept2_gender_ck CHECK ( dept_gender IN ( 'M', 'F' ) )
    --CHECK 제약조건을 통해서 dept_gender에 들어갈 수 있는 값은 M or F만 들어갈 수 있도록 작성
);

테이블 생성 후 제약 조건 선언

테이블을 생성하고 제약 조건 추가

-- 테이블레벨 제약조건 (모든 열 선언 후 제약조건을 취하는 방식)
CREATE TABLE dept2 (
    dept_no     NUMBER(2),
    dept_name   VARCHAR2(14) NOT NULL,
    loc         NUMBER(4),
    dept_date   DATE,
    dept_bonus  NUMBER(10),
    dept_gender VARCHAR2(1),
    
    CONSTRAINT dept2_deptno_pk PRIMARY KEY(dept_no),
    CONSTRAINT dept2_deptname_uk UNIQUE(dept_name),
    CONSTRAINT dept2_loc_locid_fk FOREIGN KEY (loc) REFERENCES locations(location_id),
    -- 외래키로 어떤 키를 사용하는지와 어떤 컬럼이 참조하고 있는지 표현
    CONSTRAINT dept2_deptdate_uk UNIQUE(dept_date),
    
    CONSTRAINT dept2_gender_ck CHECK (dept2_gender IN ('M', 'F'))
    -- CHECK 제약조건을 통해서 gender컬럼에 들어갈 수 있는 값을 개별 지정할 수 있음   
);

제약 조건 예시

실제 제약 조건이 어떻게 작용하는지 확인

INSERT INTO dept2 VALUES (
    10,
    '처음부서',
    '3000',
    sysdate,
    '100000',
    'M'
);

INSERT INTO dept2 VALUES (
    20,
    '두번째부서',
    '4000',
    sysdate,
    '200000',
    'M'
);
--외래키로 사용하는 location_id에 해당 값이 없으므로 dept2에서 참조할 수 없으므로 에러발생 
--즉, 외래키가 부모테이블에 없다면 INSERT가 불가능
INSERT INTO dept2 VALUES (
    10,
    '처음부서',
    '3000',
    sysdate,
    '100000',
    'M'
);
--PK인 deptno와 UK인 deptname가 있으므로 중복이 있는 데이터는 삽입 X

SELECT
    *
FROM
    dept2;
--외래키가 부모테이블에 있다면 INSERT 가능 

UPDATE locations
SET
    location_id = 4000
WHERE
    location_id = 1800;
--locations테이블에 location_id가 1800인 것을 4000으로 바꿔줄려고 하는데 
--dept2 테이블에 제약조건이 걸려있어 변경하지 못함
--locations의 pk가 location_id이므로(외래키 제약 조건 위반)

제약 조건 변경 및 삭제

제약 조건은 추가, 삭제가 가능하지만 변경은 안됨
변경을 하기 위해서는 삭제하고 새로운 내용으로 추가

ALTER TABLE 테이블명 ADD CONSTRAINT ~~;

CREATE TABLE dept2 (
    dept_no     NUMBER(2),
    dept_name   VARCHAR2(14) NOT NULL,
    loc         NUMBER(4),
    dept_date   DATE,
    dept_bonus  NUMBER(10),
    dept_gender VARCHAR2(1)
);

--PK추가 
ALTER TABLE dept2 ADD CONSTRAINT dept_no_pk PRIMARY KEY ( dept_no );
--FK추가
ALTER TABLE dept2
    ADD CONSTRAINT dept_loca_fk FOREIGN KEY ( loc )
        REFERENCES locations ( location_id );
--CHECK추가
ALTER TABLE dept2
    ADD CONSTRAINT dept2_gender_ck CHECK ( dept_gender IN ( 'M', 'F' ) );
--UNIQUE추가
ALTER TABLE dept2 ADD CONSTRAINT dept2_deptdate_uk UNIQUE ( dept_date );
--NOT NULL은 COLUMN 수정 형태로 변경
ALTER TABLE dept2 MODIFY
    dept_date DATE NOT NULL;

--제약조건 삭제 (제약조건 이름으로)
ALTER TABLE dept2 DROP CONSTRAINT dept_no_pk;

VIEW

VIEW
- 제한적인 자료만 보기 위해 사용하는 가상 테이블 개념
- 사용자가 원하는 방식으로 만든 가상의 테이블
- 물리적으로 저장된 형태가 아니므로 뷰를 통해 데이터에 접근하면 원본 데이터는 안전하게 보호될 수 있음
- 내가 원하는 조건에서 가상의 테이블을 만들어 빠르게 조회 가능
- CREATE VIEW 뷰이름 AS (사용자가 원하는 테이블 or 테이블들);

Simple VIEW

하나의 테이블로 뷰 생성

SELECT
    employee_id,
    first_name
    || ' '
    || last_name AS name,
    job_id,
    salary
FROM
    employees
WHERE
    department_id = 60;

-- CREATE VIEW 
CREATE VIEW view_emp AS
    ( SELECT
        employee_id,
        first_name
        || ' '
        || last_name AS name,
        job_id,
        salary
    FROM
        employees
    WHERE
        department_id = 60
    );

SELECT
    *
FROM
    view_emp;

Complex View

2개 이상의 테이블로 뷰 생성

여러 테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해 사용

CREATE VIEW view_emp_dept_jobs AS
    ( SELECT
        e.employee_id,
        e.first_name
        || ' '
        || e.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 ASC;
--정렬은 ()를 나와서 사용해야함
SELECT
    *
FROM
    view_emp_dept_jobs;

뷰 대체(CREATE OR REPLACE VIEW)

이미 만들어진 뷰에 데이터를 추가하고 싶을 때 사용
뷰를 변경하는게 아니라 대체하는 것이라 생각

CREATE OR REPLACE VIEW : 기존의 뷰가 있으면 대체하고 없으면 생성해줌

CREATE OR REPLACE VIEW view_emp_dept_jobs AS (
    SELECT
        e.employee_id,
        e.first_name
        || ' '
        || e.last_name AS name,
        d.department_name,
        j.job_title,
        e.salary -- 컬럼 추가 
    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 ASC;

--생성된 뷰를 바탕으로 조회 
SELECT
    job_title,
    AVG(salary)
FROM
    view_emp_dept_jobs
GROUP BY
    job_title
ORDER BY
    AVG(salary) DESC;

DROP VIEW
- 이미 만들어진 뷰를 삭제
```
DROP view view_emp;
```

조건 제약 컬럼

뷰를 통해서 데이터 조작을 할 수 없게 해주는 키워드
WITH CHECK OPTION CONSTRAINT : UPDATE를 하지 못하게 제약을 줌

WITH READ ONLY : SELECT만 허용하고 나머지 작업은 제약을 줌

CREATE VIEW view_emp_test AS
    ( SELECT
        employee_id,
        first_name,
        last_name AS name,
        hire_date,
        job_id,
        department_id
    FROM
        employees
    WHERE
        department_id = 60
    )
WITH CHECK OPTION CONSTRAINT view_emp_test_ck;

UPDATE view_emp_test
SET
    department_id = 100
WHERE
    employee_id = 105;
--WITH CHECK OPTION으로 인해서 UPDATE안됨

CREATE OR REPLACE VIEW view_emp_test AS
    ( SELECT
        employee_id,
        first_name,
        last_name AS name,
        hire_date,
        job_id,
        department_id
    FROM
        employees
    WHERE
        department_id = 60
    )
WITH READ ONLY;

INSERT INTO view_emp_test VALUES (
    300,
    'test',
    'test',
    sysdate,
    'IT_PROG',
    100
);
--WITH READ ONLY로 제약을 줬기 때문에 select말고는 아무것도 안됨

SEQUENCE

SEQUENCE

자동적으로 유일 번호를 생성하며 주로 기본 키 값을 생성하기 위해 사용
순차적으로 증가하는 값
NEXTVAL : INCREMENT BY 에서 지정한 값을 바탕으로 증가 및 감소하면서 값을 자동으로 넣어줌
CURRVAL : 현재 sequence의 value 값 확인 가능

지정한 SEQUENCE를 수정할 수 있지만 시작값(START WITH)은 바꿀 수 없음

SELECT
    *
FROM
    user_sequences;

-- 시퀀스 생성 
CREATE SEQUENCE dept2_seq START WITH 1 -- 시작값 (default 증가할 때 최소값, 감소할 때 최소값)
 INCREMENT BY 1 -- 증가값 (양수면 증가, 음수면 감소, default 1)
 MAXVALUE 10 -- 최종값 (기본값 증가일 때 1027, 감소이면 -1)
 MINVALUE 1 -- 최소값 (기본값 증가일 때 1, 감소일때 -1028)
 NOCACHE -- CACHE MEMORY 사용여부 
 NOCYCLE; -- 순환 여부(default NOCYCLE) EX) 순환하게 되면 1~10까지 순환하고 다시 1~10 반복

CREATE TABLE dept3 (
    dept_no   NUMBER(2),
    dept_name VARCHAR2(14),
    loca      VARCHAR2(13),
    dept_date DATE
);

RENAME dept2_seq TO dept3_seq;

-- 시퀀스 사용하기 (NEXTVAL, CURRVAL)
INSERT INTO dept3 VALUES (
    dept3_seq.NEXTVAL,
    'test',
    'test',
    sysdate
);
--dept3_seq.NEXTVAL : dept3_seq의 다음 값을 지정해서 넣어줌

SELECT
    *
FROM
    dept3;
--dept_no의 값이 다음값으로 계속 증가하는 것을 볼 수 있음
SELECT
    dept3_seq.CURRVAL
FROM

    dual;
--CURRVAL : 현재 seq value확인 가능

--시퀀스 수정 
--START WITH은 수정이 불가능
ALTER SEQUENCE dept3_seq MAXVALUE 9999;
-- 최대값 수정 

ALTER SEQUENCE dept3_seq INCREMENT BY - 3; 
-- 증가 및 감소 값 변경

ALTER SEQUENCE dept3_seq MINVALUE - 50; 
-- 최소값 수정

DROP SEQUENCE dept3_seq;
-- 만들어 놓은 SEQUENCE 삭제

INDEX

PRIMARY KEY, UNIQUE 제약 조건에서 자동으로 생성되고 조회를 빠르게 해주는 HINT 역할
INDEX는 조회를 빠르게 하지만 너무 많은 인덱스를 생성하여 사용하게 되면 성능 부하를 일으킬 수 있음

HINT : 강제로 인덱스를 사용하게 하는 방법

SELECT
    *
FROM
    employees
WHERE
    first_name = 'Nancy';

-- 인덱스 추가 
CREATE INDEX emp_first_name_idx ON
    employees (
        first_name
    );

-- 인덱스
-- 1. 컬럼이 WHERE 또는 조인조건에서 자주 사용되는 경우
-- 2. 열이 광범위한 값을 포함하는 경우
-- 3. 테이블이 대형인 경우
-- 4. 타겟 컬럼이 많은 수의 null값을 포함하는 경우.
-- 5. 테이블이 자주 수정되고, 이미 하나 이상의 인덱스를 가지고 있는 경우에는 권장하지 않음
 


DROP INDEX emp_first_name_idx;

-- 시퀀스와 인덱스를 사용하는 HINT 방법
CREATE SEQUENCE board_seq
START WITH 1 
increment by 1 
nocache nocycle;

CREATE TABLE tbl_board (
    bno    NUMBER(10) PRIMARY KEY,
    writer VARCHAR2(20)
);

INSERT INTO tbl_board VALUES (
    board_seq.NEXTVAL,
    'test'
);

INSERT INTO tbl_board VALUES (
    board_seq.NEXTVAL,
    'admin'
);

INSERT INTO tbl_board VALUES (
    board_seq.NEXTVAL,
    'KIM'
);

SELECT
    *
FROM
    tbl_board;

ALTER INDEX sys_c007028 RENAME TO tbl_board_idx;

SELECT
    *
FROM
    (
        SELECT /*+ INDEX_DESC (tbl_board tbl_board_idx)*/ 
        --오라클의 힌트를 통해 index를 강제로 사용해서 조회하도록 하는 것
            ROWNUM AS rn,
            bno,
            writer
        FROM
            tbl_board
            /*+ INDEX(TABLE_NAME , INDEX_NAME)*/
            -- 지정된 인덱스를 강제로 쓰게끔 지정하고 INDEX DESC, ASC를 추가해서 정렬 가능
    )
WHERE
        rn > 10
    AND rn <= 20;

Practice

Practice

CONSTRAINT 이해

CREATE TABLE members (
    m_name   VARCHAR2(10),
    m_num    NUMBER(2),
    reg_date DATE,
    gender   VARCHAR2(1),
    loca     NUMBER(5),
    CONSTRAINT mem_memnum_pk PRIMARY KEY ( m_name ),
    CONSTRAINT mem_regdate_uk UNIQUE ( reg_date ),
    CONSTRAINT mem_loca_loc_locid_fk FOREIGN KEY ( loca )
        REFERENCES locations ( location_id )
);

ALTER TABLE members MODIFY
    m_name NOT NULL;

ALTER TABLE members MODIFY
    reg_date NOT NULL;

INSERT INTO members VALUES (
    'AAA',
    1,
    '2019-07-01',
    'M',
    1800
);

INSERT INTO members VALUES (
    'BBB',
    2,
    '2018-07-02',
    'F',
    1900
);

INSERT INTO members VALUES (
    'CCC',
    3,
    '2018-07-03',
    'M',
    2000
);

INSERT INTO members VALUES (
    'DDD',
    4,
    sysdate,
    'M',
    2000
);

SELECT
    m.m_name,
    m.m_num,
    l.street_address,
    l.location_id
FROM
         locations l
    INNER JOIN members m ON l.location_id = m.loca
ORDER BY
    m_num ASC;

Let's Just Go

안녕하세요! 공부한 내용을 기록하는 공간입니다.

이전 포스트

DATABASE DDL, DML, DCL

다음 포스트

DATABASE Constraint, View, Sequence

DATABASE

DATABASE

Constraint

VIEW

SEQUENCE

Practice

DATABASE DDL, DML, DCL

DATABASE DCL

0개의 댓글