데이터 정의어

■ 데이터 정의어

1. 스키마의 생성과 제거

• 일반적으로 스키마는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화한 것이다.

• 각 사용자는 권한을 허가받지 않은 한 다른 사용자의 스키마에 속한 데이터를 볼 수 없다.

CREATE SCHEMA MY_DB AUTHORIZATION CHOI;

• 스키마 내에 릴레이션을 생성할 수 있다. 기존 스키마를 제거하려면 DROP을 사용한다.

DROP SCHEMA MY_DB CASCADE CONSTRAINTS;

2. 릴레이션의 정의 그리고 제약조건 예시

• 예시로 설명

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

• 2줄: NOT NULL

- NOT NULL을 선언하지 않으면 디폴트로 NULL을 가질 수 있게 된다.

- 한 애트리뷰트에 대해 NULL 값을 허용하지 않으려면 NOT NULL을 명시해야 한다.

- INSERT문에서 NOT NULL인 애트리뷰트에 NULL값을 삽입할 시 제약조건을 위배하므로 연산이 거절된다.

 

• 3줄: UNIQUE

- 동일한 애트리뷰트 값을 갖는 투플이 두 개 이상 존재하지 않도록 보장한다.

- NOT NULL을 명시하지 않았다면 한 개의 투플에서는 이 애트리뷰트에 널값을 가질 수 있다.

- 오라클을 UNIQUE절이 명시된 애트리뷰트에 자동적으로 인덱스를 생성한다.

 

• 4, 7줄: DEFAULT

- 애트리뷰트에 널값 대신에 특정 값을 디폴트 값으로 지정할 수 있다.

- 7줄에서 DEFAULT를 CHECK절 앞에 써주어야 한다(오라클인 경우). 그렇지 않을 경우, ORA-00907: missing right parenthesis 에러가 발생한다.

 

• 6, 7줄: CHECK

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

 

• 8줄: 기본 키 제약조건

- 릴레이션을 생성할 때 기본 키 제약조건을 명시할 수 있다.

- 기본 키는 앤티티 무결성 제약조건에 의해 널값을 갖지 않아야 한다.

 

• 9, 10줄: 참조 무결성 제약조건

- 참조되는 애트리뷰트는 참조되는 릴레이션에서 동일한 데이터 타입을 가지면서 UNIQUE 또는 기본 키로 정의되어 있어야 한다.

• 10줄: ON DELETE

- 참조되는 릴레이션에서 투플이 삭제될 때, 참조하는 릴레이션에서 어떻게 동작할 것인가를 명시한다.

※ 종류

① ON DELETE NO ACTION: 참조되는 릴레이션에서 어떤 투플을 삭제하려는데, 이 투플의 기본 키 값을 참조하고 있는 투플이 존재하면 삭제 연산을 거절한다.

② ON DELETE CASCADE: 참조되는 릴레이션에서 어떤 투플을 삭제되면 이 투플의 기본 키 값을 참조하고 있는 모든 투플이 연쇄적으로 삭제된다.

③ ON DELETE SET NULL: 참조하는 모든 투플들을 NULL값을 갖는다.

④ ON DELETE SET DEFAULT: 참조하는 모든 투플들은 DEFAULT값을 갖는다.

- 오라클에서 ON UPDATE에 대해서는 NO ACTION만 명시할 수 있다.

 

 제약조건은 테이블 생성할 때 뿐만 아니라 생성 이후에도 추가할 수 있다.

• 제약조건 추가

CREATE TABLE TB_DEPARTMENT (
  DEPARTMENT_NO   VARCHAR2(10) NOT NULL,
  DEPARTMENT_NAME VARCHAR2(40) NOT NULL,
  CATEGORY        VARCHAR2(40),
  OPEN_YN         CHAR(1),
  CAPACITY        NUMBER
);

COMMENT ON COLUMN TB_DEPARTMENT.DEPARTMENT_NO  IS '학과 번호';
COMMENT ON COLUMN TB_DEPARTMENT.DEPARTMENT_NAME  IS '학과 이름';
COMMENT ON COLUMN TB_DEPARTMENT.CATEGORY  IS '계열';
COMMENT ON COLUMN TB_DEPARTMENT.OPEN_YN  IS '개설 여부';
COMMENT ON COLUMN TB_DEPARTMENT.CAPACITY  IS '정원';

ALTER TABLE TB_DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPARTMENT_NO);

- 위와 같이 데이블 생성 후 제약조건(기본 키)을 추가할 수 있다.

CREATE TABLE TB_CLASS (
  CLASS_NO              VARCHAR2(10) NOT NULL,
  DEPARTMENT_NO         VARCHAR2(20) NOT NULL,
  PREATTENDING_CLASS_NO VARCHAR2(20),
  CLASS_NAME            VARCHAR2(60) NOT NULL,
  CLASS_TYPE            VARCHAR2(20)
);
COMMENT ON COLUMN TB_CLASS.CLASS_NO  IS '과목 번호';
COMMENT ON COLUMN TB_CLASS.DEPARTMENT_NO  IS '학과 번호';
COMMENT ON COLUMN TB_CLASS.PREATTENDING_CLASS_NO  IS '선수 과목 번호';
COMMENT ON COLUMN TB_CLASS.CLASS_NAME  IS '과목 이름';
COMMENT ON COLUMN TB_CLASS.CLASS_TYPE  IS '과목 구분';

ALTER TABLE TB_CLASS  ADD CONSTRAINT PK_CLASS PRIMARY KEY (CLASS_NO);
ALTER TABLE TB_CLASS  ADD CONSTRAINT FK_CLASS_01 FOREIGN KEY (PREATTENDING_CLASS_NO) REFERENCES TB_CLASS (CLASS_NO) ON DELETE SET NULL;
ALTER TABLE TB_CLASS  ADD CONSTRAINT FK_CLASS_02 FOREIGN KEY (DEPARTMENT_NO) REFERENCES TB_DEPARTMENT (DEPARTMENT_NO);

- 위와 같이 테이블 생성 후 제약조건(외래 키)을 추가할 수 있다.

• 제약조건 삭제

ALTER TABLE TB_CLASS DROP CONSTRAINT FK_CLASS_01