Gom's

공부하는 햇병아리

[Oracle] SQL문법 5. DDL(CREATE)

작성일     업데이트:

카테고리:

태그:

DDL (Data Definition Language)

데이터 정의 언어로 객체(Object)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다

  • 오라클 객체 종류
    • 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE), 인덱스(INDEX), 패키지(PACKAGE), 프로시저(PROCEDUAL), 함수(FUNCTION), 트리거(TRIGGER), 동의어(SYNONYM), 사용자(USER)


CREATE

테이블, 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문이다

  • 오라클 데이터형

    데이터형 설명
    CHAR(크기) 고정길이 문자 데이터 (1 < n < 2000 byte)
    VARCHAR2(크기) 가변길이 문자 데이터 (1 < n < 4000 byte)
    NUMBER 숫자 데이터(최대 40자리)
    NUMBER(n,p) n : 전체 자리수 p : 소수점 이하 자리수
    전체 자리수를 초과하는 경우 입력 거부되지만 소수점 이하 자리수가 초과되면 반올림되어 입력된다
    소수점 이하의 값이 없는 경우는 길이를 지정하지 않는 것이 좋다
    DATE 날짜 데이터로 출력이나 입력 형식과 무관하게 YYYY/MM/DD:HH24:MI:SS 형태로 저장된다
    (BC 4712년 1월 1일 ~ AD 4712년 12월 31일)
    LONG 가변 길이 문자형 데이터(최대 2GB), 단 조건 검색할 수 없으며 테이블에는 하나의 LONG 컬럼만 정의할 수 있다
    LOB 2GB까지의 가변길이 바이너리 데이터 저장 가능(이미지, 실행파일 등 저장 가능)
    ROWID ROWID를 저장하기 위한 데이터 타입
    주로 PLSQL 프로그래밍에서 많이 사용되며 각 문자는 62진수로 엔코딩(encoding) 되어있다
    BFILE 외부 파일(바이너리데이터) 저장을 위한 이진 타입, 4G 이내 파일을 저장한다
    TIMESTAMP DATE형의 확장된 형태로 밀리초 까지 표현가능하다
    INTERVAL YEAR [(yr_prec)] TO MONTH 연도와 월 형태로 기간을 표현한다
    yr_prec는 날짜의 연도의 최대 자릿수를 나타내며 그 값은 0~9 범위이고 디폴트 값은 2이다
    INTERVAL DAY TO SECOND 요일, 시, 분, 초 형태로 기간을 표현한다.
    • 테이블 생성 시 위 표를 참고하여 컬럼별 데이터 타입을 지정해주어야 한다
      => 타입을 지정하지 않았을 시 ‘열의 데이터 유형을 지정해 주십시오’ 에러 발생


[ 기본 표현법 ]

CREATE TABLE 테이블명(
    컬럼명 자료형(크기),
    컬럼명 자료형(크기),
    컬럼명 자료형(크기) DEFAULT 기본값,
    …);

-- 사용 예시
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    -- DEFAULT 설정을 먼저 한 후 , NOT NULL등의 제약조건 추가 가능
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);
  • 기본 테이블을 생성하는 구문이다
  • SQL에서는 대소문자를 구분하지 않는다, 따라서 낙타등 표기법이 의미가 없고 언더바로 단어를 구분한다
  • 하나의 계정에서는 동일한 테이블명을 가진 테이블을 중복 생성할 수 없다
  • DEFAULT 설정 : 특정 컬럼에 들어올 값에 대한 기본 설정을 할 수 있다


[ 컬럼 주석 ]

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석 내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
  • 테이블의 컬럼에 주석을 다는 구문
  • 이후 테이블 상 열 -> COMMENTS 부분에 해당 주석 내용이 표시되게 된다


[ 제약 조건(CONSTRAINTS) ]

  • 원하는 데이터 값만 유지하기 위해서 특정 컬럼마다 설정하는 제약조건
  • 제약조건이 부여된 컬럼은 들어온 데이터에 문제가 있는지 없는지 자동으로 검사하게 된다

  • 제약조건을 부여하는 방식엔 컬럼레벨, 테이블레벨 두가지 방식이 존재한다

    -- 컬럼레벨 방식(컬럼 뒤에 곧바로 기술)
CREATE TABLE MEM (
  -- PRIMARY KEY 지정(NOT NULL + UNIQUE 속성을 가진다)
  MEM_NO NUMBER PRIMARY KEY,
  -- NOT NULL + UNIQUE 지정
  MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
  -- 제약조건 + 제약조건명 지정
  MEM_PWD VARCHAR2(20) CONSTRAINT MEM_PWD_NN NOT NULL,
  --NOT NULL 지정
  MEM_NAME VARCHAR2(20) NOT NULL,
  -- 외래키 지정 : MEM_GRADE테이블의 GRADE_CODE컬럼 안에 있는 값과 동일한 값만 허용
  GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE),
  -- CHECK 제약조건으로 F,M 값만 허용
  GENDER CHAR(1) CHECK(GENDER IN('F','M')),
  PHONE VARCHAR2(15),
  EMAIL VARCHAR2(30)
);

-- 테이블레벨 방식(모든 칼럼 기술 이후에 제약조건을 나열)
CREATE TABLE MEM (
  MEM_NO NUMBER NOT NULL,
  MEM_ID VARCHAR2(20) NOT NULL,
  MEM_PWD VARCHAR2(20) NOT NULL,
  MEM_NAME VARCHAR2(20) NOT NULL,
  GRADE_ID CHAR(2),
  GENDER CHAR(2),
  PHONE VARCHAR2(15),
  EMAIL VARCHAR2(30),
  -- UNIQUE 지정
  CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID),
  -- 외래키 지정 : 컬럼레벨, 테이블레벨 둘 중 하나만 기술한다
  FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);


NOT NULL
CREATE TABLE MEM (
  MEM_NO NUMBER NOT NULL
);
  • 삽입/수정 시 해당 컬럼에 NULL값이 들어가지 않게 한다
  • 컬럼레벨 방식으로밖에 부여하지 못한다


UNIQUE
CREATE TABLE MEM (
  MEM_ID VARCHAR2(20) UNIQUE
);

CREATE TABLE MEM (
  MEM_ID VARCHAR2(20) NOT NULL,
  UNIQUE(MEM_ID)
);
  • 컬럼에 중복값을 제한한다
  • 삽입/수정 시 기존 컬럼값에 중복된 값이 있을 경우 추가/수정이 되지 않게 한다
    => 하지만 NULL값 중복은 가능하다
  • 컬럼/테이블레벨 방식 모두 사용가능하다


CHECK
CREATE TABLE MEM (
  GENDER CHAR(1) CHECK(GENDER IN('F','M'))
);

CREATE TABLE MEM (
  GENDER CHAR(1) /*NOT NULL*/,
  CHECK(GENDER IN('F','M'))
);
  • 표현법 : CHECK(조건식)
  • 컬럼에 기록될 수 있는 값에 대한 조건을 설정할 수 있다
  • 테이블안 여러 컬럼들에 선언 가능하다
  • 비교연산자를 이용하여 조건을 설정하며 비교값은 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능하다
  • 컬럼레벨과 테이블레벨로 선언했을 때의 차이가 없는 유일한 제약조건이다
  • 다만 NULL값은 동등비교가 되지 않기 때문에 CHECK 제약조건을 걸었더라도 추가가 되어버린다
    => NULL을 방지하려면 NOT NULL 조건이 꼭 필요하다


PRIMARY KEY
CREATE TABLE MEM (
  MEM_NO NUMBER PRIMARY KEY
);

CREATE TABLE MEM (
  MEM_NO NUMBER,
  PRIMARY KEY(MEM_NO)
);
  • 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 칼럼에 부여하는 제약조건이다
  • 각 행들을 구분할 수 있는 식별자의 역할을 하게된다
  • 식별자는 중복되지 않고 값이 없어서는 안되므로 자동으로 NOT NULL + UNIQUE 성격을 가지게 된다
  • 한 테이블안에서 한번만 선언 가능하다
    => 테이블에서 선언 시 한번에 여러 컬럼에 선언하는 식으로 지정 가능하다 (ex.PRIMARY KEY(컬럼1,컬럼2))
    => 나중에 다른 테이블과 JOIN시 여러 컬럼의 조합값으로 JOIN된다
  • 개인정보는 PRIMARY KEY로 선택하지 않는 것을 권장한다
    => 식별자로서 이곳저곳에 데이터가 불려다니게 되기때문(= 정보유출이 될 수 있다)


FOREIGN KEY
  --  MEM_GRADE : 참조할테이블 , GRADE_CODE : 참조할컬럼
CREATE TABLE MEM (
  GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE)
);

CREATE TABLE MEM (
  GRADE_ID CHAR(2),
  FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

-- 삭제 옵션
CREATE TABLE MEM (
  -- 삭제를 제한함(기본옵션)
  GRADE_ID_3 CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE RESTRICTED,
  --부모데이터 삭제시 해당 데이터값을 사용하는 자식데이터를 NULL로 변경
  GRADE_ID_1 CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL,
  -- 부모데이터 삭제시 해당 데이터값을 사용하는 자식데이터를 함께 삭제(행 삭제)
  GRADE_ID_2 CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE,
);
  • 표현법(컬럼레벨) : 컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할테이블명(참조할컬럼명)
  • 표현법(테이블레벨) : CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)
  • 자기자신 테이블이나 다른 테이블의 특정컬럼(PK,UK)를 참조하는 제약조건이다
  • 참조키, 외래키라고도 불린다
  • 테이블 안의 여러 컬럼에 선언 가능하다
  • 삽입/수정하려는 값이 참조되는 테이블 컬럼 상에 존재하면 허용한다
    => 참조된 다른 테이블 컬럼이 제공하고 있는 값만 해당 컬럼에 들어갈 수 있다
    => 하지만 NULL값은 참조된 컬럼상 존재하지 않아도 가능하다
  • FK가 지정되어있으면 해당 컬럼이 자식컬럼이 되고 참조한 PK나 UK조건 컬럼이 부모컬럼이 된다
  • 만약 참조할 테이블이 부모테이블과 같다면 CONSTRAINT 와 참조할 컬럼명을 생략 가능하다
    => 생략시 자동으로 참조할 테이블의 PRIMARY KEY에 해당되는 컬럼이 참조할 컬럼으로 사용된다
    => 다만 참조할 컬럼의 타입과 외래키로 지정할 컬럼 타입이 같아야 한다
    => 부모 테이블의 값들이 삭제 등으로 내용이 변경되면 관계를 형성해둔 자식 테이블들도 모두 변경되어야 하기 때문에 유지 보수 차원에서 많이 사용되지는 않는다
    => 자식컬럼이 있는 한 부모컬럼은 삭제되지 않는다

  • 컬럼레벨, 테이블레벨 둘중 하나만 기술한다

  • 삭제 옵션
    • ON DELETE RESTRICTED : 기본옵션으로 따로 명시하지 않았어도 자동 적용되어있으며 자식이 사용중인 부모데이터의 삭제를 제한한다
    • ON DELETE SET NULL : 부모데이터 삭제 시 해당 데이터값을 사용중인 자식 데이터를 NULL로 변경한다
    • ON DELETE CASCADE : 부모데이터 삭제 시 해당 데이터값을 사용중인 자식데이터도 함께 삭제하며 해당 행 자체가 삭제된다


제약조건명(CONSTRAINT)
CREATE TABLE MEM (
  CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID)
);
  • 표현법 : CONSTRAINT 제약조건명 제약조건
  • 제약조건 부여시 직접 제약조건의 이름을 지정해주지 않으면 시스템에서 SYS C~~~ 등으로 임의의 제약조건명을 부여하게된다
    => 시스템이 자동으로 부여해주므로 생략도 가능하다
  • 제약조건명은 해당 제약조건이 충족되지 않아 오류가 났을때 출력되므로 알아보기 쉽도록 명칭을 따로 지정해주는 것이다
  • 중복되지 않고, 고유한 이름으로 지정하는 것을 권장한다
  • 컬럼레벨, 테이블레벨 방식 모두 사용 가능하다


[ 테이블 복제 ]

[표현법]
CREATE TABLE 테이블명
AS 서브쿼리;

-- 예시
CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;
  • SUBQUERY를 이용하여 테이블 생성(복사)가 가능하다
  • 컬럼들, 데이터들, NOT NULL 제약조건의 복사가 가능하다
    => 서브쿼리를 통해 테이블을 생성한 경우 제약조건은 NOT NULL만 복사된다


CREATE TABLE EMPLOYEE_COPY2
AS SELECT * FROM EMPLOYEE
    WHERE 1 ='0';
  • 위와같이 의도적으로 조건의 결과를 FALSE로 만들고 복사를 실행하면 해당 테이블에 있는 컬럼의 구조만 복사된다
    => 데이터 없이 컬럼들만 복사 됨


CREATE TABLE EMPLOYEE_COPY3
AS SELECT EMP_ID, EMP_NAME, SALARY, SALARY*12 연봉
    FROM EMPLOYEE;
  • 서브쿼리의 SELECT절에 산술연산, 함수식이 기술된 경우(= 추가로 만들어낸 컬럼) 꼭 별칭을 지정해주어야 한다
    => 별칭 없을 시 must name this expression with a column alias 에러 발생

Oracle 카테고리 내 다른 글 보러가기

댓글남기기

최근 글 10 개