[SQLD]003. SQL 기본 두번째 공부. DDL, CREATE, ALTER, DROP

이번 포스팅에서는 SQL 기본 공부 중 DDL과 DML에 대해서 공부하는 과정을 포스팅하겠습니다.
앞선 포스팅에서 공부했지만 DDL은 관계형 데이터베이스의 구조를 정의하는 언어이고,
DML은 테이블에서 데이터를 입력, 수정, 삭제 조회하는 언어입니다.

DDL(Data Definition Language)

1. 테이블 생성

데이터베이스를 사용하기 위해서 가장 먼저 테이블을 생성해줘야 합니다.
테이블생성은 CREATE 문을 이용해서 생성하며, 생성된 테이블을 변경하기 위해서는 ALTER문을
생성된 테이블을 삭제하기 위해서는 DROP 문을 사용합니다.
각 구문에 대해 보다 자세히 설명하면 아래와 같습니다.

1) CREATE

새로운 테이블을 생성합니다.
테이블을 생성할 때 기본키, 외래키, 제약사항 등을 설정할 수 있습니다.

① CREATE TABLE EMPLOYEE라는 테이블을 생성하라는 의미이며, “( )” 괄호 안에 칼럼명과 칼럼의 데이터타입을

명시하여 테이블 내 속성을 생성할 수 있습니다.

*SQL 문의 마지막에는 “;” 세미콜론을 항상 붙여줍니다.

② 칼럼 : 테이블에 생성되는 칼럼 이름과 데이터 타입을 입력합니다. 칼럼 이름은 영문, 한글, 숫자 모두 가능합니다.
(SQL에서 칼럼 이름은 한글이 가능하지만 DB 종류에 따라 한글을 사용하지 못하는 경우가 많으며,

영문을 사용하는 것을 추천합니다.)

③ 데이터 타입 : NUMBER는 숫자형 타입, VARCHAR2는 가변 길이 문자열, CHAR는 고정된 크기 문자,

DATE는 날짜형 타입을 의미합니다. 칼럼의 타입을 정하면 해당 칼럼에는 그 타입에 맞는 값 만을 입력할 수 있습니다.
SALARY(급여) 칼럼의 경우, NUMBER(10,2)라는 데이터타입을 명시했는데, 이는 소수점 둘째 자리를 의미합니다.

④ 기본키 : 칼럼 옆에 PRIMARY KEY 입력을 통해 해당 칼럼이 기본키임을 지정합니다.
CONSTRAINT 를 사용하여 기본키(EMPNO)와 기본키의 이름(EMPPK)을 지정할 수 있습니다.
-> 나중에 다수 제약 조건을 비활성화하거나 삭제하는 등의 관리를 위해 제약조건에 이름을 지정해 줍니다.

만약, 두 개의 칼럼을 기본키로 지정하고자 한다면, 아래와 같이 입력해 주면 됩니다.

- CONSTRAINT EMPPK PRIMARY KEY(EMPNO, EMP_NAME)

기타) ORACLE DB에서 “SYSDATE”는 오늘의 날짜를 조회합니다. 이를 DEFAULT 옵션으로 지정해서,
컬럼을 추가할 경우, 추가하는 당일 날짜가 입력될 수 있도록 합니다.

⑤ 외래키 : 외래키 지정에 앞서, 마스터 테이블이 생성돼야 하며, 사원(EMPLOYEE) 테이블과

부서(DEPARTMENT) 테이블에서는 사원테이블에서 부서테이블의 부서 번호를 참조하기 때문에,

부서 테이블이 마스터 테이블이 됩니다.
마스터 테이블 생성 후, 사원 테이블 생성 시, 칼럼 옆에 FOREIGN KEY 입력을 통해 해당 칼럼이 외래키임을

지정합니다. 또한 기본키와 동일하게 CONSTRAINT를 이용 외래키의 명칭을 DEPTFK로 지정해 줍니다.

⑥ 마지막으로 CASCADE 옵션을 통해 참조무결성을 준수할 수 있도록 지정해 줍니다.

참조무결성이란 관계형 데이터베이스(RDB)에서 릴레이션(relation)은 참조할 수 없는

외래 키(foreign key)를 가져서는 안 된다는 조건을 말합니다.

만일 참조할 수 없는 외래 키값이 존재한다면 참조될 릴레이션에 실제로 존재하지도 않는 투플(tuple)을 참조한다는

의미가 되므로 모순이 발생하게 되기 때문입니다.

즉, 마스터 테이블인 부서 테이블에서 부서 번호(DEPT_NO)가 없는데, 이를 참조하고 있는

슬레이브 테이블(EMPLOYEE)에서 부서번호가 있는 경우 참조 무결성을 위배하게 되며,

이를 준수할 수 있도록 부여해 주는 옵션입니다.

CASCADE 옵션은 참조 관계가 있을 경우 참조되는 데이터를 자동으로 반영할 수 있도록 해주는 것으로

“ON DELETE CASCADE”를 통해 자신이 참조하고 있는 테이블의 데이터가 삭제되면

자동으로 자신도 삭제될 수 있도록 해줍니다.

아래 과정을 통해 CASCADE가 어떻게 적용되는지 이해할 수 있습니다.

가. 우선 마스터 테이블인 부서 테이블에 두 개 데이터를 입력해 주고 잘 입력되었는지 확인해 줍니다.

나. 다음으로 슬레이브 테이블인 직원정보 테이블에 직원정보 두 개 데이터를 입력해 줍니다.

다. 부서번호가 001인 부서를 부서테이블에서 삭제해 줍니다.

라. 부서테이블과 직원정보 테이블의 모든 데이터를 조회해 보면 부서테이블에 001 부서가 삭제되었고

이에 따라 직원정보에서 001 부서인 직원정보도 같이 삭제된 것을 확인해 볼 수 있습니다.

2) ALTER TABLE :

생성된 테이블을 변경하며, 칼럼을 추가하거나 변경, 삭제할 수 있습니다.
기본키를 설정하거나, 외래키를 설정할 수 있습니다.

① RENAME TO 옵션을 통해 테이블명을 변경할 수 있습니다.

② ADD 옵션을 통해 칼럼을 추가할 수 있습니다

③ MODIFY문을 이용해 칼럼의 데이터 타입, 데이터 길이, 제약 조건을 설정할 수 있습니다.
아래 코드와 같이 EMP_NAME의 데이터길이를 20에서 40으로 변경하고 NOT NULL 조건을 추가해 줄 수 있습니다.
주의할 사항으로 데이터 타입을 변경할 때 기존 데이터가 있는 경우 에러가 발생합니다.
즉, 아래와 같이 EMP_NAME의 데이터타입을 숫자로 변경하려는 시도를 할 경우 에러가 발생하는 것을 볼 수 있습니다.