Database Management - DDL CREATE TABLE

기본적으로 데이터들을 저장하기 위한 테이블들을 정의해야한다.

https://www.oracletutorial.com/oracle-basics/oracle-create-table/

Oracle CREATE TABLE By Examples

위의 출처를 통해 Create table을 요약했다.

 

CREATE TABLE

create table [schemaName.]tableName (
    column1 dataType columnConstraint,
    column2 dataType columnConstraint,
	...
    tableConstraint
);

• column(n) : 열의 이름이 들어가게 된다.
• dataType : 열이 가지는 레코드(데이터)의 atomic type을 넣는다.
• columnConstraint : 해당 열이 가지는 제약조건을 명시한다.
• tableConstraint : 테이블이 가지는 제약조건을 명시한다.

 

Data Type

해당 데이터 타입들은 전부 Oracle DB에 내장된 데이터 타입들이다.

Types	Description	Size
VARCHAR2(size [BYTE])	가변 길이의 문자열을 저장하도록 한다.	1 byte ~ 4KB
NVARCHAR2(size)	가변 길이의 문자열을 저장하되, 저장소는 최대 저장소를 가지고 저장되도록 한다.	1 byte ~ 4000 byte
NUMBER[ (p [, s]) ]	p는 자리수 s는 소수점 ~이하까지 표현한다는 뜻이다. 이때, 소수점 이하도 자리수로 포함이 되기 때문에 number(4,2)는 123.45를 저장할 수 없다. 또한, 23.345를 저장할 때는 초과한 소수점 자리수에 대해 자동으로 반올림을 한다. p : (from 1 to 38) s : (from -84 to 127)	1 byte ~ 22 byte
FLOAT[ (p) ]	NUMBER 사용을 권장한다. (정확하지 않음) p : (from 1 to 126)	1 byte ~ 22 byte
LONG	호환성을 위한 길이가 2 Gigabyte인 문자열 데이터	2^31 -1 byte
DATE	기원전 4712년 1월 1일 부터 9999년 12월 31일 까지의 날짜를 표현	7 byte
BINARY_FLOAT	32 bit의 부동 소수점 숫자 표현	4 byte
BINARY_DOUBLE	64 bit의 부동 소수점 숫자 표현	8 byte
TIMESTAMP [(fractional_seconds_precision)]	년, 월, 일, 시, 분, 초를 표함하는 데이터	7 byte ~ 11 byte
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE	timestamp에서 timezone_hour, timezone_minute가 포함된 데이터 저장	13 byte
INTERVAL YEAR [(year_precision)] TO MONTH	년, 월 기간 데이터를 저장	5 byte
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]	일, 시간, 분, 초에 대한 기간 데이터를 저장	11 byte
RAW(size)	raw binary data를 저장	~ 2000 byte
LONG RAW	raw binary data	~ 2 GB
ROWID	64 크기의 문자열의 기본적인 고유 주소 저장
UROWID[(size)]
CHAR	고정된 크기의 문자 데이터들을 저장	1 byte ~ 2000 byte
NCHAR	위와 동일하지만, AL16UTF16의 인코딩은 두배, UTF8의 인코딩은 3배의 문자열 크기를 가짐	1 byte ~ 2000 byte
CLOB	single-byte와 multibyte를 포함하는 대형 문자열 객체를 저장	~ (4GB-1)*(database block size)
NCLOB	위와 추가하여 Unicode 문자열도 포함하게 된다.	~ (4GB-1)*(database block size)
BLOB	이미지, 사운드 파일, 비디오 클립 등의 Binary 데이터를 저장할 때 사용된다. LOB 데이터 타입중 하나이다.	~ 4GB
BFILE	외부 데이터베이스에 저장된 큰 바이너리 데이터를 참조를 저장한다.	~ 4GB

 

TO_DATE()

 date 데이터 타입은 데이터로 to_date를 사용해 넣을 수 있는데, to_date의 용법을 알아보고자 한다.

-- to_date(문자열, 날짜형식)
select to_date('2000-01-01', 'YYYY-MM-DD') from dual;

 to_date에서 문자열이 날짜형식과 같지 않다면, (ORA-01861)의 오류가 발생한다.  여기서 날짜 형식은 다양하게 있을 수 있는데, 다음과 같다.

• YYYY/RRRR: 4자리 연도
• YY/RR: 2자리 연도
• MM: 2자리 월
• MON: 3자리 월 이름(영문 축약어)
• MONTH: 월 이름(영문)
• DD: 2자리 일
• DDTH: 2자리 일(TH)
• DY: 3자리 요일 이름(영문 축약어)
• DAY: 요일 이름(영문)
• HH24: 2자리 시간(24시간)
• HH: 2자리 시간
• MI: 2자리 분
• SS: 2자리 초
• AM, A.M., PM, P.M.: 오전/오후 지시어

 

Constraints

• PRIMARY KEY : 기본키로 사용
• FOREIGN KEY : 외래키로 사용
• UNIQUE : 이미 가지고 있는 데이터는 안받음
• CHECK : 조건문을 거쳐 참인 데이터만 받음
• NOT NULL : NULL 허용안됨
• GENERATED BY DEFAULT AS IDENTITY : number이나 varchar2에 대해 시작 값과 증분 값을 지정시켜 새로운 행이 삽입될 때마다 연속된 일련번호가 생성되어 자동으로 집어넣는다. (하지만 varchar2에 대해서는 해당 제약조건은 성능을 떨어뜨릴 수 있다.)

constraint 절을 통해 다음과 같이 제약조건을 넣을 수도 있다.

CREATE TABLE employees (
  employee_id NUMBER(6),
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  department_id NUMBER(6),
  CONSTRAINT pk_employee_id PRIMARY KEY (employee_id),
  CONSTRAINT fk_department_id
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

이렇게 CONSTRAINT (NAME) (CONSTRAINT) 를 넣으면 다음과 같은 장점을 얻을 수 있다.

1. 가독성: 제약조건의 의도를 명시
2. 유지 보수성: 
3. 충돌 방지: 여러 제약조건들을 통해 충돌을 방지
4. 디버깅: 오류가 발생했을때, 오류 메세지에 뜨게 되어 수월한 디버깅을 할 수 있다.

 

Examples

Ex 1)

자동으로 번호를 매겨주는 제약조건(constraint)

create table persons(
    person_id number generated by default as identity,
    first_name varchar2(50) not null,
    last_name varchar2(50) not null,
    constraint pk_person_id primary key(person_id)
);

 

Ex 2)

varchar2

create table ex1 (
    test1 varchar2(1) not null,
    test2 varchar2(1 char) not null,
    test3 varchar2(1 byte) not null
);

insert into ex1(test1, test2, test3) values ('각', '각', '각');
insert into ex1(test1, test2, test3) values ('가', '가', '가');
insert into ex1(test1, test2, test3) values ('ㄱ', 'ㄱ', 'ㄱ');
insert into ex1(test1, test2, test3) values ('s', 's', 's');
insert into ex1(test1, test2, test3) values ('ss', 'ss', 'ss');


-- ORA-12899: value too large for column "SQL_TJPLOGLJLTZNCJXYFLJRPXBTM"."EX1"."TEST1" (actual: 3, maximum: 1) ORA-06512: at "SYS.DBMS_SQL", line 1721


-- ORA-12899: value too large for column "SQL_TJPLOGLJLTZNCJXYFLJRPXBTM"."EX1"."TEST1" (actual: 3, maximum: 1) ORA-06512: at "SYS.DBMS_SQL", line 1721


-- ORA-12899: value too large for column "SQL_TJPLOGLJLTZNCJXYFLJRPXBTM"."EX1"."TEST1" (actual: 3, maximum: 1) ORA-06512: at "SYS.DBMS_SQL", line 1721


-- 1 row(s) inserted.

-- ORA-12899: value too large for column "SQL_TJPLOGLJLTZNCJXYFLJRPXBTM"."EX1"."TEST1" (actual: 2, maximum: 1) ORA-06512: at "SYS.DBMS_SQL", line 1721

 

Ex 3)

기본값 제약조건 추가하기

create table employee (
    pk not null generated by default as identity
    name varchar(10) not null
    salary number(15) default 2000000
    hireDate date default to_date('2000-12-06', 'YYYY-MM-DD')
);

 

Ex 4)

date, to_date, to_char(날짜, 숫자 등을 문자로 변환하는 함수) 써보기

drop table employ;

create table employ (
    id number(5) generated by default as identity,
    firstName varchar2(10) not null,
    lastName varchar2(20) not null,
    salary number(15) default 50000000,
    hireDate date default to_date('2000-01-01', 'YYYY-MM-DD'),

    primary key(id)
);

insert into employ(firstName, lastName, hireDate)
    values('hong', 'gildong', to_date('2020-12-03', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('I', 'U', to_date('2019/03/23', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('p', 'sy', to_date('12.15.2023', 'MM.DD.YYYY'));
insert into employ(firstName, lastName, hireDate)
    values('black', 'pink', to_date('13-08-19', 'DD-MM-YY'));
insert into employ(firstName, lastName, hireDate)
    values('gwon', 'jina', to_date('23/10/23', 'YY/MM/DD'));

insert into employ(firstName, lastName, hireDate)
    values('baek', 'a', to_date('2018-11-30', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('ja', 'va', to_date('2010-01-01', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('py', 'thon', to_date('2015/06/30', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('park', 'hyosin', to_date('2013-05-05', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('b', 'ts', to_date('2012.12.31', 'YYYY.MM.DD'));

insert into employ(firstName, lastName, hireDate)
    values('c', 'lang', to_date('2018/02/10', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('a', 'bc', to_date('2010/01/01', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('d', 'ef', to_date('2011/11/11', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('g', 'hi', to_date('2022/03/19', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('jk', 'lmn', to_date('2022/03/19', 'YYYY/MM/DD'));


select
    hireDate as 날짜,
    to_char(hireDate, 'Mon') as hire_Mon,
    to_char(hireDate, 'YYYY') as hire_year,
    to_char(hireDate, 'MM') as hire_month
    from employ;

 

Ex 5)

check 제약조건 사용해보기

drop table employ;

create table employ (
    id number(5) generated by default as identity,
    firstName varchar2(10) not null,
    lastName varchar2(20) not null,
    salary number(15) default 50000000,
    hireDate date default to_date('2000-01-01', 'YYYY-MM-DD'),
    primary key(id),
    constraint hireDateCheck check(hireDate between to_date('2000-01-01', 'YYYY-MM-DD') and to_date('2020-12-31', 'YYYY-MM-DD')),
    constraint firstNameCheck unique(firstName)
);

insert into employ(firstName, lastName, hireDate)
    values('hong', 'gildong', to_date('2020-12-03', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('I', 'U', to_date('2019/03/23', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('p', 'sy', to_date('12.15.2023', 'MM.DD.YYYY'));
insert into employ(firstName, lastName, hireDate)
    values('black', 'pink', to_date('13-08-19', 'DD-MM-YY'));
insert into employ(firstName, lastName, hireDate)
    values('gwon', 'jina', to_date('23/10/23', 'YY/MM/DD'));

insert into employ(firstName, lastName, hireDate)
    values('baek', 'a', to_date('2018-11-30', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('ja', 'va', to_date('2010-01-01', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('py', 'thon', to_date('2015/06/30', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('park', 'hyosin', to_date('2013-05-05', 'YYYY-MM-DD'));
insert into employ(firstName, lastName, hireDate)
    values('b', 'ts', to_date('2012.12.31', 'YYYY.MM.DD'));

insert into employ(firstName, lastName, hireDate)
    values('c', 'lang', to_date('2018/02/10', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('a', 'bc', to_date('2010/01/01', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('d', 'ef', to_date('2011/11/11', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('g', 'hi', to_date('2022/03/19', 'YYYY/MM/DD'));
insert into employ(firstName, lastName, hireDate)
    values('jk', 'lmn', to_date('2022/03/19', 'YYYY/MM/DD'));


select
    hireDate as 날짜,
    to_char(hireDate, 'Mon') as hire_Mon,
    to_char(hireDate, 'YYYY') as hire_year,
    to_char(hireDate, 'MM') as hire_month
    from employ;


-- ORA-02290: check constraint (SQL_YZQAMSWFLXPMKSUUXVHYNJSXU.HIREDATECHECK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721


-- 1 row(s) inserted.

-- ORA-02290: check constraint (SQL_YZQAMSWFLXPMKSUUXVHYNJSXU.HIREDATECHECK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721


-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- 1 row(s) inserted.

-- ORA-02290: check constraint (SQL_YZQAMSWFLXPMKSUUXVHYNJSXU.HIREDATECHECK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721


-- ORA-02290: check constraint (SQL_YZQAMSWFLXPMKSUUXVHYNJSXU.HIREDATECHECK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721


-- Result Set 2
-- 날짜	HIRE_MON	HIRE_YEAR	HIRE_MONTH
-- 03-DEC-20	Dec	2020	12
-- 23-MAR-19	Mar	2019	03
-- 13-AUG-19	Aug	2019	08
-- 30-NOV-18	Nov	2018	11
-- 01-JAN-10	Jan	2010	01
-- 30-JUN-15	Jun	2015	06
-- 05-MAY-13	May	2013	05
-- 31-DEC-12	Dec	2012	12
-- 10-FEB-18	Feb	2018	02
-- 01-JAN-10	Jan	2010	01
-- 11-NOV-11	Nov	2011	11
-- 13-AUG-19	Aug	2019	08
-- 30-NOV-18	Nov	2018	11
-- 01-JAN-10	Jan	2010	01
-- 30-JUN-15	Jun	2015	06
-- 05-MAY-13	May	2013	05
-- 31-DEC-12	Dec	2012	12
-- 10-FEB-18	Feb	2018	02
-- 01-JAN-10	Jan	2010	01
-- 11-NOV-11	Nov	2011	11
-- 13-AUG-19	Aug	2019	08
-- 30-NOV-18	Nov	2018	11
-- 01-JAN-10	Jan	2010	01
-- 30-JUN-15	Jun	2015	06
-- 05-MAY-13	May	2013	05
-- 31-DEC-12	Dec	2012	12
-- 10-FEB-18	Feb	2018	02
-- 01-JAN-10	Jan	2010	01
-- 11-NOV-11	Nov	2011	11