[SQL] Oracle - (DDL) CREATE / 제약조건(CONSTRAINTS)

 

< DDL (Data Definition Language) > 

 

- 데이터 정의 언어

- 오라클에서 제공하는 객체(Object)*를 새로이 만들고(CREATE), 구조를 하고(ALTER),

  구조 자체를 삭제하는(DROP) 명령문

- 주로 DB관리자, 설계자가 사용하는 데이터 구조 자체를 정의하는 언어 

 

* 오라클에서의 객체(구조)

: 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE)

  인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER)

  프로시져(PROCEDUER), 함수(FUNCTION)

  동의어(SYNONYM), 사용자(USER)

 

 

< CREATE TABLE >

 

- 테이블(TABLE) : 행(ROW), 열(COLUMN)로 구성되는 가장 기본적인 데이터베이스 객체

                              테이블을 통해 모든 데이터를 저장함

[ 표현법 ]

CREATE TABLE 테이블명(  -- 중괄호임!!

        컬럼명 자료형,

        컬럼명 자료형,

        컬럼명 자료형,

        ...

);

 

 

< Oracle 자료형 >

 

- 문자 : Byte 단위 (숫자, 영문자, 특수문자 : 1글자당 1Byte / 한글 : 1글자당 3Byte)

CHAR(바이트수)	- 최대 2000Byte까지 지정 가능   - 고정길이 (할당된 크기 유지)   - 주로 들어올 값의 글자수가 정해져 있을 경우 사용
VARCHAR2(바이트수) VAR '가변' 2 '2배'	- 최대 4000Byte까지 지정 가능   - 가변길이 (적은 값이 들어오면 그 값에 맞춰서 크기가 줄어듬)

- 숫자(NUMBER) : 정수, 실수 구분없이 같은 자료형으로 표현

- 날짜 (DATE)

 

 

< COMMENT ON COLUMN >

 

- 컬럼에 대한 설명(= COMMENTS)를 입력

[ 표현법 ] COMMENT ON COLUMN 테이블명.컬럼명 IS '주석 내용';

 

 

< INSERT INTO >

 

- 테이블에 한 행의 데이터를 추가할 수 있는 구문

- 값을 컬럼 순서대로 작성해야 함

[ 표현법 ] INSERT INTO 테이블명 VALUES(첫번째 컬럼의 값, 두번째 컬럼의 값, 세번째 컬럼의 값...);

 

 

< 제약조건 (CONSTRAINTS) >

 

- 원하는 데이터값만을 유지, 보관하기 위해서 특정 컬럼마다 설정하는 제약

- 제약조건이 부여된 컬럼에 들어갈 데이터를 자동으로 검사

- 데이터 무결성 보장 목적

- 컬럼레벨 방식과 테이블레벨 방식이 존재

NOT NULL	컬럼레벨 방식	- 해당 컬럼에 반드시 값이 존재해야하는 경우 사용     (NULL값이 있으면 안되는 컬럼에 사용)   - 삽입, 수정 시 NULL값을 허용하지 않도록 제한
UNIQUE	컬럼레벨 방식 테이블레벨 방식	- 컬럼에 중복값을 제한하는 제약조건   - 삽입, 수정 시 해당 컬럼에 중복값이 있을 경우,     추가 또는 수정이 되지 않도록 제한
CHECK	컬럼레벨 방식	- 컬럼에 기록될 수 있는 데이터에 대한 조건을 설정
PRIMARY KEY (PK)	컬럼레벨 방식	- 테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 컬럼에 사용   - 각 행들의 정보를 구분할 수 있는 식별자의 역할   - 컬럼값이 중복되지 않는 컬럼에 부여 (NOT NULL + UNIQUE)   - 한 테이블 당 하나의 컬럼에만 설정 가능   - 두 컬럼을 하나로 묶어서 설정할 수 있음 => 복합키
FOREIGN KEY (외래키)	컬럼레벨 방식 테이블레벨 방식	- 해당 컬럼에 다른 테이블에 존재하는 값만 들어와야 할 때     부여하는 제약조건   - 다른 테이블(부모 테이블)을 '참조'한다고 표현   - 다른 테이블간의 관계를 형성할 수 있음(부모테이블 - 자식테이블)

 

--> 회원들의 데이터(아이디, 비밀번호, 이름, 회원가입일)를 담기 위한 MEMBER 테이블

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20),
    MEMBER_DATE DATE
);

--> 컬럼에 주석 달기

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_DATE IS '회원가입일';


SELECT * FROM USER_TABLES; -- 만든 테이블의 데이터 볼 수 있음
-- USER_TABLES : 현재 이 계정이 가지고 있는 테이블들의 전반적인 구조를 확인할 수 있는 데이터 딕셔너리
-- 데이터 딕셔너리 : 다양한 객체들의 정보를 저장하고 있는 시스템 테이블

SELECT * FROM USER_TAB_COLUMNS; -- 만든 테이블 컬럼의 구조 볼 수 있음

--> NOT NULL 제약조건

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,  -- 컬럼레벨 방식
    MEM_ID VARCHAR2(20) NOT NULL, 
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

INSERT INTO MEM_NOTNULL
VALUES(2, NULL, NULL, NULL, NULL, NULL, NULL); 
-- NOT NULL 제약조건에 위배 오류 발생
-- cannot insert NULL into ("DDL"."MEM_NOTNULL"."MEM_ID")

--> UNIQUE 제약조건

CREATE TABLE MEM_UNIQUE (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, -- 컬럼레벨 방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

CREATE TABLE MEM_UNIQUE (
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_ID)  -- 테이블레벨 방식
);

INSERT INTO MEM_UNIQUE VALUES(1, 'user01', 'pass01', '홍길동', '여', NULL, NULL);
INSERT INTO MEM_UNIQUE VALUES(2, 'user01', 'pass02', '김길동', '남', NULL, NULL);
-- UNIQUE 제약조건에 위배되었으므로 INSERT에 실패
-- ORA-00001: unique constraint

--> CHECK 제약조건

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')), -- CHECK(조건식)
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL
);

SELECT * FROM MEM_CHECK;

INSERT INTO MEM_CHECK VALUES(1, 'user01', 'pass01', '홍길동', '가', NULL, NULL, SYSDATE);
-- ORA-02290: check constraint (CHECK에 해당하는 값만 넣어야함)

--> PRIMARY KEY 제약조건

CREATE TABLE MEM_PRIMARYKEY(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30) 
);


CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) PRIMARY KEY, -- PRIMARY KEY는 한 번만 받을 수 있음
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30) 
);
-- PRIMARY KEY가 한 테이블에 두 개가 될 수 없다!
-- 오류 : table can have only one primary key

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER,
    MEM_ID VARCHAR2(20),
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    PRIMARY KEY(MEM_NO, MEM_ID) -- 컬럼을 묶어서 PRIMARY KEY하나로 설정
);

--> FOREIGN KEY

-- MEM_GRADE테이블의 GRADE_CODE컬럼 참조

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE), -- 컬럼레벨 방식
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) -- 테이블레벨 방식
);

[ FOREIGN KEY 삭제 옵션 ]

 

- 외래키 제약조건 부여 시(자식테이블 생성 시),

  부모테이블의 데이터가 삭제되었을 때 자식테이블에서 어떻게 처리할 것인지 옵션으로 정할 수 있음

- 별도로 지정하지 않으면 'ON DELETE RESTRICTED(삭제 제한)'으로 설정

ON DELETE SET NULL	부모데이터 삭제 시, 해당 데이터를 사용하고 있는 자식데이터를 NULL로 변경
ON DELETE CASCADE	부모데이터 삭제 시, 해당 데이터를 사용하고 있는 자식데이터도 같이 삭제

 

---

 

< CONSTRAINT >

 

- 제약조건 부여 시, 제약조건명도 지정하는 표현식

- 제약조건에 위배되는 경우, 오류구문에 제약조건명을 알려주는데

   이때 파악하기 용이하도록 제약조건명을 지정해줄 수 있음

[ 표현법 ]

> 컬럼레벨 방식

CREATE TABLE 테이블명(

       컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,

       컬럼명 자료형,

        ...

);

> 테이블레벨 방식

CREATE TABLE 테이블명(

         컬럼명 자료형,

         컬럼명 자료형,

          ...

          CONSTRAINT 제약조건명 제약조건(컬럼명)

);

 

< DEFAULT >

 

- 특정 컬럼에 들어올 값에 대한 기본값 설정

[ 표현법 ] 컬럼명 자료형 DEFAULT 기본값 제약조건

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(15),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL -- MEM_DATE컬럼은 오늘 날짜를 기본값으로 
);