Introduce
본 문서는 2022년 3월 17일 에 작성되었습니다.
DDL(Data Definition Language, 데이터 정의어) 는 쉽게 생각하면,
데이터 베이스에서 사용할 데이터의 형태를 정의 하는 언어라고 생각할 수 있습니다.
이러한 데이터 정의어를 사용하기 위해서는
데이터 모델링에 대한 이론 과 Domain 에 대한 이해 가 필요하다고 생각합니다.
해당 문서에서는 위 둘에 대한 간단한 개념과 DDL Syntax 에 대한 내용을 포함하고 있습니다.
Thoery
데이터 정의어에 대해서 공부하기 전에,
몇 가지 이론을 매우 간단하게 짚고 넘어가도록 하겠습니다.
- Entity & Instance
- Attribute & Domain
- Relationship
- Nomalization vs De-Nomalization
Entity & Instance
Entity 는 설계도 Instance 는 제작물 이라고 이해할 수 있습니다.
DDL 중 CREATE 를 이용하여 우리는 테이블(설계도) 를 만들 수 있습니다.
DML 중 INSERT 를 이용하여 우리는 테이블 에 맞는 로우 데이터(제작물) 을 만들 수 있습니다.
Attribute & Domain
Attribute 는 설계도 를 제작 시, 포함할 부품들 이라고 이해할 수 있습니다.
Domain 은 이러한 부품들에 중량이나 수 등의 조건을 부여하는 것으로 이해할 수 있습니다.
Relationship
Relationship 은 각각의 Entity 들의 관계의 유무를 따지고 있습니다.
즉, 밀가루를 이용해서 빵을 만든다면,
2개의 Entity 가 존재하며 빵은 밀가루를 필수적으로 가져야 함 을 알 수 있습니다.
Nomalization & De-Nomalization
Nomalization 은 공간 및 성능의 효율성을 이루기 위해서, 하나의 Entity 를 여러 가지 이유로 여러 Table 로 쪼개는 것 등을 의미합니다.
1차, 2차, 3차, 보이스-코드, 4차 정규화 등이 존재하며 일반적으로 3차 정규화까지만 실행해도 충분합니다.
단, 해당 내용은 본 시리즈의 목적을 벗어나는 관계로 별도로 서술하지 않고 있습니다.
De-Nomalization 은 통계 데이터 등의 집계 함수로 인한 성능 저하 를 막기 위해서, 미리미리 계산을 해놓은 정보를 모아 두는 것 등을 의미합니다.
Domain
위에서 언급한 이론 중에서 Domain 은,
하나의 칼럼이 가질 수 있는 자료형 과 크기 제한을 포함한 개념입니다.
- Data Type
- Constarints
DDL 부분을 제대로 설명하기 위해서, 해당 부분은 조금 더 알아볼 필요가 있습니다.
Data Type
하나의 테이블은 하나 이상의 칼럼을 가지게 됩니다.
하나의 칼럼은 최소한 자료형에 대한 정보를 명시해줘야 합니다.
여기서 자료형의 종류는 다음의 포스트를 참고해주세요.
모두 암기할 필요는 없습니다.
Constraints
하나의 칼럼은 추가적으로 제약 조건 을 명시할 수 있습니다.
이러한 제약 조건은 크게 6가지가 존재합니다.
| 이름 | 명령어 | 설명 및 예시 |
|---|---|---|
| 기본키 | PRIMARY KEY | CONSTRAINT 테이블명 PRIMARY KEY (칼럼명) CONSTARINT 테이블명 PRIMARY KEY (칼럼명, ... ) - 자동으로 UNIQUE, NOT NULL 의 제약조건이 생긴다. - 자동으로 인덱스가 생긴다. |
| 외래키 | FOREIGN KEY | CONSTRAINT 테이블명 FOREIGN KEY 테이블명 (칼럼명) - 외래키 참조 시, 외래키 칼럼명은 변경이 불가능해 진다. - 외래키 참조 시, 실제로 참조된 테이블은 삭제가 불가능해 진다. |
| 고유키 | UNIQUE | 칼럼명 자료형 UNIQUE - 1개만 존재해야 한다는 특성을 만족하면, NULL 도 넣을 수 있다. |
| 기본값 | DEFAULT | 칼럼명 자료형 DEFAULT 기본값 - null 값이 들어올 경우 대신 기본값을 넣어준다. |
| 필수값 | NOT NULL | 칼럼명 자료형 NOT NULL - null 값이 들어올 경우 에러를 일으킨다. - 기본값이 존재한다면, NOT NULL 이 들어올 수 없으므로 무의미해진다. |
| 제한범위 | CHECK | 칼럼면 자료형 CHECK 조건식 - 제한범위 외의 값이 들어올 경우 에러를 일으킨다. |
DDL
DDL 을 공부하기 이전에 필요한 모든 기본 개념을 공부하였습니다.
이제 우리는 DDL 을 통해서 테이블을 만들고 변경하는 등의 작업을 할 수 있습니다.
CREATE
CREATE 는 테이블을 생성하기 위한 명령어입니다.
// 기본 문법
CREATE 테이블 명 (
칼럼명 자료형 도메인,
칼럼명 자료형 도메인,
칼럼명 자료형 도메인
);ALTER
ALTER 는 생성된 테이블의 칼럼 옵션을 변경하기 위한 명령어입니다.
ALTER TABLE 테이블 명
추가적인 명령어추가적인 명령어는 다음 등이 존재하며,
해당 명령어를 적용할 범위인 ALTER TABLE 테이블명 뒤에 이어서 작성하면 됩니다.
-- 칼럼 추가 및 제거
ADD 칼럼명 자료형
DROP COLUMN 칼럼명
-- 칼럼 속성 및 자료형 변경
ALTER COLUMN 칼럼명 자료형 도메인
-- 기본키 및 외래키 설정
ADD PRIMARY KEY (칼럼명)
ADD FOREIGN KEY (칼럼명) REFERENCES 테이블명 (칼럼명)RENAME
RENAME 은 테이블명을 변경하기 위한 명령어 입니다.
-- 1개의 테이블명 변경
RENAME 테이블명 TO 변경할 테이블명;
-- 복수의 테이블명 변경
RENAME 테이블명 TO 변경할 테이블명,
테이블명 TO 변경할 테이블명,
테이블명 TO 변경할 테이블명;또한 ALTER 를 이용하여 칼럼명도 변경할 수 있습니다.
-- 단수의 칼럼명 변경
ALTER TABLE 테이블명
RENAME COLUMN 칼럼명 TO 변경할 칼럼명;
-- 복수의 칼럼명 변경
ALTER TABLE 테이블명
RENAME COLUMN 칼럼명 TO 변경할 칼럼명,
RENAME COLUMN 칼럼명 TO 변경할 칼럼명,
RENAME COLUMN 칼럼명 TO 변경할 칼럼명;COMMENT
지금까지,
테이블 및 칼럼을 추가 및 수정하는 방법을 배웠습니다.
테이블의 삭제의 내용은 많고 주의할 점이 많기 때문에, COMMENT 부터 알아보기로 했습니다.
참고 자료 : Tstory - Jhnyang [Oracle, MySQL] Comment 코멘트 ...
-- 코멘트 추가 및 수정
COMMENT ON 테이블명 IS '코멘트';
COMMENT ON 테이블명.칼럼명 IS '코멘트';DROP & TRUNCATE
DROP 과 TRUNCATE 는 모두 정보를 지우는 효과를 가지고 있습니다.
하지만, 부수 효과 측면에서 다르기 떄문에 주의해서 사용해야 합니다.
참고 자료 : 예제로 배우는 오라클 11g
추가적으로 DML 에 DELETE 가 있습니다.
-- 테이블 내 정보 모두 삭제, 테이블도 삭제, 되돌릴 수 없음.
DROP TABLE 테이블명;
-- 테이블 내 정보 모두 삭제, 되돌릴 수 없음.
TRUNCATE TABLE 테이블명;시나리오
CREATE 시나리오
CREATE, Data Type, Domain 을 실습하기 위해서 CREATE 시나리오를 작성해보았습니다.
임의의 웹 서비스 A 에서는 매우 간단한 유저 정보만을 다루고자 했습니다.
- 아이디, 이름, 비밀번호, 상세정보, 직업정보
아이디, 이름, 비밀번호 는 각각 숫자, 문자, 문자이며 모두 필수값이여야 합니다.
상세정보 는 문자이며입니다.
직업정보 는 문자이며입니다.
웹 서비스의 Client, Server 에서 유효성 검사 구문이 있었지만,
요구사항서를 확인해보니 DB 쪽에도 안전성을 위한 DOMAIN 처리를 해달라고 적혀있었습니다.
유저 테이블을 만들기
CREATE user (
user_id SERIAL PRIMARY KEY,
-- CONSTAINT 구문을 사용해도 결과는 동일하다. --
-- PRIMARY KEY 는 기본적으로 UNIQUE, NOT NULL 이 포함된다. --
user_name TEXT NOT NULL,
user_pw TEXT NOT NULL,
user_description TEXT,
user_job_category TEXT,
);위와 같이 테이블을 만들었지만 2가지 문제점이 보였습니다.
- user_description 의 길이가 너무 커서 조회 시 성능 저하가 발생할 우려가 있다.
- user_job_category 에는 '안녕하세요' 와 같이 원하지 않는 값도 들어갈 수 있다.
이에 상세정보 테이블을 분리하는 정규화 과정 이 필요했습니다.
여기에는 user_description 과 user_job_category 를 넣고, 해당 테이블의 기본키 값을 user 테이블에 외래키 참조의 형태로 넣게 되었습니다.
상세정보 테이블을 만들기
CREATE user (
user_id SERIAL PRIMARY KEY,
user_name TEXT NOT NULL,
user_pw TEXT NOT NULL,
user_detailed_pk INT FOREIGN KEY user_detailed (user_detailed_pk)
);
CREATE user_detailed (
user_detailed_pk SERIAL PRIMARY KEY,
user_description TEXT,
user_job_category TEXT
);1번의 문제는 해결 되었으나, 2번이 남아있으므로 다음과 같이 user_jobs 테이블을 만들게 되었습니다.
상세정보 - 직업군 의 경우의 수를 제한해보자
CREATE user (
user_id SERIAL PRIMARY KEY,
user_name TEXT NOT NULL,
user_pw TEXT NOT NULL,
user_detailed_pk INT FOREIGN KEY user_detailed (user_detailed_pk)
);
CREATE user_detailed (
user_detailed_pk SERIAL PRIMARY KEY,
user_description TEXT,
user_job_category TEXT FOREIGN KEY user_job (user_job_category)
);
CREATE user_job (
user_job_category TEXT PRIMARY KEY
);위와 같이 테이블을 만들고,
아직 배우지 않은 DML 의 INSERT 를 이용해 user_job 에 '백수', '직장인', '프리랜서' 라는 값을 넣어두면,
자연스럽게 해당 값들 외의 값은 user_detailed 에 들어가지 못하게 만들어 지게 됩니다
ALTER 시나리오
임의의 웹 서비스 A 에서는 서비스 운영 도중 다음과 같은 문제를 마주했습니다.
- 특정한 사용자들의 아이디, 이름, 비밀번호, 상세정보 등의 길이가 너무 크거나 짧은 것
이러한 부분을 SQL 의 INSERT 구문에 반영할 수도 있지만,
요구사항서를 확인해보니 DB 쪽에도 DOMAIN 처리를 해달라고 적혀 있었습니다.
자세한 내용은 다음과 같았습니다.
- 아이디 10 자 이상 100 자 이하
- 유저이름 10 자 이상 100 자 이하
- 비밀번호 10 자 이상 300 자 이하 (암호화 되었기 때문에 길이가 길어졌다)
- 상세정보 1000 자 이하
ALTER TABLE user
ALTER COLUMN user_id VARCHAR2(100);
ALTER TABLE user
ALTER COLUMN user_name VARCAHR2(100);
ALTER TABLE user
ALTER COLUMN user_pw VARCAHR2(100);
ALTER TABLE user_detailed
ALTER COLUMN user_detailed VARCAHR2(1000);DOMAIN 시나리오
임의의 웹 서비스 A 에서는 서비스 운영 도중 새로운 요구사항서를 보내주었습니다.
해당 요구사항서에는 다양한 테이블에 제약조건을 추가해달라는 내용이 담겨 있습니다.
- user_detailed.user_job_category 에 기본값으로 '백수'를 줄 것.
ALTER TABLE user_detailed
ALTER COLUMN user_job_category TEXT DEFAULT '백수';