[기본] SQLD 2과목 1장 공부 정리 2 (DDL문에서 create table, constraint 설정, on delete cascade 옵션)

과목 Ⅱ. 데이터모델링의 이해

 

1장 SQL 기본

 

3절 DDL(Data Definition Language)

 

1. 테이블 생성

  : DB 사용을 위해서는 먼저 테이블을 생성해야함

 

※ SQL 구문을 적을 때 대문자 소문자 구분은 필요없음

 

※ 테이블명으로 무조건 문자가 먼저 오고 뒤에 숫자, _ $, #까지 허용

 

<테이블 관리 SQL문>

• Create Table : 새로운 테이블 생성 (기본키, 외래키, 제약사항(Constraint) 설정 가능)
• Alter Table : 생성된 테이블 변경 (칼럼 추가, 변경, 삭제, 기본키, 외래키 설정도 가능)
• Drop Table : 해당 테이블 삭제 (데이터 구조 & 저장된 데이터 모두 삭제)

---

1-1) 기본적인 테이블 생성문

 

테이블 만드는 데 한참동안 애를 먹다가 뒤늦게야 알아냈다.

 

공부하고 있는 책(이기적 출판사-SQLD 이론서+기출문제)은 Oracle을 기준으로 하는데 나는 MySQL로 쿼리를 실행하려다 보니 쿼리 적용이 하나도 안됐다.

 

그 원인을 찾아보니 두 DBMS에서 사용하는 데이터 타입이 달랐다...

 

이걸 몰라서 진짜 계속 적용 못하고 헤매고 있었다..

 

SQLD 기출을 보면 Oracle을 기준으로 출제되는 성향이 강해서 학습은 Oracle 기준으로 하되, 실습은 따로 적용해야겠다.

 

간단하게 세 가지 데이터 타입만 살펴보자면

데이터 타입	Oracle	MySQL
숫자형	number	int
가변형 문자	varchar2	varchar
날짜	DATE	datetime

※ Oracle에서 VARCHAR2(n)형은 가변문자로 입력한 길이만큼 저장되는 반면 CHAR(n)형은 n이라는 길이에 못 미치면 공백으로 채워넣는다.

 

※ 날짜형을 입력할 때는 문자형처럼 따옴표 사이에 입력해야 함

 

이제 테이블을 만들러 가보자.

#DB 변경
use thon;

#테이블 생성

Create Table EMP(
empno int(10) primary key,
ename varchar(20),
sal int(6)
);

#테이블 확인
desc emp;

EMP라는 테이블을 만들었다.

EMP라는 테이블을 만들었고 그 안에 empno, ename, sal이라는 칼럼을 만들었다.

 

여기서 int, varchar은 해당 칼럼에 지정해주는 데이터 타입이고 괄호 안의 숫자는 아마도 길이(바이트)를 의미할 것이다.

 

그리고 primary key는 empno라는 칼럼을 EMP테이블의 기본키로 설정한다는 의미다.

 

DESC로 만들어준 테이블의 칼럼과 데이터 타입, Null 여부, Key, Default 옵션을 확인해 줄 수 있다.

---

1-2) 테이블 생성하면서 제약조건 설정

제약조건	설명
PRIMARY KEY	기본키 정의 (NULL 입력 불가)
FOREIGN KEY	외래키 정의
UNIQUE KEY	고유키 정의 (NULL 입력 가능)
NOT NULL	NULL 값 입력 금지
CHECK	입력할 수 있는 값의 범위 제한

제약조건인 constraint를 집어넣어 기본키나 외래키 설정, cascade 옵션 등을 설정해줄 수 있다.

 

※ CONSTRAINT 옵션에서 PRIMARY KEY는 테이블 당 하나만 가능 / FOREIGN KEY는 테이블 당 여러개 가능

※ CONSTRAINT 옵션에서 NOT NULL은 NULL 입력 방지 / UNIQUE는 테이블 내에 중복이 없도록 함 (단, NULL은 허용)

# Oracle 기준

Create Table EMP(
empno number(10),
ename varchar2(20),
sal number(10,2) default 0, #(10,2)에서 2는 소수점 둘째자리까지 저장하라는 의미
deptno varchar2(4) not null,
createdate date default sysdate, #createdate칼럼으로 date형식을 사용하는데 기본값으로 sysdate = 오늘날짜시분초 를 사용
constraint emppk primary key(empno) #constraint가 바로 제약조건으로 기본키로 empno칼럼을 지정한 것이고 그 기본키 이름을 'emppk'로 지정
);

 

# MySQL 기준

create table emp(
empno int(10), #Oracle의 number형은 MySQL에서 int
ename varchar(20),#Oracle의 varchar2형은 MySQL에서 varchar
sal int(10) default 0,
deptno varchar(4) not null,
date1 datetime default current_timestamp, #오라클의 sysdate는 MySQL에서 current_timestamp
constraint emppk primary key(empno) #제약조건은 동일
);

아까 만들었던 emp 테이블을 drop table문으로 먼저 삭제한 후 다시 만들어주었다.

아래 만들어진 emp 테이블을 보면 정상적으로 5개의 칼럼이 정확한 데이터 타입으로 입력되었다.

 

empno칼럼이 기본키로 설정되었고, Default 옵션도 입력한대로 적용되었다.

---

1-3) 외래키 지정

 

외래키 지정을 살펴보자

 

# Oracle 기준

#마스터 테이블 DEPT 생성
Create Table DEPT(
deptno varchar2(4) primary key,
deptname varchar2(20));

#EMP 테이블 생성
Create Table EMP(
empno number(10),
ename varchar2(20),
sal number(10,2) default 0,
deptno varchar2(4) not null,
createdate date default sysdate,
constraint emppk primary key(empno),
constratin deptfk foreign key(deptno) #DEPT테이블의 deptno 칼럼을 외래키로 지정
references dept(deptno) #dept테이블의 deptno 칼럼을 참조하겠다는 뜻
);

#MySQL 기준

#마스테 테이블 DEPT 생성
create table dept(
deptno varchar(4) primary key,
deptname varchar(20)
);

#EMP 테이블 생성
create table emp(
empno int(10),
ename varchar(20),
sal int(10) default 0,
deptno varchar(4) not null,
date1 datetime default current_timestamp,
constraint emppk primary key(empno),
constraint deptfk foreign key(deptno)
references dept(deptno)
);

제약조건 지정(기본키, 외래키 지정)에서 Oracle과 MySQL을 비교했을 때 차이는 없다.

마스터 테이블 DEPT 생성

deptno에 키로 MUL이 출력되었는데 Foreign key가 맞게된 것인가 모르겠다..

이렇게 테이블을 생성하면서 외래키를 지정해주기도 하지만 보통 생성과 동시에 외래키를 지정하지는 않는다.

 

대부분의 상황에서는 테이블을 변경하면서 ALTER문을 이용해 제약조건을 추가한다.

 

이 부분은 나중에 살펴보자.

---

1-3) CASCADE 옵션 사용

 

ON DELETE CASCADE 옵션은 자신이 참조하고 있는 테이블(마스터 테이블)의 데이터가 삭제 되면 자동으로 슬레이브 테이블(ex. EMP)의 해당 데이터도 삭제되는 옵션이다.

 

위에서 만든 두 테이블 dept와 emp에 on delete cascade 옵션을 적용해보자.

# Oracle 기준

Create Table DEPT(
deptno varchar2(4) primary key,
deptname varchar2(20));

Create Table EMP(
empno number(10),
ename varchar2(20),
sal number(10,2) default 0,
deptno varchar2(4) not null,
createdate date default sysdate,
constraint emppk primary key(empno),
constratin deptfk foreign key(deptno)
references dept(deptno)
on delete cascade
);

# MySQL 기준

create table dept(
deptno varchar(4) primary key,
deptname varchar(20)
);

create table emp(
empno int(10),
ename varchar(20),
sal int(10) default 0,
deptno varchar(4) not null,
date1 datetime default current_timestamp,
constraint emppk primary key(empno),
constraint deptfk foreign key(deptno)
references DEPT(deptno)
on delete cascade #on delete cascade 옵션을 적용
);

1-2와 동일한 테이블을 만들되 맨 하단에 on delete cascade문을 추가해주었다.

 

그리고 이 테이블에 데이터를 입력해보자.

# Oracle 기준

insert into dept values ('1000', '인사팀');
insert into dept values ('1001', '총무팀');

insert into emp values (100, '홍설이', 1000, '1000', sysdate);
insert into emp values (101, '고길동', 2000, '1001', sysdate);

# MySQL 기준

insert into dept values ('1000', '인사팀'); #dept 테이블 deptno = 1000 이고 deptname = 인사팀 이라는 데이터 입력
insert into dept values ('1001', '총무팀');

insert into emp values (100, '홍설이', 1000, '1000', current_timestamp()); #emp테이블에 칼럼에 맞는 데이터 입력
insert into emp values (101, '고길동', 2000, '1001', current_timestamp());

#emp 테이블 조회 (Oracle, MySQL 동일)

select * from emp;

EMP 테이블에 두 데이터가 입력되었다.

 

이제 ON DELETE CASCADE 옵션이 정상적으로 작동하는지 확인하기 위해 DEPT 테이블에서 deptno = 1000인 행을 삭제해보자.

 

# dept 테이블에서 deptno = 1000 인 데이터 삭제 (Oracle, MySQL 동일)

delete from dept where deptno = '1000';

#dept, emp 테이블 조회 (Oracle, MySQL 동일)

select * from dept;
select * from emp;

부서번호 1000 인사팀이 삭제되었다.

그리고 부서번호 1000에서 일하던 '홍설이'의 데이터도 완전히 삭제되었다.

ON DELETE CASCADE 옵션이 잘 작동되었다!

 

이 옵션을 활용하면 '참조 무결성'을 준수할 수 있다.

 

※ 참조 무결성 : 마스터 테이블에는 없는데 슬레이브 테이블에는 데이터가 있는 경우 참조 무결성이 위배되었다고 함.

 

※ ON DELETE SET NULL : 참조하는 칼럼이 삭제되면 NULL로 변경

---

테이블 생성까지만 봤는데 너무 느리게 공부하는 것 같아서 조금 속도를 높여봐야겠다.