무결성 제약조건 이어서, 인덱스, 뷰, 사용자 권한 제어 21. 03. 12.

참조 무결성 제약조건

- 한 테이블의 칼럼 값이 자신 · 다른 테이블의 칼럼 값 중에 하나와 일치시키기  위한 제약조건

자식 테이블 : 다른 테이블의 칼럼 값을 참조하는 테이블
부모 테이블 : 다른 테이블에 의해 참조되는 테이블 (부모- 교수 테이블, 자식- 학생 테이블)
외래 키 : 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼 (자식 테이블의 DEPTNO)
참조 키 : 자식 테이블에서 참조하는 부모 테이블의 칼럼

 

 

 

CHECK 무결성 제약조건

- 칼럼에서 허용 가능한 데이터의 범위나 조건을 지정

- 데이터 입력이나 수정 시 실수로 부정확한 값을 입력 예방

- 하나의 컬럼에 여러 개의 CHECK 무결성 제약조건을 지정 가능

 

 

 

무결성 제약조건 생성 방법

- 테이블 생성과 동시에 정의

- 테이블을 생성한 후에 추가, 삭제할 수 있음

- 제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동 생성

- 칼럼 레벨 : 칼럼 정의 시 해당 칼럼별로 지정

- 테이블 레벨 : 제약조건이 하나 이상의 칼럼을 참조, 지정

 

 

칼럼 레벨에서 제약조건 정의

column_constraint

 

NOT NULL만 빠짐.

 

무결성 제약조건 생성문에서의 키워드

 

-- 강좌 테이블 인스턴스

-- PK 제약조건명 PK_SUBJECT (인덱스 이름)
-- SUBNAME의 NOT NULL 제약조건명 NN_SUBJECT_NAME
-- TERM의 제약조건명 CK_SUBJECT_TERM 1, 2의 값이 와야 한다.

CREATE TABLE SUBJECT (
    SUBNO NUMBER CONSTRAINT PK_SUBJECT PRIMARY KEY
    , SUBNAME VARCHAR2(50) CONSTRAINT NN_SUBJECT_NAME NOT NULL
    , TERM VARCHAR2(1) CONSTRAINT CK_SUBJECT_TERM CHECK (TERM IN (1,2))
    , TYPE VARCHAR2(6)
);

 

강좌 - 학생 : 다대다 관계

 

CREATE TABLE SUGANG (
    -- 외래키를 걸면 데이터 타입 안 써도 됨. 참조해서 따라가는 것.
    STUDNO CONSTRAINT FK_SUGANG_STUDENT_STUDNO REFERENCES STUDENT(STUDNO) 
    , SUBNO CONSTRAINT FK_SUGANG_SUBJECT_SUBNO REFERENCES SUBJECT(SUBNO)
    , REGDATE DATE DEFAULT SYSDATE
    , RESULT NUMBER(3) -- 평가 결과니깐 0하면 안 됨.
    , CONSTRAINT PK_SUGANG PRIMARY KEY(STUDNO, SUBNO) -- 열에 하나씩 따로 못 검
);

 

 

기존 테이블에 무결성 제약조건 추가

- NULL을 제외한 무결성 제약조건 추가

 

- NULL 무결성 제약 조건 추가

 

-- 학생테이블 인스턴스를 참조하여 studno에 기본키, idnum에 고유키,
-- name에 NOT NULL 무결성 제약조건을 추가하여라 (STUDNO은 위에서 걸었음)

ALTER TABLE STUDENT ADD CONSTRAINT UK_STUDENT_IDNUM UNIQUE(IDNUM);
ALTER TABLE STUDENT MODIFY (NAME CONSTRAINT NN_STUDENT_NAME NOT NULL);

 

-- 부서 테이블 인스턴스를 참조하여 DEPTNO에 기본 키, DNAME에 NOT NUL 무결성 제약조건을 추가하여라.

ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPTNO PRIMARY KEY(DEPTNO);

ALTER TABLE DEPARTMENT MODIFY(DNAME CONSTRAINT NN_DNAME NOT NULL);

 

 

-- 교수 테이블 인스턴스를 참조하여 PROFNO에 기본 키, NAME에 NOT NULL, DEPTNO에 참조 무결성 제약조건을 추가하여라.

ALTER TABLE PROFESSOR ADD CONSTRAINT PK_PROFESSOR PRIMARY KEY(PROFNO);
ALTER TABLE PROFESSOR MODIFY(NAME CONSTRAINT NN_PROFESSOR NOT NULL);
ALTER TABLE PROFESSOR
-- ADD CONSTRAINT FK_PROFESSOR_DEPTNO FOREINGN KEY(DEPTNO) REFERENCES PROFESSOR(DEPTNO);

-- 학과 테이블에 학과 기본키 추가 PK_DEPARTMENT
ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY(DEPTNO);

-- 학과 테이블에 COLLEGE, DEPTNO 사이의 참조키 추가 FK_DEPARTMENT_DEPTNO_COLLEGE
ALTER TABLE DEPARTMENT MODIFY(COLLEGE CONSTRAINT FK_DEPARTMENT_DEPTNO_COLLEGE
REFERENCES DEPARTMENT);

-- 학생 테이블에 학과 이름 NN 추가 NN_DEPARTMENT_DNAME
ALTER TABLE DEPARTMENT MODIFY(DNAME CONSTRAINT NN_DEPARTMENT_DNAME NOT NULL);

-- 학생 테이블에 교수 참조키 추가 FK_STUDENT_PROFNO
ALTER TABLE STUDENT MODIFY(PROFNO CONSTRAINT FK_STUDENT_PROFESSOR_PROFNO
REFERENCES PROFESSOR(PROFNO));

-- 학생 테이블에 학과 참조키 추가 FK_STUDENT_DEPTNO
ALTER TABLE STUDENT ADD CONSTRAINT FK_STUDENT_DEPARTMENT_DEPTNO
FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO);

 

 

 

즉시 제약조건 위배

INSERT INTO SUBJECT VALUES (1, 'SQL', 1, '필수');
INSERT INTO SUBJECT VALUES (1, 'JAVA', 1, '필수'); -- 고유키 겹침.
INSERT INTO SUBJECT VALUES (2, '', 1, '필수'); -- NOT NULL 위배
INSERT INTO SUBJECT VALUES (3, 'JSP', 3, '필수'); -- CHECK 위배
INSERT INTO SUBJECT VALUES (5, '데이터베이스', 2, '필수');

 

무경성 제약조건 삭제

ALTER TABLE SUBJECT DROP CONSTRAINT CK_SUBJECT_TERM;

 

 

무결성 제약조건 활성화 및 비활성화

- 대용량 데이터 초기 입력시

- 무결성 제약조건의 위반 여부를 검사하는 과정으로 인해 처리 시간이 오래 걸리는 경우가 발생
- 이러한 경우, 기존의 무결성 제약조건을 일시적으로 비활성화하여 데이터를 입력한 다음, 비활성화된 무결정 제약조건은 다시 활성화
- ALTER TABLE 명령문에서 ENABLE 또는 DISABLE 절 사용

 

- NOVALIDATE : 기존 데이터에 대해서는 제약조건을 적용하지 않고, 새로 입력되는 데이터나 수정하는 데이터에 대해서만 제약조건을 검사하기 위한 옵션

- PK는 안 됨. FK는 할 수 있음.

예) DISABLE한 후 3학기를 넣고 ENABLE NOVALIDATE를 하면 이미 입력되어 있는 3학기는 괜찮다고 통과 됨.

 

ALTER TABLE SUBJECT ADD CONSTRAINT CK_SUBJECT_TERM CHECK (TERM IN (1,2));
SELECT * FROM SUBJECT; -- 이미 3학기 들어가 있음.

ALTER TABLE SUBJECT ENABLE NOVALIDATE CONSTRAINT CK_SUBJECT_TERM;
INSERT INTO SUBJECT VALUES (6, '데이터구조', 3, '필수'); -- ENABLE이라 삽입 안 됨.

 

 

무결성 제약조건 조회

SELECT * FROM USER_CONS_COLUMNS;

 

 

인덱스 관리

인덱스란?

- SQL 명령문의 처리 속도를 향상시키기 위해 칼럼에 대해 생성하는 객체 (빠른 탐색)

- 포인트를 이용하여 테이블에 저장된 데이터를 랜덤 액세스하기 위한 목적으로 사용

 

 

인덱스의 종류

- 고유, 비고유, 단일, 결합, DESCENDING INDEX, 함수 기반 인덱스

 

인덱스의 효율적인 사용 방법

-  WHERE 절이나 조인 조건절에서 자주 사용되는 칼럼

- 전체 데이터중에서 10~15% 이내의 데이터를 검색하는 경우

- 두 개 이상의 칼럼이 WHERE 절이나 조인 조건에서 자주 사용되는 경우

- 테이블에 저장된 데이터의 변경이 드문 경우

- 열에 널 값이 많이 포함된 경우, 열에 광범위한 값이 포함된 경우

 

고유 인덱스 (unique index)

- 유일한 값을 가지는 칼럼에 대해 생성하는 인덱스로 모든 인덱스 키는 테이블의 하나의 행과 연결

 

-- 부서테이블에서 name 칼럼을 고유 인덱스로 생성하여라. 단, 고유인덱스의 이름을 idx_dept_name으로 정의.

CREATE UNIQUE INDEX IDX_DEPARTMENT_DNAME ON DEPARTMENT(DNAME);

 

PK가 생성되면 인덱스는 자동 생성 됨.

 

-- SQL과목을 수강한 학생의 학번, 학생 이름, 학과 이름, 담당교수 이름을 조회

SELECT STUDNO, STUDENT.NAME, DNAME, PROFESSOR.NAME
FROM SUBJECT
JOIN SUGANG USING(SUBNO)
JOIN STUDENT USING(STUDNO)
JOIN DEPARTMENT USING(DEPTNO)
JOIN PROFESSOR USING(PROFNO)
WHERE SUBNAME = 'SQL';

 

 

 

비고유 인덱스

- 중복된 값을 가지는 칼럼에 대해 생성하는 인덱스로 하나의 인덱스 키는 테이블의 여러 행과 연결될 수 있음.

 

 

단일 인덱스, 결합 인덱스

 

-- 학생테이블의 deptno, grade 칼럼을 결합 인덱스로 생성하여라. 결합 인덱스의 이름은 idx_stud_dno_grade 로 정의.

CREATE INDEX IDX_STUDENT_DEPTNO_GRADE ON STUDENT(DEPTNO, GRADE);

 

※ 탐색할 때 인덱스를 타서 RANGE가 되어야 하는데 FULL 됐을 때, 강제로 인덱스 태우는 법. (힌트 이용)

SELECT /*+ INDEX(STUDENT IDX_STUDENT_DEPTNO_GRADE) */ *
FROM STUDENT WHERE DEPTNO = 101 AND GRADE = 1;

 

 

 

 

인덱스 재구성

- 인덱스를 정의한 테이블의 칼럼 값에 대해 변경 작업이 자주 발생하여, 불필요하게 생성된 인덱스 내부 노드를 정리하는 작업참조 무결성 제약조건

 

 

뷰

 

뷰의 개념

- 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블 (바로가기 아이콘같은 느낌)

장점
- 데이터를 보호하기 위한 보안 (security)
- 사용자 편의성 (flexibility)

 

 

뷰 생성

- CREATE VIEW 명령문 사용

- 뷰 생성시 칼럼 이름을 명시하지 않으면 기본 테이블의 칼럼 이름을 상속

- 함수나 표현식에 의해 정의된 칼럼은 별도로 이름을 명시(별명 사용)

 

 

-- 학생테이블에서 101번학과 학생들의 학번, 이름, 학과번호로 정의되는 단순뷰를 생성하여라.

CREATE VIEW VIEW_STUDENT101 AS
SELECT STUDNO, NAME, DEPTNO
FROM STUDENT
WHERE DEPTNO = 101;

 

INSERT INTO VIEW_STUDENT101 VALUES(20303, '홍길동', 101);
UPDATE VIEW_STUDENT101 SET NAME = '고길동' WHERE NAME = '홍길동';
SELECT * FROM STUDENT;
DELETE VIEW_STUDENT101 WHERE NAME = '고길동';

 

-- 학생테이블과 부서테이블을 조인하여 102번학과 학생들의학번, 이름, 학년, 학과이름으로 정의되는 복합뷰를 생성하여라.

CREATE OR REPLACE VIEW VIEW_STUDENT102 AS
SELECT STUDNO, NAME, GRADE, DNAME
FROM STUDENT
NATURAL JOIN DEPARTMENT;

 

-- 교수테이블에서 학과별 평균급여와 총계로 정의되는 뷰를 생성하여라.

CREATE OR REPLACE VIEW VIEW_PROF AS
SELECT DEPTNO, AVG(SAL) AVG_SAL, SUM(SAL) SUM_SAL -- 컬럼 별칭 쓰기.
FROM PROFESSOR
GROUP BY DEPTNO;

- 함수를 사용했기 때문에 컬럼 별명을 사용하지 않으면 오류 발생.

 

 

인라인 뷰(inline view)

- FROM 절에서 참조하는 테이블의 크기가 클 경우, 필요한 행과 컬럼만으로 구성된 집합을 재정의하여 질의문을 효율적 구성

- FROM 절에서 서브쿼리를 사용하여 생성한 임시 뷰

- SQL 명령문이 실행되는 동안만 임시적으로 정의

 

많이 씀.

 

-- 인라인뷰를 사용하여 학과별로 학생들의 평균키와 평균 몸무게, 학과이름을 출력하여라.

SELECT DNAME, AVG_H, AVG_W
FROM(
	SELECT DEPTNO, AVG(HEIGHT) AVG_H, AVG(WEIGHT) AVG_W
    FROM STUDENT
    GROUP BY DEPTNO
)
JOIN DEPARTMENT
USING(DEPTNO);

 

- 그 외 방법들

SELECT DNAME, AVG_H, AVG_W
FROM(
    SELECT DEPTNO, AVG(HEIGHT) AVG_H, AVG(WEIGHT) AVG_W
    FROM STUDENT
    GROUP BY DEPTNO
) A
JOIN DEPARTMENT D
ON(A.DEPTNO = D.DEPTNO);

 

WITH TMP AS (
    SELECT DEPTNO, AVG(HEIGHT) AVG_H, AVG(WEIGHT) AVG_W
    FROM STUDENT
    GROUP BY DEPTNO
)
SELECT DNAME, AVG_H, AVG_W
FROM TMP
JOIN DEPARTMENT D
ON(TMP.DEPTNO = D.DEPTNO);

 

-- 학과별로 학생들의 평균키와 평균 몸무게, 학과이름을 출력하여라. (서브쿼리로)

SELECT
    (SELECT DNAME FROM DPARTMENT D WHERE D.DEPTNO = S.DEPTNO) DNAME
    , AVG(HEIGHT) AVG_H, AVG(WEIGHT) AVG_W
FROM STUDENT S
GROUP BY DEPTNO;

 

-- 가장 간단한 방법

SELECT DNAME, AVG(HEIGHT) AVG_H, AVG(WEIGHT) AVG_W
FROM STUDENT
JOIN DEPARTMENT
USING(DEPTNO)
GROUP BY DNAME;

 

 

뷰의 변경

- 기존 뷰에 대한 정의를 삭제한 후 재생성

- CREATE 명령문에서 OR REPLACE 옵션을 이용하여 재생성

  (OR REPLACE 옵션은 기존에 생성된 뷰가 있을 경우에는 기존 뷰를 무시하고 재생성하는 옵션)

- 변경 결과는 USER_VIEWS 데이터 딕셔너리에 저장

 

 

뷰의 삭제

- 뷰는 실체가 없는 가상 테이블이므로 뷰의 삭제는 USER_VIEWS 데이터 딕셔너리에 ㅁ저장된 뷰의 정의를 삭제

- 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향 없음.

 

 

사용자 권한 제어

객체 권한

- 테이블, 뷰, 시퀀스, 함수 등과 같은 객체를 조작할 수 있는 권한

- 객체의 종류에 따라 서로 다른 유형의 객체 권한

- 테이블의 칼럼별로 INSERT, UPDATE, REFERENCES 등의 권한을 상세 부여

 

 

동의어 (synonym)

- 객체를 조회할 때마다 객체의 소유자를 일일이 지정하는 방법은 매우 번거로움.

- 동의어는 하나의 객체에 대해 다른 이름을 정의하는 방법

동의어와 별명(Alias) 차이점
- 동의어는 데이터베이스 전체에서 사용
- 별명은 해당 SQL 명령문에서만 사용
예) DUAL은 원래 SYS.DUAL