데이터베이스 6일차 - 제약조건(2), 테이블 수정, 뷰

제약조건

기본키(Primary Key) 설정

 - 테이블 생성시 PK를 정의한다.

 - PK(기본키)는 유일성, 최소성을 만족해야 한다.

 

외래키(Foreign Key) 설정

 - FK가 정의된 테이블이 자식 테이블, 참조되는 테이블을 부모 테이블이라고 한다.

 - 자식 테이블은 부모 테이블의 참조되는 컬럼에 존재하는 값이나 NULL만 입력가능하다.

 

 ON DELETE CASCADE : 참조되는 부모 테이블의 행에 대한 DELETE를 허용한다. 즉, 부모 테이블의 행이 지워지면

 자식  테이블의 행도 같이 지워진다.

 ON DELETE SET NULL : 마찬가지로 DELETE를 허용하며, 부모 테이블의 행이 지워지면 자식 테이블의 행은

 NULL값으로 설정된다.

 * 참조되는 컬럼은 PK이거나 UK만 가능하다.

 

제약조건 조회

SELECT *
FROM ALL_CONSTRAINTS
WHERE table_name = '테이블명';

위의 형태로 테이블명을 입력하면 해당 테이블의 모든 제약조건을 조회할 수 있다.

 

제약조건 추가

위의 형태로 제약조건을 추가하며, NOT NULL은 아래와 같이 MODIFY를 이용하여 추가한다.

 

제약조건 삭제

위의 형태로 제약조건을 삭제하며,  CASCADE를 사용하면 이를 참조하는 FK도 삭제된다.

 

테이블 열 추가/수정/삭제

 - ADD는 컬럼 추가, MODIFY는 컬럼 수정

-- 열 추가
ALTER TABLE E_EMP
ADD (GENDER VARCHAR2(1));

ALTER TABLE E_EMP
ADD (커미션 NUBER DEFAULT 0 NOT NULL);

-- 한 번에 두개의 열 추가
ALTER TABLE E_EMP
ADD (날짜 DATE DEFAULT SYSDATE,
     제작자 VARCHAR2(100) DEFAULT USER);

-- 열의 수정
ALTER TABLE E_EMP
MODIFY (제작자 VARCHAR2(200)); -- (100) => (200)

ALTER TABLE E_EMP
MODIFY (GENDER NUMBER);

-- 열의 삭제
ALTER TABLE E_EMP
DROP COLUMN 제작자;

-- 여러 개 열 삭제
ALTER TABLE E_EMP
DROP (GENDER, 커미션, 커미션2);

 

테이블 이름 수정, CREATE TABLE AS

 CREATE TABLE AS

  : 서브 쿼리의 결과를 테이블로 저장하는 방법이다. 주로 테스트용 테이블, 임시테이블, 분석테이블을 생성하는데 이용

 테이블 이름 수정

-- 테이블의 열 및 이름 수정
ALTER TABLE E_EMP
RENAME COLUMN department_id TO 부서번호;

ALTER TABLE E_EMP
RENAME COLUMN employee_id TO 직원번호;	 -- 열 이름 수정

RENAME E_EMP TO 직원테이블;       		-- 테이블 이름 수정

CREATE TABLE stmans(id,job,sal)			 -- 열이름 지정하여 테이블 생성
AS
SELECT employee_id, job_id, salary FROM employees
WHERE job_id = 'ST_MAN';

 

뷰(VIEW)

 : 테이블에서 사용자가 필요로 하는 부분만 선택하여 만들어 놓은 데이터의 집합. 즉, 가상의 테이블

 

* 뷰를 사용하는 이유는 사용자의 편의와 데이터베이스의 보안 때문이다. 뷰를 사용하면 원본 테이블에 직접 접근하지 않아도 사용자에게 적절한 데이터를 제공할 수 있어,데이터베이스의 데이터를 안전하게 유지할 수 있다. 또, 복잡한 SQL문을 매번 작성하지 않아도 된다.

 

뷰의 특징

 - 뷰는 기본테이블로부터 유도된 테이블이기 때문에 기본테이블과 같은 형태의 구조이며, 조작도 거의 동일하다.

 - 뷰는 가상테이블이기 때문에 물리적으로 구현되어 있지 않다.

 - 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 명령문이 간단해진다.

 - 뷰를 통해서만 데이터에 접근하게 하면 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적인 기법으로 사용할       수 있다.

복잡한 뷰

-- 그룹함수를 사용한 뷰는 DML 사용 불가(수정, 삭제, 입력)

DROP VIEW 부서별_직원_보고서;

CREATE OR REPLACE VIEW 부서별_직원_보고서 -- OR REPLACE VIEW 추가
AS
SELECT department_id 부서번호, COUNT(employee_id) 직원수, MAX(salary) 최고급여,
       MIN(salary) 최저급여, ROUND(AVG(salary),0) 평균급여
FROM employees
GROUP BY department_id;

SELECT * FROM 부서별_직원_보고서;

--DML 사용 시 에러
INSERT INTO 부서별_직원_보고서 VALUES(120,6,10000,5000);

읽기 전용 뷰(WITH READ ONLY)

CREATE OR REPLACE VIEW EMP_V_READ
AS
SELECT employee_id 직원번호, first_name 이름, last_name 성,
email 이메일, hire_date 고용일자, job_id 직종
FROM employees
WHERE department_id  = 90
WITH READ ONLY;

SELECT * FROM EMP_V_READ;

-- DML 사용 불가
DELETE FROM emp_v_read;

체크 옵션 뷰(WITH CHECK OPTION) : 특정 조건만 DML 적용

CREATE OR REPLACE VIEW EMP_체크옵션
AS
SELECT employee_id 직원번호, first_name 이름, last_name 성,
email 이메일, hire_date 고용일자, job_id 직종, department_id 부서번호
FROM employees
WHERE department_id  = 90 -- 부서번호가 90번 일 때만 DML 사용가능
WITH CHECK OPTION;

 

인덱스(INDEX)

: 인덱스는 색인으로 RDBMS에서 검색속도를 눞이기 위해 사용하는 하나의 기술이다.

 - 자동 인덱스 : PK(기본키) 또는 UNIQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.

 - 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스

 

 인덱스 확인하기

SELECT TABLE_NAME       테이블명,
       INDEX_NAME       인덱스이름,
       COLUMN_NAME      컬럼이름
    FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';

 

인덱스 만들기

CREATE INDEX members_last_name_i
ON members(last_name);

 

인덱스 삭제 : 인덱스는 테이블이 DROP/삭제 시 같이 삭제됨

DROP INDEX MEMBERS_first_name_i;

 

멀티 인덱스