170531 테이블의 생성과 제약조건 (DDL)

DB의 가장 대표적인 구성원은 테이블입니다. 

테이블은 저장공간을 가진 객체로 정의 할 수 있는데요. 이 테이블을 생성해야 그 안에서 데이터를 저장하고 활용 할 수 있겟죠. 하지만 가장 베이스에 정의되는 개념이기 때문에 마구잡이로 생성하면 안되겠습니다. 그래서 제약조건이 있는것이구요.

테이블에는 자신을 정의하는 내용들이 있는데요. 자신의 테이블이름과 자신의 안에 구성되어있는 컬럼 그리고 그 컬럼을 채워줄 데이터들의 유형들이 정의되어 있습니다. 

테이블을 생성하기 위해서는 접속한 데이터베이스 계정에게 CREATE TABLE 시스템 권한이 부여되어 있어야 합니다. 또한 접속한 데이터베이스 계정에게 저장 공간에 대한 권한(TABLESPACE 상의 QUOTA)이 설정되어 있어야 합니다.

접속한 계정으로 자신으 스키마 테이블을 생성한다고 표현하는데요. 

스키마란 소유권이 동일한 객체들의 묶음입니다. 그냥 계정이름에 따른 테이블을 할당한다고 보면되겠네요. 

예를 들어) 지빵.밥상, 지빵.책상, 영희.밥상, 영희.책상 처럼 각각의 테이블마다 누구의 것이라고 이름표를 붙여주는 느낌이네요 

-생성문법-

CREATE TABLE 스키마이름.테이블이름 ( 컬럼1 DataType ,컬럼2 DataType , ... , );

생성규칙

• 오라클에서는 테이블이름 및 컬럼이름이 30 BYTES 를 넘을 수 없다.
• A-Z, a-z, 0-9, _, $, # 문자만 포함될 수 있으며, 숫자로 시작하면 안됩니다.
• 테이블(또는 객체) 이름 및 컬럼이름에 오라클 데이터베이스 서버의 예약어는 사용될 수 없습니다.
• 테이블(또는 객체) 이름은 같은 사용자에 의해 소유된 다른 객체의 이름과 중복될 수 없습니다.
• 이미 다른 객체가 사용 중인 이름으로 테이블을 생성하려고 시도하면 아래와 같은 에러가 발생됩니다.

Default를 이용한 생성 방법 

테이블을 생성할 경우 컬럼에 들어갈 값들을 Default로 미리 지정해 줄 수 있습니다. 

위와 같이 컬럼에 Default를 설정해주면 아무것도 입력되지 않을 경우에 Default 설정해준 값이 대신 들어가게 됩니다. 이런방법을 활용해서 null로 인해 에러가 발생하는걸 방지할 수 있겠네요. 

위의 결과를 보면 파란 DEFAULT에는 설정이 안되어있으므로 NULL값이 들어가게 되고 빨간색 DEFAULT에는 각각 SEOUL과 sysdate 값이 들어가게 될것입니다.

Default 고려사항 

• 컬럼의 데이터 유형과 일치한 상수(Literal) 값, 표현식, SQL 함수들을 컬럼의 DEFAULT 값으로 설정할 수 있습니다. 단, 다른 컬럼의 이름이나 ROWNUM, NEXTVAL, CURRVAL 같은 PSEUDO-컬럼이 DEFAULT 값으로 설정될 수 없습니다.

• 필요한 경우, 테이블의 컬럼 하나 하나에 대하여 DEFAULT 옵션을 이용하여 디폴트로 사용될 값을 각각 정의합니다.

• 테이블에 한 행을 입력할 때, DEFAULT 옵션이 설정된 컬럼에 대하여, 컬럼에 입력되는 값이 있으면 그 값이 입력되고, 입력 값이 없으면 DEFAULT 옵션으로 설정된 값이 자동으로 입력됩니다.

• UPDATE문과 INSERT문에서 DEFAULT 키워드를 이용하여 데이터입력 및 수정을 할 수 있습니다.

Default 값 변경 및 해제 방법

ALTER TABLE 스키마.테이블 MODIFY 컬럼 DEFAULT 값 ; * 값 = 숫자, '날짜', '문자' , '' (설정된 default값 해제)

---

DATA TYPE

DATA TYPE	특징
VARCHAR2	• 최대 4000 BYTE 길이까지만 처리 가능합니다.  • 가변-길이 문자 데이터 유형 (6Byte 입력시 6만큼)
CHAR	• 최대 2000 BYTE 길이까지만 처리 가능합니다.  • 고정-길이 문자 데이터 유형으로, 실제 데이터가 차지하고 남은 공간은 빈칸으로 채워서 저장됩니다.
DATE	• 날짜 데이터를 처리하는 데이터 유형으로, 사용 시에 길이를 명시하지 않습니다.  • DATE 데이터 유형이 세션(CLIENT)에서 기본적으로 표시되는 형식은 사용자가 NLS_DATE_FORMAT 세션 설정에 의하여 명시적으로 정해 주거나 NLS_TERRITORY 세션 설정에 의하여 암시적으로 정해 집니다.  • 위의 세션 설정을 명시적으로 선언해서 설정하지 않은 경우에는 클라이언트의 프로그램이 실행되는 운영체제의 언어 및지역에 따라, 자동으로 설정됩니다.
NUMBER	• 저장되는 NUMBER는 2 자리당 1 BYTE 저장 공간을 사용합니다.  NUMBER (5,2) 의 경우 아래와 같은 개념으로 생각하면된다.   정수      소수

TIME STAMP 유형
TIMESTAMP [ (fractional_seconds) ]	[세기/년/월/일/시/분/초/밀리초]형식  NLS_TIMESTAMP_FORMAT 설정으로 명시적으로 정해 주거나  NLS_TERRITORY 설정에 의하여 자동으로 정해집니다.
TIMESTAMP [ (fractional_seconds) ] WITH TIME ZONE	TIMESTAMP 데이터유형의 값에 시간대가 추가  NLS_TIMESTAMP_TZ_FORMAT 설정으로 명시적으로 정해 주거나  NLS_TERRITORY 설정에 의하여 자동으로 정해 집니다.  접속한 세션(CLIENT)의 시간대가 고려됩니다
TIMESTAMP [ (fractional_seconds) ] WITH LOCAL TIME ZONE	데이터베이스 서버의 시간대를 기준으로 서버와 CLIENT 세션의시간대와의 시차가 계산되어 반영   NLS_TIMESTAMP_FORMAT 설정으로 명시적으로 정해 주거나  NLS_TERRITORY 설정에 의하여 암시적으로 정해 집니다.  접속한 세션(CLIENT)의 시간대가 고려됩니다.

[+9:00] 시간대의 오라클-서버에, [+7:00] 시간대의 사용자가 접속하여 '2010/06/18/ 20:00:00'의 클라이언트의

날짜-시간 데이터를 입력하는 경우, 테이블의 데이터 유형에 따라 처리하는 방법이 아래처럼 차이가 납니다.

TIMESTAMP [ (fractional_seconds) ]	사용자가 입력한 값이 그대로 오라클 서버에 저장됨   2010/06/18/ 20:00:00 로 표시됨   즉, 서버와 세션의 시간대 차이가 전혀 고려되지 않으며 시간대 오류가 발생할 가능성 있음.
TIMESTAMP [ (fractional_seconds) ] WITH TIME ZONE	사용자가 입력한 값에 사용자의 시간대가 추가됨 (+7:00)  2010/06/18/ 20:00:00 +7:00 로 표시됨  시간대 오류 극뽁
TIMESTAMP [ (fractional_seconds) ] WITH LOCAL TIME ZONE	시간대의 차이를 반영함   2010/06/18/ 22:00:00 차이를 반영해 두시간 더해져 기록됨   서버의 시간대를 기준으로 처리된 날짜시간이 표시됩니다.

-TIMESTAMP 데이터유형은 시간대를 표시하지 못하므로, 시간대를 표시해야 하는 경우라면, TIMESTAMP 데이터유형은 올바른 선택이 아닙니다.

- TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE 데이터유형이 지정된 테이블의 컬럼에 시간대를 고려해야 하는 DATETIME 데이터를 입력할 때, SYSDATE 함수를 절대로 사용하지 마십시오.

- TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE 데이터유형이 지정된 테이블의 컬럼에 값을 입력할 때는 SYSTIMESTAMP 함수나 LOCALTIMESTAMP 함수, 또는 CURRENT_TIMESTAMP 함수를 사용하시기 바랍니다.

SYSTIMESTAMP	데이터베이스 서버가 운영 중인 시스템의 날짜와 시간을 TIMESTAMP WITH TIME ZONE 데이터 유형으로 반환합니다.
CURRENT_TIMESTAMP	세션(CLIENT)의 시간대에서 현재 날짜와 시간을 TIMESTAMP WITH TIME ZONE 데이터 유형으로 반환합니다
LOCALTIMESTAMP[(밀리초_자리수)]	세션(CLIENT)의 시간대에서 현재 날짜와 시간을 TIMESTAMP 데이터 유형으로 반환합니다.

INTERVAL 데이터 유형
INTERVAL YEAR [ (연도 최대-자릿수) ] TO MONTH	기간 데이터를 [n 년]-[n 개월] 형식으로 처리합니다. [연도 최대-자릿수] = 0 ~ 9, 명시하지 않으면, 2 로 지정됩니다.
INTERVAL DAY [(일 최대-자리수)]  TO SECOND[(밀리초-자리수]	기간 데이터를 [n일]-[n시간]-[n분]-[n초] 형식으로 처리합니다. [일 최대-자릿수] = 0 ~ 9 , 명시하지 않으면 2로 지정  [밀리 초-자릿수] = 0 ~ 9 , 명시하지 않으면 6으로 지정

밀리초와 시간대 이름을 표시하는 포맷 

ALTER SESSION SET  NLS_TIMESTAMP_FORMAT =  'YYYY/MM/DD HH24:MI:SSXFF TZR' ; XFF - 밀리초 TZR - 시간대 이름 ( NLS_TIMESTAMP_TZ_FORMAT사용) : TIMEZONE → (+9:00 & Asia/Seoul)

Constraint는 사용자가 요구하는 업무 규칙을 데이터에 대하여 구현한 것입니다. 데이터베이스에 저장되는 데이터가 준수해야 할 규칙으로 이 규칙을 주수하는 데이터만이 데이터베이스에 저장되어 사용 될 수 있습니다.

제약조건의 타입	설명
NOT NULL	NULL 이 와서는안된다. 반드시 값이 있어야 함.
UNIQUE	테이블에 있는 모든 행에 대하여 지정된 컬럼(또는 컬럼들)에 입력된 값과 같은 값(중복된 값)이 입력될 수 없습니다. 또한 입력되는 값이 없으면, 검사를 수행하지 않습니다.
PRIMARY KEY	테이블에 있는 각 행을 고유하게 식별할 수 있는 데이터 또는 데이터 조합입니다.
FOREIGN KEY	다른 테이블(참조되는 테이블)의 컬럼에 없는 값은 지정된 컬럼에 입력될 수 없습니다.
CHECK	명시된 조건을 위반하는 데이터는 입력될 수 없습니다.

NOT NULL 

한행이 입력될때에 NOT NULL 제약조건이 지정된 컬럼에는 반드시 값이 들어가야 한다.

NOT NULL이 입력된곳에 값을 입력하지 않는다면 에러가 발생.

UNIQUE 

테이블에 있는 모든 행에 대하여 지정된 컬럼들에 입력된 값과 중복된 값이 입력될수 없는 성질입니다. 입력되는 것이없으면 검사를 수행하지 않습니다. 

신입사원이 전화번호를 할당받기 전의 경우로 생각할 수 있겟습니다. 사원으로서 존재는 하나 전화번호는 아직 없을수도 있겠죠 ? UNIQUE를 사용해서 처리해줍시다. 

PRIMARY KEY 

다른 제약 조건의 경우 하나의 테이블에 대하여 여러번 필요에 의해서 사용 할 수 있으나, 기본키 제약조건은 테이블에 단 하나만 올 수 있다. 

다른 제약조건은 기능에 의해서 사용되기 때문에 여러번 사용 할 수 있는데 기본키는 각 행을 고유하게 식별해야 되기 때문에 null 이 올 수 없고, 중복이 되어서는 안된다

(not null + unique) 와 기능적으로는 같으나 이 두개의 기능을 합쳣다고 기본키로 사용되는것은 아니며 오로지 primary key 만 기본키로 사용합니다. 

FOREIGN KEY

외래키는 구현은 컬럼이름으로 하지만 실제 내부적으로는 기본키를 참조하는것이 전제조건이다. 이때 외래키가 참조하는 기본키를 부모키(parent key)라고 하며 외래키는 자식키(child key)라고 한다. 레퍼런스(참조)의 관계에 있어서는 부모 자식 관계로 정의함.

레퍼런스 되는쪽(부모쪽) 에 외래키에서 참조하는 내용이 있으면 변경이 적용될 수 없다. 

외래키쪽은 마음대로 삭제할수 있다. 하지만 insert ,update의 경우에는 레퍼런스에 있는 요소에 맞게 추가/변경 되어야 한다.

CHECK

조건을 걸어놓고 행이 입력될때마다 조건을 검사 할 수 있다. 

CURRVAL, NEXTVAL, LEVEL, ROWNUM 같은 PSEUDO 컬럼은 사용할 수 없습니다.

• SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, LOCALTIMESTAMP, CURRENT_DATE, UID, USER, USERENV 함수들을

사용할 수 없습니다.

• 다른 행에 있는 다른 값을 참조하는 SELECT 문(즉, 서브쿼리)을 사용할 수 없습니다.

제약조건을 사용하기위한 가이드라인 

- 제약조건을 정의할때는  CONSTRAINT 제약조건이름 으로 이름을 명시해 주는것을 권장한다. (명시하지 않으면 서버가 SYS_Cnnnn 등의 형식으로 임의부여함)

- 컬럼-레벨정의 : 컬럼한줄에 정의 , NOT NULL 제약조건은 컬럼-레벨로 한다.

-  테이블-레벨정의  : 컬럼정의가 끝나고 밖에서 , 콤마를 써서 따로 정의합니다. 둘 이상의 컬럼을 조합하여 하나의 제약조건을 정의하는 경우에는 테이블레벨을 사용

  

FOREIGN KEY 제약조건 주의 사항

 - 컬럼 레벨 문법을 사용하는 경우에는 [FOREIGN KEY(컬럼명)]를 명시하면 안됩니다. (테이블 레벨 방법으로 기술 시에만 명시)

 - REFERENCES 키워드 다음에는 참조되는 테이블 이름(컬럼이름)을 명시하고,

   이 때 참조되는 컬럼에는 PRIMARY KEY  또는 UNIQUE 제약조건이 반드시 설정되어 있어야만 합니다. 

   (외래키는 기본키에 종속된다는 개념)

 - ON DELETE CASCADE 옵션 명시 : 자식 테이블에서 해당 내용 삭제 후 부모테이블에서도 삭제

 - ON DELETE SET NULL 옵션 명시 : 자식 테이블에서 내용 NULL로 변경 된 후 부모테이블에서 행이 삭제

---

DROP 

:테이블 삭제 

DROP TABLE 테이블이름 ;

-테이블과 관련된 INDEX, CONSTRAINT, TRIGGER 객체도 모두 삭제됨, VIEW 객체는 삭제되지 않고 사용할수 없는 상태

-테이블에 할당된 디스크의 저장공간은 사용이 끝난공간으로 전환

-DDL 문이기 때문에 ROLLBACK이 안됨, 대신 휴지통 기능에 의해 삭제된 정보가 관리됨. 

-PURGE 옵션 작성하면 휴지통에 저장되지않고 모두 삭제됨

Drop의 경우 테이블자체를 삭제하기 때문에 운용중인 서비스에서 실수로라도 테이블을 잘못 드롭하면 서비스 자체가 불가능해질 수 도있다. 서비스가 불가능함은 신뢰도를 잃어 기회비용및 고객유치에 큰 손실을 겪게될것이다. 조심해서 다뤄야한다. 만약 테이블을 삭제 및 절단해야한다면, 객체단위 백업을 반드시 수행한 후 테이블 삭제 및 절단을 수행해야 한다.

---

OnLine Transaction Processing (OLTP) : 은행등의 작은 데이터 단위의 트랙잭션이 많은 작업들.

업무시간동안은 insert나 update같은 작업이 많이 일어나고 있는 환경이다.

Data-Warehousing(DW환경) : 은행에서 업무시간이 끝나고 집계등의 select등의 작업이 주로 이루어지는 환경을 DW 환경이라한다.