SQL - 제약조건, 뷰, 인덱스, 권한

@markdown

## 무결성 제약 조건의 개념과 종류

____

- `NOT NULL` : NULL을 허용 X.

- `UNIQUE` : 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함.

- `PRIMARY KEY` : NULL을 허용 X.

- `FOREIGN KEY` : 참조되는 테이블의 칼럼의 값이 존재하면 허용함

- `CHECK` : 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만을 허용함

<pre><code class="sql" style="font-size:14px">USER_CONSTRAINTS의 내용을 출력

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME

FROM USER_CONSTRAINTS

</code></pre>

### CHECK 조건

____

- 제한된 데이터만 받을 수 있도록 조건 지정

<pre><code class="sql" style="font-size:14px">

컬럼이름 자료형 constraint 조건이름 check(컬럼 in(값1, 값2 ...))

gender char(1) constraint student_gender_chk check(gender in('M', 'F'))

</code></pre>

- 내가 만든 (USER) 제약 조건(CONSTRAINTS)의 정보를 조회 할 수 있다.

<pre><code class="sql" style="font-size:14px">제약조건 확인

desc user_constraints

</code></pre>

- 상품코드와 시퀀스 번호를 조합해 Primary Key로 지정한다.

<pre><code class="sql" style="font-size:14px">create table sales(

product_code number(2),

seq number(2),

sales_date date,

memo varchar2(2000),

constraint sales_pk primary key(product_code, seq)

)

</code></pre>

- 테이블 복사 시 제약조건까지 복사되진 않는다.

<pre><code class="sql" style="font-size:14px">create table emp_copy

as select * from emp;

</code></pre>

- 오라클에서는 제약 조건을 비활성화시킴으로서 제약조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법을 제공해준다.

- 부모테이블과 자식테이블 사이에 제약조건으로 묶여있는 경우, 제약조건을 삭제하게 되면 참조 무결성에 위배된다.

- 참조 무결성을 지키기 위해 잠시 제약조건을 비활성화 할 수 있다.

- 부모테이블의 데이터 삭제나 삽입 시 제약조건에 걸린 경우 해결하기 위해 활성/비활성 기능을 통해 제약조건을 다룬다.

<pre><code class="sql" style="font-size:14px">alter table 테이블이름

enable constraint 제약조건 이름

disable constraint 제약조건 이름

</code></pre>

### CASCADE 옵션

____

- 부모 테이블과 자식 테이블 간의 참조 조건이 설정 되어 있을 때 부모 테이블의 제약 조건을 비활성화하면 이를 참조하고 있는 자식 테이블의 제약 조건까지 같이 비활성화시켜 주는 옵션

#### 제약조건 cascade

<pre><code class="sql" style="font-size:14px">drop primary key cascade

</code></pre>

- 부모 테이블의 기본키 제약조건을 삭제하게 되면 연관되어 있는 자식 테이블의 제약조건까지 연쇄적으로 삭제가 된다.

#### 데이터 cascade

- 외부키 제약조건에 cascade 옵션을 주고 테이블을 생성하게 되면, 부모 테이블에서 제거되는 데이터가 자식 테이블까지 삭제되는 연쇄 삭제가 발생한다.

## 뷰(View)

____

- 물리적인 데이터 테이블에서 가져와 생성한 가상의 테이블

- select 문장의 결과가 저장된다.

- 복잡한 sql을 미리 뷰로 정의하고 재사용 할 수 있다.

- 보안상 이유로 칼럼 조회를 제한하기 위해 뷰를 사용함

<pre><code class="sql" style="font-size:14px">CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name

[(alias, alias, alias, ...)]

AS subquery

[WITH CHECK OPTION]

[WITH READ ONLY];

</code></pre>

- replace 옵션 : 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경할 수 있다.

- force 옵션 : 기본 테이블의 존재 여부에 상관없이 뷰를 생성합니다.(존재여부 체크X)

- noforce : 테이블 존재여부 체크 함

- WITH READ ONLY : 해당 뷰를 통해서는 select만 가능하며 insert/update/delete를 할 수 없다.

- 뷰를 생성할 수 있는 권한 부여

<pre><code class="sql" style="font-size:14px">grant create view to SCOTT

</code></pre>

## 시퀀스

____

- 오라클에서 행을 구분하기 위해서 기본 키를 설정한다. 하지만 기본키는 중복된 값을 가질 수 없으므로 항상 유일한 값을 가져야 합니다.

- 시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성해준다.

- 시퀀스를 기본 키로 사용하게 되면 사용자의 부담을 줄일 수 있다.

- MySql에서는 Auto Increment 기능이 있다.

<pre><code class="sql" style="font-size:14px">CREATE SEQUENCE sequence_name

[START WITH n]

[INCREMENT BY n]

[{MAXVALUE n | NOMAXVALUE}]

[{MINVALUE n | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n | NOCACHE}]

</code></pre>

- 시퀀스의 nextval 실행한 순간 시퀀스 번호는 증가한다.

- 시퀀스 객체에 접근한 것이 성공했기 때문에 쿼리의 성공 실패 여부에 상관없이 증가한다.

<pre><code class="sql" style="font-size:14px">create sequence seq_no --시퀀스 생성

create table seqTest(

  id number,

name varchar2(20)

);

create table seqTest2(

id number,

name varchar2(20)

);

insert into seqTest values(seq_no.nextval,  '첫번째')

insert into seqTest2 values(seq_no.nextval, '두번째')

insert into seqTest values(seq_no.nextval,  '세번째')

insert into seqTest2 values(seq_no.nextval, '네번째')

insert into seqTest999 values(seq_no.nextval, '네번째') -- 실패해도 시퀀스 번호 증가

select * from seqTest

select * from seqTest2

</code></pre>

## 인덱스

____

#### 장점

- 검색 속도가 빠르다.

- 시스템에 걸리는 부하를 줄여 성능을 향상시킨다.

#### 단점

- 인덱스를 위한 저장 공간이 필요하다

- 인덱스 생성에 시간이 걸린다.

- 데이터 변경하는 DML(update, insert, delete) 쿼리가 많이 발생하는 경우 성능이 저하된다.

#### 특징

- Oracle은 Primary Key, Unique를 설정한 칼럼은 자동으로 인덱스 생성해준다.

- 기본적으로 Oracle의 인덱스는 B-트리 구조로 생성된다.

<pre><code class="sql" style="font-size:14px">만들어진 인덱스 확인 하기

select *

from user_ind_colums

where table_name = 'emp'

create table indextest as

select * from emp

create index idx_ename on indextest(ename)

</code></pre>

## 사용자 관리

_____

### 시스템 권한

____

CREATE USER : 새롭게 사용자를 생성하는 권한

DROP USER : 사용자를 삭제하는 권한

DROP ANY TABLE : 임의의 테이블을 삭제할 수 있는 권한

QUERY REWRITE : 함수 기반 인덱스를 생성하는 권한

BACKUP ANY TABLE : 임의의 테이블을 백업할 수 있는 권한

<pre><code class="sql" style="font-size:14px">CREATE USER user_name

IDENTIFIED BY password;

</code></pre>

GRANT CREATE SESSION TO user_name;

CREATE SESSION : 데이터베이스에 접속할 수 있는 권한

CREATE TABLE : 사용자 스키마에서 테이블을 생성할 수 있는 권한

CREATE VIEW : 사용자 스키마에서 뷰를 생성할 수 있는 권한

CREATE SEQUENCE : 사용자 스키마에서 시퀀스를 생성할 수 있는 권한

CREATE PROCEDURE : 사용자 스키마에서 함수를 생성할 수 있는 권한

### 객체 권한

____

사용자에게 특정 객체를 조작할 수 있는 권한을 부여한다.

`ALTER`, `DELETE`, `EXECUTE`, `INDEX`, `INSERT`, `REFERENCES`, `SELECT`, `UPDATE`