[DB] 테이블 생성, 변경, 제거

1. 테이블 생성 (CREAT TABLE)

데이터 베이스에서 테이블을 생성하기 위해서는 테이블의 이름 및 각 열에 대한 이름, 타입을 지정해 줘야한다. 그리고 선택사항으로 데이터를 반드시 넣어야 하는지, 범위를 어떻게 할지에 대한 내용에 대해서도 지정해줄 수 있다.

 

테이블 생성에 대한 코드 구조는 다음과 같다.

CREATE TABLE 테이블명 (
   열이름1 데이터타입 [제약조건],
   열이름2 데이터타입 [제약조건],
   ...
);

 

-> CREATE TABLE 명령문을 통해 테이블을 생성했다.

-> 테이블명을 먼저 적어주고

-> 괄호 안에 내가 사용할 열에 대한 이름, 타입, 제약조건에 대해 명시해준다.

 

1-1. 데이터 타입 (Data Type)

데이터 타입은 테이블에서 각 열이 가질 수 있는 값의 종류와 크기를 정의한다.

 

이때 적절한 데이터 타입을 선택하는 것이 데이터 무결성이나 저장 공간 절약, 성능 최적화에 중요한 역할을 하기 때문에 신중하게 결정하면 좋을 것 같다.

 

추가로 데이터 형식이 RDBMS 마다 다르기 때문에 각 RDBMS별로 최적화된 데이터 형식을 찾아서 적용할 필요가 있다.

 

내가 작성하는 부분은 MySQL에서 사용되는 데이터 타입이다.

정수형(Integer)

타입	설명	저장 크기	값의 범위
TINYINT	작은 정수, 주로 플래그 값(0/1) 저장에 사용	1 바이트	-128 ~ 127
SMALLINT	비교적 작은 정수	2 바이트	-32,768 ~ 32,767
MEDIUMINT	중간 크기 정수	3 바이트	-8,388,608 ~ 8,388,607
INT	일반적인 정수	4 바이트	-2,147,483,648 ~ 2,147,483,647
BIGINT	매우 큰 정수	8 바이트	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807

 

부동 소수점형(Floating-Point)

타입	설명	저장 크기	유효 숫자
FLOAT	단정밀도 부동 소수점 (정밀도 낮음)	4 바이트	약 7자리
DOUBLE	배정밀도 부동 소수점 (정밀도 높음)	8 바이트	약 15자리
DECIMAL	고정 소수점 (정확한 값 저장)	지정 크기	지정된 정밀도 및 규모

문자열(String)

타입	설명	최대 길이
CHAR(n)	고정된 길이의 문자열. 비어 있는 부분은 공백으로 채워짐.	0 ~ 255
VARCHAR(n)	길이가 가변적인 문자열. 필요에 따라 저장 공간을 절약.	65,535
TEXT	비교적 큰 텍스트 저장	65,535
MEDIUMTEXT	더 큰 텍스트 저장	16,777,215
LONGTEXT	아주 큰 텍스트 저장	4,294,967,295

날짜 및 시간

타입	설명	형식
DATE	날짜 저장	YYYY-MM-DD
TIME	시간 저장	HH:MI:SS
DATETIME	날짜와 시간 모두 저장	YYYY-MM-DD HH:MI:SS
TIMESTAMP	자동으로 현재 시간 저장	YYYY-MM-DD HH:MI:SS
YEAR	연도 저장 (4자리 또는 2자리)	YYYY 또는 YY

논리

타입	설명
BOOLEAN	TRUE 또는 FALSE 값 저장.
BIT	0 또는 1을 저장. (BIT(1))

바이너리

타입	설명	최대길이
BINARY(n)	고정된 길이의 바이너리 데이터	255 바이트
VARBINARY(n)	가변 길이의 바이너리 데이터	65,535 바이트
BLOB	이진 대용량 객체 (이미지, 파일 등)	65,535 바이트
MEDIUMBLOB	큰 바이너리 데이터 저장	16,777,215 바이트
LONGBLOB	아주 큰 바이너리 데이터 저장	4,294,967,295 바이트

 

1-2. 제약 조건

NULL

NULL이라는 값은 단순히 알 수 없는 값의 의미이다.

 

만약 열에 NOT NULL 제약조건이 있다면, 해당 열 값이 NULL을 가질 수 없기 때문에 해당 열에는 항상 값을 입력해줘야 한다.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary INT
);

 

-> 이때 first_name과 last_name은 반드시 값이 있어야 한다.

 

UNIQUE

열의 모든 값이 중복을 허용하지 않고, NULL 값은 허용이 되는데 같은 열에서 여러 개의 NULL 값은 중복으로 간주되지 않는다.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

 

-> sku에는 UNIQUE 제약 조건을 통해 같은 값이 올 수 없도록 했다.

 

기본 키(PRIMARY KEY)

테이블에 저장된 데이터 행들을 서로 구분하기 위해 설정한다. 이전에 정리했던 내용대로 유일성과 최소성을 만족하며, NULL을 허용하지 않는다!

 

그렇기에 PRIMARY KEY는 자동으로 UNIQUE와 NOT NULL이 포함된다.

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NULL
    email VARCHAR(100) NULL
);

 

-> user_id가 PRIMARY KEY로 설정했고, AUTO_INCREMENT를 통해 자동으로 증가 하는 기능을 추가했다.

외래 키(Foregin Key)

외래 키 제약 조건을 통해 두 테이블에 관계를 정의할 수 있다.

 

두 테이블에 관계에 있어서,

다른 테이블의 열 값을 참조하는 테이블 -> 자식 테이블

이때 참조하는 자식 테이블의 열 -> 외래 키

자식 테이블에 의해 참조되는 테이블 -> 부모 테이블

자식 테이블에서 참조하는 부모 테이블의 열 -> 참조 키

 

외래 키 제약 조건은 다음과 같다.

 

* 조건

1) 부모 테이블의 참조 키는 기본 키 또는 UNIQUE 제약조건에 있는 열이거나 고유 인덱스를 가져야 한다.

2) 자식 테이블의 외래 키는 부모 테이블의 참조 키와 데이터 형식이 동일해야 한다.

3) 두 테이블 모두 같은 서버, 같은 데이터베이스 내에 저장되어 있어야 한다.

CREATE TABLE 자식테이블 (
    열1 데이터타입,
    열2 데이터타입,
    부모_키 데이터타입,
    FOREIGN KEY (부모_키) REFERENCES 부모테이블(부모_키)
);

DEFAULT

열에 값을 지정하지 않으면 미리 정의한 기본값이 자동으로 저장되도록 한다.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'Pending'
);

 

-> order_date에 값이 입력되지 않으면, 현재 날짜가 자동으로 입력되어 저장된다.

-> status도 마찬가지로 'Pending'이라는 값이 저장된다.

 

CHECK

열 값이 특정한 조건을 만족하는 경우에만 저장을 할 수 있도록 한다. 이때, 비교 연산자와 논리 연산자를 사용할 수 있다.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(10, 2) CHECK (balance >= 0)
);

 

-> balance 열에 음수 값이 저장되지 않도록 >= 라는 비교 연산자를 통해 조건을 걸어두었다.

 

2. 테이블 구조 변경

테이블 구조 변경은 열의 추가, 삭제, 변경을 의미한다.

2-1. 열 추가

테이블에 새로운 열을 추가할 때 사용한다.

ALTER TABLE 테이블명
ADD 열이름 데이터타입 [제약조건];

 

새로운 열을 추가할 때 기존에 있던 데이터의 행에는 이 열에 NULL 또는 기본값으로 채워지게 된다.

그렇기에 NOT NULL 제약 조건을 사용하면 에러가 발생할 수 있다.

 

ALTER TABLE employees 
ADD department_id INT DEFAULT 0;

 

추가적으로, 기존에 존재하던 행에 NULL이 아닌 다른 값을 할당하고 싶으면 WITH VALUES 옵션을 활용하면 된다.

ALTER TABLE employees 
ADD department_id INT NOT NULL DEFAULT 0 WITH VALUES;

 

-> 이 경우에는 모든 행에 0으로 값이 채워지기 때문에 NOT NULL 제약 조건을 사용할 수 있다.

 

2-2. 열 삭제

테이블에서 불필요하거나 사용하지 않는 열을 삭제할 수 있다.

ALTER TABLE 테이블명
DROP COLUMN 열이름;

 

열을 삭제하게 되면 그 열의 모든 데이터가 영구적으로 삭제된다.

 

추가적으로, 외래 키로 참조되는 열을 삭제할 때에는 오류가 발생하게 된다.

ALTER TABLE employees DROP FOREIGN KEY fk_department;  -- 외래 키 제약 조건 삭제
ALTER TABLE departments DROP COLUMN department_id;

 

이때, CONSTRAINT 키워드를 통해 설정했던 외래 키 제약 조건 이름을 먼저 삭제한뒤에 해당 열을 삭제하면 된다.

 

2-3. 열 변경

열의 데이터 타입이나 이름, 제약 조건을 추가하거나 제거할 때 열 변경을 한다.

ALTER TABLE 테이블명
MODIFY 열이름 새로운_데이터타입 [제약조건];

 

이때 주의할점이 변경 대상 열에 데이터가 없으면 상관없지만 데이터가 있으면 열 길이를 변경하는 것은 저장된 데이터들의 길이와 같거나 클 경우에만 가능하다.

 

3. 테이블 제거

데이터베이스에서 테이블을 삭제하는 작업은 영구적으로 데이터를 제거하는 것이기 때문에 신중하게 작업해야한다.

 

DROP TABLE 테이블명;

 

테이블 제거 옵션

옵션	설명
IF EXISTS	테이블이 존재할 때만 삭제합니다. (존재하지 않으면 오류를 피함)
CASCADE	외래 키 제약 조건을 포함하여 참조된 모든 테이블을 함께 삭제합니다.
RESTRICT	참조된 테이블이 있으면 삭제하지 않습니다. (기본 옵션)

 

-> 기본 옵션이 RESTRICT로 삭제하려는 테이블이 참조되고 있다면, 참조하는 테이블을 먼저 삭제한 뒤에 해야 한다.

-> 아니면 CASCADE 옵션을 사용하는 방법이 있다