오라클 SQL 문법 공부24- CREATE TABLE 과 제약조건

서론

기본적인 DML 문법들은 끝이 난 것 같고 데이터를 SELECT, INSERT, UPDATE, DELETE 한다는 것은 특정 테이블의 데이터를 조작한다는 것이다. 조작을 하려면 당연히 테이블이 존재해야 하는데 이제 DDL 중에 하나인 CREATE TABLE.. 에 대해서 알아보고자 한다. 

본론

기본 문법

--CREATE(생성하기)
--컬럼명 데이터형 뒤에 DEFAULT SYSDATE 라고 하면 기본 값으로 들어간다.
--TABLE SPACE USERS;
--테이블을 저장하기 위해서 테이블을 묶어놓은 공간, 폴더 같은 공간, 논리적인 공간이다.
--테이블이 만들어 질때 하나의 테이블스페이스 소속이 되어있어야 한다.
--테이블들의 성격을 구분짓기 위한 테이블들의 집합체..? 성능상의 차이는 거의 없다.
--두개의 테이블 스페이스에 하나의 테이블을 저장 할 순 없다, 테이블스페이스가 없는 테이블은 없다.

--테이블을 만들때 테이블스페이스를 지정하지 않으면 디폴트 테이블스페이스로 지정된다.
--디폴트 테이블스페이스는 유저별로 다르다.

create table ddl_test
(no number(3) primary key,
name varchar2(10) not null,
birth date default sysdate
)
tablespace users;

--테이블이 꽉차서 insert가 안되요, 하면 해당 테이블의 테이블스페이스 크기를 확인해야됨

--table 생성시 주의 사항
--1. 테이블이름은 반드시 문자로 시작해야 한다.
--숫자로 시작할 수 없고, 특수문자로 시작되는 경우 ""(쌍따옴표)를 이용해서 만들 수 있으나 권고x
--
--2. 테이블 이름이나 컬럼이름은 최대 30bytes까지
--
--3. 한글이 지원되는 DB라면 한글이름의 테이블, 컬럼이름 가능
--
--4. 같은 계정 내에서는 동일한 오브젝트명으로 생성할 수 없다.
--(다른계정일 경우 상관x)
--
--5. DB에서 쓰는 명령어나 함수 등 약속된 단어에 대해서는 테이블명이나 컬럼명으로 사용하는 것을 피한다.

--테이블의 각 컬럼에 default 값 확인
select * from dba_tab_columns where owner in ('SCOTT', 'HR');
--DATA_PRECISION, DATA_SCALE 은 NUMBER 타입 컬럼에만 값이 있음
--DATA_PRECISION은 NUMBER 형일때 괄호안의 숫자임
--문자나 날짜타입이면 DATA_LENGTH로 사용하면 됨
--넘버타입일때 DATQA_PRECISION 비어있으면 그냥 NUMBER 타입
--DATA_SCALE이 0일때는 NUMBER(4) 이런식임, 있으면 NUMBER(2,2)이런식
--DEFAULT 가 설정되어있으면 DATA_DEFAULT 값을 보면 된다.
--NULLABLE로 NULL 여부체크

CREATE TABLE [스키마].테이블명 (컬럼명 데이터타입) [TABLESPACE 테이블스페이스명]; 형식으로 테이블을 생성한다. 테이블을 생성할 때 이 테이블이 저장되는 논리적인 공간을 제시 해주어야 하는데 이 공간이 테이블 스페이스이다. 나중에 유저생성 쪽에서 다루겠지만 유저를 생성하게 되면 기본 테이블스페이스를 지정하는 부분이 있는데 테이블스페이스 쪽을 생략하면 이 기본 테이블스페이스로 지정이 된다. 또한 스키마도 생략하고 테이블 명만 적어주면 해당 유저명의 스키마로 생성이 된다.

여기서 스키마는 논리적인 구조를 다루는 것으로써 스키마가 다르면 동일한 테이블명을 사용 할 수가 있다. EX) scott.test, hr.test 당연히 select * from test라고 쳐보면 해당 유저의 스키마에 속한 테이블이 검색이 되며 권한이 있다면 다른 스키마의 테이블을 조회 할 수도 있다. 혹은 스키마명을 생략하고도 다른 스키마의 테이블을 검색 할 수도 있는데 이것은 추후에 다룰 시노님(SYNONYM)에서 정의가 가능하다.

정리하자면 테이블 생성은 특정 테이블스페이스에 어떠한 컬럼들을 가지고 있는 테이블을 생성 할 것이고 이것을 특정 스키마로 관리하겠다 라고 생각하면 될 것 같다. 보통은 CREATE 하는 유저의 스키마와 기본 테이블스페이스로 생성될 것이다. 오라클 11g 버전부터는 테이블 생성 시 세그먼트가 즉시 할당되지 않기 때문에 ANY CREATE TABLE 권한만 있다면 특정 스키마에 테이블 생성은 모두 가능하다. (단 해당 유저가 특정 테이블스페이스에 QUOTA가 없다면 INSERT는 안 될 것이다) 

컬럼 데이터 타입

문자형 데이터 타입 

데이터 유형	정의
CHAR(n)	고정 길이 데이터 타입(최대 2000byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간 공백으로 채워진다.
VARCHAR2(n)	가변 길이 데이터 타입(최대 4000byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간은 채우지 않는다.
NCHAR(n)	고정 길이 유니코드 데이터 타입(최대 2000byte)
NVARCHAR2(n)	가변 길이 유니코드 데이터 타입(최대 4000byte)
LONG	가변 길이 데이터 타입(최대 2Gbyte)
CLOB	대용량 텍스트 데이터 타입(최대 4Gbyte)
NCLOB	대용량 텍스트 유니코드 데이터 타입(최대 4Gbyte)

숫자형 데이터 타입 

데이터 유형	정의
BINARY_FLOAT	부동 소수형 데이터 타입(4byte) -32bit 부동 소수
BINARY_DOUBLE	부동 소수형 데이터 타입(8byte) -64bit 부동 소수
NUMBER(P,S)	P, S로 표현 숫자 데이터 타입 p: 1 ~ 38, s: -84 ~ 127 p(precision): 유효자리수, s(scale): 소수점 유효자리

날짜형 데이터 타입 

데이터 유형	정의
DATE	고정 길이 날짜
INTERVAL_YEAR	날짜(년도, 월)형태의 기간 표현 데이터 타입()
INTERVAL_DAY	날짜 및 시간(요일, 시, 분 초)형태의 기간 표현 데이터 타입
TIMESTAMP	밀리초(ms)까지 표현 데이터 타입
TIMESTAMP_WITH TIME ZONE	날짜 및 시간대 형태의 데이터 타입
TIMESTAMP_WITH LOCAL TIME ZONE	저장 시 데이터베이스 시간대를 준수, 조회시 조회하는 클라이언트 시간 표현 데이터 타입

이진 데이터 타입 

데이터 유형	정의
RAW(n)	가변 길이 이진 데이터 타입(최대 2Gbyte)
LONGRAW	가변 길이 이진 데이터 타입(최대 4Gbyte)
BLOB	대용량의 바이너리 데이터를 저장하기 위한 데이터 타입(최대 4Gbyte)
BFILE	대용량의 바이너리 데이터를 파일형태로 저장하기 위한 데이터 타입(최대 4Gbyte)

출처 - http://www.incodom.kr/

보통은 문자형은 가변길이 데이터 타입인 VARCHAR2를 주로 사용하고 데이터 크기에 맞춰서 사용하는 편인 것 같다. 날짜형의 경우 NUMBER 타입을 주로 사용하는데 뒤의 괄호를 생략하면 최대 값은 38로 지정이 된다. 특히나 앞에 숫자인 P부분은 총 자리수를 뜻하며 뒤의 S부분은 소수점 자리 숫자를 의미 한다. NUMBER(5,3) 이라면 총 자리수는 5자리, 소수점 4번째 자리에서 반올림하여 3자리까지 표시하겠다라는 의미이다. 이 타입에 12.321 를 INSERT 하면 정상적으로 그대로 들어가지고 12.3335라면 반올림 되어 12.334 으로 들어가진다. 하지만 123.333 이라고 입력하면 총 자리수인 5를 넘어서기 때문에 오류가 난다. 앞에는 총 자리 수, 뒤에는 소수점 어느자리까지 표시할 것인가로 기억하면 좋을 것 같다.  

제약조건

--컬럼명 데이터타입 NOT NULL
--특정 컬럼에 NULL 값을 허용하지 않은 제약조건이다. 데이터를 입력하거나 수정할 경우에 해당 컬럼에 NULL 값이 들어간다면 제약조건 위배가 되어 오류가 난다.
--컬럼명을 제시 해줄 때 바로 제약조건을 넣어줄 수 있으며 이럴 경우에 제약 조건명은 오라클 내부 특정 이름으로 지정된다.
--CONSTRAINTS를 이용하여 제약조건을 명시해 줄 수 있다.  
CREATE TABLE TB
(A VARCHAR2(10) NOT NULL);

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS NN_A  NOT NULL(A))


--컬럼명 데이터타입 UNIQUE
--유니크 제약조건은 중복이 불가능, 즉 들어가는 값이 유일해야 한다는 조건이다
--역시나 동일한 값을 입력할 경우에는 제약조건 위배가 되며 자동으로 인덱스가 생성된다.
--중복값만 체크하기 때문에 공백이 허용되고 공백도 넣고 싶지 않다면 NOT NULL도 넣거나 PRIMARY KEY로 지정해주는게 좋다.
--모든 제약조건들은 컬럼 옆에 즉시 제약 조건을 달 경우 오라클 내부 특정 이름으로 지정되며 CONSTRAINTS를 이용하여 따로 제약 조건명을 지정해줄수 있다.
CREATE TABLE TB
(A VARCHAR2(10) UNIQUE);

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS UNIQUE_A  UNIQUE(A) )

--컬럼명 데이터타입 PRIMARY KEY
--기본키 NOT NULL 이며 중복값도 허용되지 않는다. (NOT NULL + UNIQUE)
--테이블당 하나의 기본키를 설정해 줄 수 있으며 자동으로 인덱스가 생성된다.
CREATE TABLE TB
(A VARCHAR2(10) PRIMARY KEY);

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS PK_A  PRIMARY KEY(A) )

--컬럼명 REFERENCES 참조테이블(참조테이블 컬렴명)
--참조키 특정 테이블의 컬럼을 참조하여 해당 컬럼에 있는 값들만 입력이 가능하다. 참조 무결성이라고도 한다
--참조하는 테이블의 컬럼은 기본키만 가능하다.
CREATE TABLE TB
(A VARCHAR2(10) REFERENCES TB2(A) );

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS RK_A  FOREIGN KEY(A) REFERENCES TB2(A))

--CHECK
--특정값만 입력 받게끔 조건을 걸 때 사용한다
CREATE TABLE TB
(A VARCHAR2(10) CHECK(A IN ('콜라', '사이다'));

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS CK_A CHECK(A IN ('콜라', '사이다'))

--DEFAULT
--데이터를 입력 받지 않아도 INSERT시 기본으로 입력되는 값
CREATE TABLE TB
(A VARCHAR2(10) DEFAULT '식혜');

CREATE TABLE TB
(A VARCHAR2(10) 
CONSTRAINTS DE_A DEFAULT '식혜')

--제약 조건 확인하는 쿼리
SELECT M.CONSTRAINT_NAME AS 제약조건이름, M.CONSTRAINT_TYPE AS 제약조건타입,
       M.TABLE_NAME AS 제약조건테이블명, D.COLUMN_NAME AS 제약조건컬럼명,
       R.TABLE_NAME AS 참조테이블명, O.COLUMN_NAME AS 참조컬럼명
FROM DBA_CONSTRAINTS M, DBA_CONS_COLUMNS D, DBA_CONSTRAINTS R, DBA_CONS_COLUMNS O
WHERE M.CONSTRAINT_NAME = D.CONSTRAINT_NAME
  AND M.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME
  AND R.CONSTRAINT_NAME = O.CONSTRAINT_NAME
  AND M.OWNER = 'SCOTT'

제약조건들은 컬럼명 들을 명시 할때 바로 넣을 수도 있는데 이럴경우 제약조건명이 자동으로 생성된다. 제약조건 명을 관리하기 좋게 따로 명명하려면 컬렴 명시 후CONSTRAINTS 명령어를 통해 지정해주면 된다. 위의 예시 중 CONSTRAINTS 뒤에 있는 이름들이 명명한 이름들이다.

결론

CREATE TABLE과 제약조건에 대해서 알아보았다. CREATE TABLE 할때 제약조건이 가장 중요하기 때문에 잘 확인하도록 하자.