[오라클 DB] 데이터 정의어(DDL)

데이터 정의어(DDL) 

 

릴레이션, 애트리뷰트, 뷰, 인덱스를 생성 및 제거하는 역할을 수행한다.

설계 기반의 구축을 담당한다고 생각하면 된다.

 

 

데이터 정의어 종류

 

CREATE	DOMAIN	도메인을 생성
	TABLE	테이블을 생성
	VIEW	뷰를 생성
	INDEX	인덱스를 생성
ALTER	TABLE	테이블 구조를 변경
DROP	DOMAIN	도메인을 제거
	TABLE	테이블을 제거
	VIEW	뷰를 제거
	INDEX	인덱스를 제거

 

 

릴레이션 제거

 

예) DEPARTMENT 릴레이션을 제거하는 경우

 

DROP TABLE DEPARTMENT;

 

테이블 수정

 

예) EMPLOYEE 릴레이션에 "PHONE" 애트리뷰트를 추가함

 

ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

 

 

인덱스 생성

 

SQL의 표준은 아니지만, 대부분의 상용 관계 DBMS에서 지원한다.

인덱스는 검색 기능을 향상시키는 장점을 지니고 있기에 인덱스 튜닝이 중요하다고 여겨지지만, 추가적인 저장공간이 필요하며 갱신 연산 속도를 저하시키는 단점을 지니고 있다.

 

예) EMPLOYEE 릴레이션의 DNO 애트리뷰트에 인덱스를 생성함

 

CREATE INDEX EMPDNO_IDX ON EMPLOYEE(DNO);

 

 

---

 

제약조건 ( Constraint )

데이터의 무결성을 지키기 위해 제한하는 조건을 말한다.

 

다음 테이블 생성 예시를 보며 설명하겠다.

 

 

EMPLOYEE 테이블 생성 

CREATE TABLE EMPLOYEE(
       (EMPNO      NUMBER       NOT NULL,                                                                                                     
       EMPNAME  CHAR(10)      UNIQUE,                                                                                                    
       TITLE          CHAR(10)       DEFAULT ‘사원’,
       MANAGER  NUMBER,
       SALARY      NUMBER,      CHECK(SALARY < 6000000),
       DNO             NUMBER,      CHECK(DNO IN(1,2,3,4,5,6)) DEFAULT 1,
       PRIMARY KEY (EMPNO),
       FOREIGN KEY(MANAGER) REFERENCES  EMPLOYEE(EMPNO),
       FOREIGN KEY(DNO) REFERENCES  DEPARTMENT(DEPTNO) ON DELETE CASCADE);
);

 

 

애트리뷰트 제약조건

 

 

NOT NULL

 

널 값을 허용하지 않는다는 의미

 

EMPNO      NUMBER       NOT NULL

 

EMPNO 애트리뷰트는 정수형 타입이며, NULL 값을 허용하지 않겠다는 제약조건을 명시한 부분이다. (뒤에서 설명하겠지만, 해당 애트리뷰트는 기본 키 애트리뷰트로 선언되기에 엔터티 무결성 제약조건 위배를 방지하기 위한 제약조건이다.)

 

 

 

 

UNIQUE

 

동일한 애트리뷰트가 동일한 값을 가지지 않도록 보장한다.

널 값을 하나는 가질 수 있으며, 오라클에서는 해당 애트리뷰트에 자동적으로 인덱스를 생성한다.

 

EMPNAME  CHAR(10)      UNIQUE

 

EMPNAME 애트리뷰트는 10바이트 길이의 문자열이 저장될 것임을 선언하였으며, 해당 값은 고유한 값으로 중복되지 않아야 함을 명시한 부분이다.

 

 

 

 

DEFAULT

 

레코드 삽입 시에 값을 지정하지 않으면 해당 값을 저장한다는 의미이다.

 

TITLE          CHAR(10)       DEFAULT ‘사원’

 

TITLE 애트리뷰트는 10바이트 길이의 문자열이 저장될 것임을 선언하였으며, 레코드에 삽입 시 값을 지정하지 않으면 DEFAULT 값인 '사원' 값이 저장될 것임을 명시한 부분이다.

 

 

 

CHECK

 

애트리뷰트가 가질 수 있는 값의 범위를 지정한다.

 

SALARY      NUMBER,      CHECK(SALARY < 6000000),
 DNO           NUMBER,      CHECK(DNO IN(1,2,3,4,5,6))  DEFAULT 1

 

SALARY 애트리뷰트는 정수형 타입이며, 값은 6,000,000 미만의 정수 값만이 들어올 수 있음을 명시하였다.

(7,000,000이 값으로 삽입된다면 오류를 뱉게 된다.)

DNO 애트리뷰트는 정수형 타입이며, 1 ~ 6의 정수만이 들어올 수 있고, 레코드에 삽입 시 값을 지정해주지 않는다면 DEFAULT 값인 1이 삽입될 것임을 명시하였다.

 

 

또한 애트리뷰트 간의 관계를 지정할 수도 있다.

 

CREATE TABLE EMPLOYEE (
       ID NUMBER,
       NAME CHAR(10),
       SALARY NUMBER,
       MANAGER_SALARY NUMBER, CHECK(MANAGER_SALARY > SALARY)
);

 

MANAGER_SALARY 애트리뷰트는 정수형 타입이며, MANAGER_SALARY 애트리뷰트에 삽입된 값은 SALARY 애트리뷰트에 삽입된 값보다 항상 커야 함을 명시하였다.

 

 

 

 

PRIMARY KEY

 

기본 키를 지정한다.

기본 키는 중복된 값이 존재해서는 안되며, 엔티티 무결성 제약조건에 의해 널 값을 가지지 않아야 한다.

또한 기본 키에는 자동적으로 인덱스가 생성된다.

 

PRIMARY KEY (EMPNO)

 

EMPNO 애트리뷰트를 기본 키로 지정하였으며, 해당 키는 중복된 값이 존재해서는 안되며 널 값을 가지지 않아야 한다.(NOT NULL)

 

 

 

 

FOREIGN KEY

 

참조 무결성 제약조건을 정의한다.

참조되는 애트리뷰트는 UNIQUE 또는 기본 키로 정의되어 있어야 한다.

오라클에서 지원하는 참조 무결성 제약조건 관련 동작들은 다음과 같다.

 

ON DELETE NO ACTION : RESTRICT와 같은 의미임 (삭제 연산 거절)

ON DELETE CASCADE (연쇄 삭제)

ON DELETE SET NULL (NULL 값 부여)

ON DELETE SET DEFAULT (디폴트 값 부여)

ON UPDATE NO ACTION : 오라클은 UPDATE에 대해서는 NO ACTION만을 지원함

 

FOREIGN KEY(MANAGER) REFERENCES  EMPLOYEE(EMPNO),
FOREIGN KEY(DNO) REFERENCES  DEPARTMENT(DEPTNO) ON DELETE CASCADE

 

MANAGER 애트리뷰트는 EMPLOYEE 릴레이션의 EMPNO 애트리뷰트를 참조한다.

DNO 애트리뷰트는 DEPARTMENT 릴레이션의 DEPTNO 애트리뷰트를 참조하며 연쇄 삭제를 적용하여 참조되는 값의 튜플이 삭제되었을 경우 참조하는 값의 튜플들 또한 자동적으로 삭제되게끔 한다.

 

 

 

 

무결성 제약조건 추가 및 삭제

 

ALTER TABLE 문을 사용하여 제약조건을 추가/삭제할 수 있다.

 

예) STNO을 기본 키로 추가 정의함

 

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY(STNO);

 

예) 제약조건 STUDENT_PK를 삭제함

 

ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;

 

 

 

 

애트리뷰트 추가 및 삭제

 

예) EMPLOYEE에 'PHONE CHAR(10)' 추가

 

ALTER TABLE EMPLOYEE ADD PHONE CHAR(10);

 

 

예) EMPLOYEE에 'PHONE CHAR(10)' 삭제

 

ALTER TABLE EMPLOYEE DROP COLUMN PHONE;