[데브코스] 사용자-게시글 예제로 배우는 테이블 설계와 컬럼 속성, 제약조건

🗂️Board 스키마 생성

CREATE DATABASE Board;
USE Board

---

🗂️사용자 테이블 생성

 CREATE TABLE users (
    -> id INT NOT NULL AUTO_INCREMENT, // PRIMARY KEY 추가
    -> name VARCHAR(30) NOT NULL,
    -> job VARCHAR(100),
    -> birth DATE
    -> );

 

❌오류: Incorrect table definition

Incorrect table definition; there can be only one auto column and it must be defined as a key

AUTO_INCREMENT 속성을 가진 컬럼이 하나 이상이거나, PRIMARY KEY 또는 UNIQUE KEY로 설정되지 않은 컬럼에 AUTO_INCREMENT를 붙였을 때 발생하는 MySQL 오류이다.

 

id 컬럼이 AUTO_INCREMENT인데, PRIMARY KEY 또는 UNIQUE 제약 조건이 없기 때문에 발생하였다. id 컬럼에 PRIMARY KEY를 부여하여 문제를 해결하였다.

 

AUTO_INCREMENT

AUTO_INCREMENT는 새 레코드가 삽입될 때마다 자동으로 숫자를 증가시켜 고유한 값을 생성하는 MySQL의 속성

 

✅사용 조건:

• AUTO_INCREMENT는 INT 계열 숫자 타입에서만 사용 가능 (e.g., INT, BIGINT, SMALLINT)
• 반드시 PRIMARY KEY 또는 UNIQUE 제약 조건이 있어야 한다.
• 하나의 테이블에 딱 하나만 존재할 수 있다.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

id	name
1	Alice
2	Bob

 

아래처럼 시작값을 설정해 줄 수 있다.

ALTER TABLE users AUTO_INCREMENT = 100;

 

 

💡사용 시 주의사항:

• 중간 값이 삭제되어도, 자동 증가 값은 되돌아가지 않는다.
• 숫자 재사용을 원하면 수동으로 관리하거나 TRUNCATE로 전체 초기화 필요하다.

 

DESC

MySQL에서 테이블의 구조(스키마)를 확인할 때 사용하는 명령어

 

DESC를 사용하여 생성된 테이블 구조를 확인할 수 있다.

DESC users

 

제약 조건

제약 조건(Constraints)은 테이블에 저장되는 데이터의 무결성(정확성 + 일관성)을 보장하기 위한 규칙

PRIMARY KEY	각 행을 고유하게 식별. NULL 허용 ❌	id INT PRIMARY KEY
UNIQUE	고유한 값만 허용. 중복 ❌, NULL ✅	email VARCHAR(100) UNIQUE
NOT NULL	NULL 입력 ❌	name VARCHAR(50) NOT NULL
DEFAULT	값이 없을 때 자동으로 설정될 기본값	created_at DATETIME DEFAULT NOW()
CHECK	조건 만족 시만 삽입 허용	age INT CHECK (age >= 0)
FOREIGN KEY	다른 테이블의 키를 참조 (관계 설정)	user_id INT, FOREIGN KEY (user_id) REFERENCES users(id)

---

🗂️사용자 테이블 데이터 삽입

INSERT INTO users(name, job, birth) VALUES("gongu", "actor", "800123");

---

🗂️게시글 테이블 생성

CREATE TABLE posts (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> title VARCHAR(100) NOT NULL,
    -> content VARCHAR(2000),
    -> created_at TIMESTAMP DEFAULT NOW(),
    -> updated_at TIMESTAMP DEFAULT NOW()
    -> );

 

MySQL 날짜/시간 타입

DATE: 날짜만 저장(yyyy-mm-dd)
DATETIME: 날짜 + 시간(yyyy-mm-dd hh:mm:ss)
TIME: 시간만(hh:mm:ss)
TIMESTAMP: 시스템 시간대 정보에 맞게 일시를 저장(yyyy-mm-dd hh-mm-ss)

 

TIMESTMAP

TIMESTAMP는 MySQL에서 시스템 시간대를 기준으로 저장되며, 내부적으로는 UTC로 변환되어 보관되는 특별한 타입

 

🧭 TIMESTAMP의 시간 처리 방식

 

1. 저장 시: 로컬 시간 → UTC로 변환
MySQL 서버의 시스템 시간대를 기준으로 입력한 값은 자동으로 UTC로 변환되어 저장된다.

예시 (서버가 KST = UTC+9일 경우):
INSERT INTO logs (created_at) VALUES ('2025-05-02 12:00:00');

• 입력값: 2025-05-02 12:00:00 (KST)
• 실제 저장되는 값: 2025-05-02 03:00:00 (UTC)

 

2. 조회 시: UTC → 로컬 시간대로 변환되어 출력
저장된 UTC 값은 현재 세션의 시간대(time_zone)에 따라 로컬 시간으로 변환되어 보여진다.

→ 사용자 입장에선 로컬 시간으로 입력하고 로컬 시간으로 본 것처럼 보인다.

 

	DATETIME	TIMESTAMP
저장 방식	입력된 그대로 저장	내부적으로 UTC로 저장
시간대 영향	없음	있음 (변환됨)
권장 사용	절대 시간 기록 (생일, 예약일 등)	변화하는 시점 기록 (로그, 생성일 등)
표현 범위	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19

 

 

DEFAULT vs NOT NULL

DEFAULT와 NOT NULL은 열의 제약조건(Constraints) 중 기본값과 null 허용 여부를 제어하는 핵심 옵션

 

✅ NOT NULL

• 해당 컬럼에 NULL 값을 허용하지 않는다.
• 데이터를 삽입할 때 반드시 값이 있어야 한다.
• 누락된 값을 허용하지 않도록 하여 데이터 무결성(data integrity)을 보장한다.

✅ DEFAULT

• 값을 지정하지 않았을 때 자동으로 채워지는 기본값
• INSERT 시 해당 컬럼을 생략하면 이 값으로 자동 입력된다.
• NULL은 허용할 수도 있고 안 할 수도 있다. (→ NOT NULL과 함께 쓰면 명확함)

⭐ NOT NULL은 NULL 입력을 강제로 막는 제약 조건이기 때문에, 둘을 병행해 쓰면 더욱 안정적인 데이터 관리 가능!

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  views INT NOT NULL DEFAULT 0
);

---

🗂️게시글 테이블 삽입, 컬럼 추가, 수정

게시글 테이블에 데이터 삽입

INSERT INTO posts(title, content) VALUES ("title1", "content");

 

게시글 테이블에 수정일자 컬럼 추가

 ALTER TABLE posts MODIFY COLUMN updated_at DATETIME DEFAULT NOW() ON UPDATE NOW();

 

게시글 테이블 id=2 수정

 UPDATE posts SET content = "updated!" WHERE id = 2;

 

ALTER

이미 존재하는 테이블의 구조를 수정할 때 사용하는 SQL 명령어

 

ALTER TABLE 테이블명 작업내용;

형식으로 사용한다.

 

ALTER TABLE posts 
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

• MySQL에서 레코드가 업데이트될 때 자동으로 시간 갱신하도록 updated_at 컬럼을 수정한다.

 

컬럼 추가	ALTER TABLE posts ADD COLUMN views INT DEFAULT 0;
컬럼 삭제	ALTER TABLE posts DROP COLUMN views;
컬럼 이름 변경 (MySQL 8+)	ALTER TABLE posts RENAME COLUMN old_name TO new_name;
컬럼 자료형 변경	ALTER TABLE posts MODIFY COLUMN title VARCHAR(200);
기본값 변경	ALTER TABLE posts ALTER COLUMN views SET DEFAULT 100;
제약조건 추가	ALTER TABLE posts ADD CONSTRAINT ...
테이블 이름 변경	RENAME TABLE old_name TO new_name;

---

🗂️게시글 테이블에 작성자 컬럼 FK 추가 및 설정

 ALTER TABLE posts
    -> ADD COLUMN user_id INT;
    
ALTER TABLE posts
    -> ADD FOREIGN KEY(user_id)
    -> REFERENCES users(id);

 

Foreign Key

두 테이블 간의 관계를 설정하는 제약조건

데이터베이스에서 테이블 간 연관성을 표현하고, 데이터 무결성을 보장하는 데 사용한다.

• 다른 테이블의 기본 키(primary key)를 참조하는 열(column)이다.
• 한 테이블의 특정 열 값이 다른 테이블에 반드시 존재해야 한다는 제약을 설정한다.
• 주로 1:N 관계에서 사용된다.

---

 

🧩조인

두 개 이상의 테이블을 연결하여 데이터를 하나의 결과로 결합하는 SQL 명령어

 

SELECT * FROM posts LEFT
    -> JOIN users ON posts.user_id = users.id;

 

주로 테이블 간의 관계를 바탕으로 데이터를 결합하고, 여러 테이블에 분산된 정보를 조합하여 유용한 결과를 얻을 때 사용한다.

 

INNER JOIN

두 테이블에 공통된 데이터가 있을 경우에만 그 데이터를 결합하여 반환한다. 공통된 데이터가 없는 경우, 결과에 포함되지 않는다.

SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

 

 

OUTER JOIN

LEFT JOIN 
왼쪽 테이블(LEFT)의 모든 데이터를 포함하고, 오른쪽 테이블(RIGHT)에서 일치하는 값이 있을 경우 그 값을 결합한다. 일치하는 데이터가 없는 경우, 오른쪽 테이블의 컬럼은 NULL로 반환된다.

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

 

RIGHT JOIN
오른쪽 테이블(RIGHT)의 모든 데이터를 포함하고, 왼쪽 테이블(LEFT)에서 일치하는 값이 있을 경우 그 값을 결합한다. 일치하는 데이터가 없는 경우, 왼쪽 테이블의 컬럼은 NULL로 반환된다.

SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

 

FULL JOIN
LEFT JOIN과 RIGHT JOIN을 결합한 것으로, 두 테이블의 모든 데이터를 포함한다. 두 테이블에서 일치하는 데이터가 있을 경우 결합하고, 일치하지 않는 경우에는 NULL로 채운다.

-- MySQL에서는 UNION 사용
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

💡MySQL에서는 FULL OUTER JOIN이 지원되지 않지만, 다른 DBMS에서는 지원된다. MySQL에서는 이를 UNION으로 대체할 수 있다.

 

 

LEFT JOIN을 사용하여, users와 posts 테이블 조인을 수행한 결과는 다음과 같다.

---

이번 글에서는 사용자와 게시글이라는 아주 기본적인 테이블 구조를 다뤘지만, 그 안에 숨은 설계 포인트와 제약조건들이 생각보다 많다는 걸 새삼 느꼈다. AUTO_INCREMENT, DEFAULT, NOT NULL, TIMESTAMP 같은 속성들은 이미 익숙하다고 생각했지만, 공부하면 할수록 세부 동작과 설정 옵션들이 계속 나온다.

 

AUTO_INCREMENT만 해도 단순히 자동 증가가 아니라 증가 간격 설정, 락 모드에 따른 동작 차이, 다중 테이블에서의 충돌 회피 등 의외로 깊은 주제가 많았고, TIMESTAMP도 한 테이블에 하나만 자동 업데이트 가능하다든지, 시간대 설정과의 관계 등 실무에 영향을 주는 요소가 숨어 있었다.

 

아직 이 글에서는 다루지 않았지만, 나중에는 락 모드(Lock Mode)나 ID 증가값 튜닝, 트리거를 활용한 제약 보완 같은 내용도 정리해보고 싶다. 테이블 설계는 단순히 컬럼 나열이 아니라, 데이터 무결성과 성능을 함께 고민하는 작업이라는 걸 점점 체감하고 있다.