[J STORY] SQLP - DDL(DATA DEFINTION LANGUAGE)

1. 데이터 유형

• 데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준
• 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정
• 선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 데이터베이스는 에러를 발생시킴
• 데이터 유형과 더불어 지정한 크기(SIZE)도 중요한 기능을 제공, 지정한 데이터의 크기를 넘어선 자료가 입력되는 상황에서 에러를 발생

• 데이터 유형(숫자 타입)
  • ANSI/ISO 기준에서는 NUMERIC Type의 하위 개념으로 NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION
  • SQL Server와 Sybase는 ANSI/ISO 기준의 하위 개념에 맞추어서 작은 정수형, 정수형, 큰 정수형, 실수형 등 여러 숫자 타입을 제공하고 있으며, 추가로 MONEY, SMALLMONEY 등의 숫자 타입도 가지고 있음
  • Oracle은 숫자형 타입에 대해서 NUMBER 한 가지 숫자 타입의 데이터 유형만 지원

벤더에서 ANSI/ISO 표준을 사용할 때는 기능을 중심으로 구현하므로, 일반적으로 표준과 다른(ex: NUMERIC → NUMBER, WINDOW FUNCTION → ANALYTIC/RANK FUNCTION) 용어를 사용하는 것은 현실적으로 허용이 된다.

 

데이터 유형

• 데이터 유형(문자열 타입)
  • CHAR, VARCHAR

• VARCHAR 유형
  • VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐
  • 길이가 다양한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합
  • CHAR 유형보다 작은 영역에 저장할 수 있음
  • VARCHAR 유형 문자열 비교에서 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단

• CHAR 유형
  • CHAR에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교
  • CHAR의 공백 채우기 비교에서는 우선 짧은 쪽 컬럼의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 하고 앞에서부터 한 문자씩 비교

예) CHAR 유형
'AA' = 'AA '

예) VARCHAR 유형
AA' ≠ 'AA '

• VARCHAR 유형 사용 예
  • 고정된 길이의 문자열을 가지지 않는 경우(팀명, 운동장 주소, 선수이름, 아이디, 이름, 이메일)

• CHAR 유형 사용 예
  • 고정된 길이의 문자열을 가지는 경우(주민등록번호, 코드성컬럼)

CHAR가 아닌 VARCHAR, NUMERIC 유형에서 정의한 길이나 자릿수의 의미는 해당 데이터 유형이 가질 수 있는 최대한의 한계값을 정의한 것이라고 보아야 함
문자열(CHAR와 VARCHAR)에 대한 최대 길이와 NUMBER 칼럼의 정밀도(Precision)를 지정하는 것은 테이블 설계시 반드시 고려해야 할 중요 요소

 

 

2. CREATE TABLE

테이블은 일정한 형식에 의해서 생성
테이블 생성을 위해서는 해당 테이블에 입력될 데이터를 정의하고, 정의한 데이터를 어떠한 데이터 유형으로 선언할 것인지를 결정

 

 가. 테이블과 칼럼 정의

  - 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정

  - 선수 테이블을 예로 들면 '선수ID' 칼럼이 기본키로 적당할 것이다.
  - 기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 만들어질 수 있다.
  - 테이블과 테이블 간에 정의된 관계는 기본키(PRIMARY KEY)와 외부키(FOREIGN KEY)를 활용해서 설정
선수 테이블에 선수의 소속팀 정보가 같이 존재한다고 가정하면, 특정 팀의 이름이 변경되었을 경우 그 팀에 소속된 선수 데이터를 일일이 찾아서 수정을 하거나, 또한 팀이 해체되었을 경우 선수 관련 정보까지 삭제되는 수정/삭제 이상(Anomaly) 현상이 발생할 수 있다.
이런 이상 현상을 방지하기 위해 팀 정보를 관리하는 팀 테이블을 별도로 분리해서 팀ID와 팀 이름을 저장하고, 선수 테이블에서는 팀ID를 외부키로 참조하게 한다.

 

 

CREATE TABLE

테이블 선언

다음은 테이블 생성 시에 주의해야 할 몇 가지 규칙이다.

• 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
• 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
• 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
• 테이블 이름을 지정하고 각 칼럼들은 괄호 "( )" 로 묶어 지정한다.
• 각 칼럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝난다.
• 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점 : 공통성 칼럼을 도메인으로 관리)
• 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
• 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
• 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다. - A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

 

 다. 제약조건(CONSTRAINT)

데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약
테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없지만, 이후에 ALTER TABLE을 이용해서 추가, 수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약 조건에 대한 충분한 검토가 있어야 한다.

제약조건 종류

 

NULL 의미
NULL(ASCII 코드 00번)은 공백(BLANK, ASCII 코드 32번)이나 숫자 0(ZERO, ASCII 48)과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다.
'NULL'은 '아직 정의되지 않은 미지의 값'이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.

 

DEFAULT 의미
데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값(DEFAULT)을 사전에 설정할 수 있다.
데이터 입력시 명시된 값을 지정하지 않은 경우에 NULL 값이 입력되고, DEFAULT 값을 정의했다면 해당 칼럼에 NULL 값이 입력되지 않고 사전에 정의된 기본 값이 자동으로 입력된다.

 

 

3. ALTER TABLE

  가. ADD COLUMN

다음은 기존 테이블에 필요한 칼럼을 추가하는 명령이다.

ALTER TABLE

새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.

 

 나. DROP COLUMN

• 테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능
• 한 번에 하나의 칼럼만 삭제 가능하며, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함
• 한 번 삭제된 칼럼은 복구가 불가능함

DROP COLUMN

 다. MODIFY COLUMN

• 칼럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 포함할 수 있음

MODIFY COLUMN

칼럼을 변경할 때는 몇 가지 사항을 고려해서 변경해야 한다.

• 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. 이는 기존의 데이터가 훼손될 수 있기 때문이다.
• 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.
• 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.
• 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
• 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

 라. RENAME COLUMN

아래는 테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에 유용하게 쓰일 수 있는 RENAME COLUMN 문구이다.

RENAME COLUMN

 

 마. DROP CONSTRAINT

테이블 생성 시 부여했던 제약조건을 삭제하는 명령어 형태는 다음과 같다.

DROP CONSTRAINT

 

 바. ADD CONSTRAINT

테이블 생성 시 제약조건을 적용하지 않았다면, 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.
다음은 특정 칼럼에 제약조건을 추가하는 명령어 형태이다.

ADD CONSTRAINT

 

4. RENAME TABLE

RENAME 명령어를 사용하여 테이블의 이름을 변경할 수 있다.

RENAME TABLE

 

5. DROP TABLE

다음은 불필요한 테이블을 삭제하는 명령이다.

DROP TABLE

• 테이블의 모든 데이터 및 구조를 삭제
• CASCADE CONSTRAINT 옵션은 해당 테이블에 참조되는 제약조건에 대해서도 삭제
• SQL Server에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에 참조하는 FOREIGN KEY 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 함

 

6. TRUNCATE TABLE

• 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제
• 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행

TRUNCATE TABLE

DROP TABLE의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없다.
반면 TRUNCATE TABLE의 경우는 테이블 구조는 그대로 유지한 체 데이터만 전부 삭제하는 기능이다.

TRUNCATE는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL로 분류하였다.
테이블에 있는 데이터를 삭제하는 명령어는 TRUNCATE TABLE 명령어 이외에도 다음 DML 절에서 살펴볼 DELETE 명령어가 있다.

그러나 DELETE와 TRUNCATE는 처리하는 방식 자체가 다르다. 테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다.
단, TRUNCATE TABLE의 경우 정상적인 복구가 불가능하므로 주의해야 한다.