MySQL에서 기본 키(PK)에 자동 증가(AUTO_INCREMENT) 속성을 부여하는 방법과 다양한 테이블 스키마 수정 기법을 살펴봅니다. 기본적으로 AUTO_INCREMENT는 1부터 시작하며 데이터가 삽입될 때마다 1씩 증가합니다.

1. 기존 테이블에 새로운 자동 증가 기본 키 열 추가

테이블이 이미 생성되어 있으나 기본 키가 없거나 자동 증가 속성이 없는 경우, 새로운 열을 추가하면서 기본 키로 설정할 수 있습니다. 이 경우 새로 추가된 열의 값은 기존 행에 대해 자동으로 채워집니다.

ALTER TABLE employee_records 
ADD COLUMN emp_id INT AUTO_INCREMENT PRIMARY KEY;

2. 기존 열을 자동 증가 기본 키로 변경

이미 존재하는 열(예: member_id)을 자동 증가 기본 키로 전환하려면 CHANGE 또는 MODIFY 구문을 사용합니다. 자동 증가 속성을 적용하려면 해당 열이 반드시 NOT NULL이어야 하며, 기본 키로 지정되어야 합니다.

ALTER TABLE member_accounts 
CHANGE member_id member_id INT NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (member_id);

3. 데이터 삽입 시 주의사항

자동 증가로 설정된 열은 INSERT 문에서 값을 명시적으로 지정할 필요가 없습니다. 데이터베이스 엔진이 자동으로 다음 순번을 할당합니다.

INSERT INTO member_accounts (login_name, password_hash) 
VALUES ('tech_user', 'hashed_pw_123');

4. 테이블 생성 시 자동 증가 설정

새로운 테이블을 설계할 때 초기 단계부터 자동 증가 기본 키를 정의하는 표준적인 방법입니다.

CREATE TABLE member_accounts (
    member_id INT NOT NULL AUTO_INCREMENT,
    login_name VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 유용한 DDL(데이터 정의어) 확장 기법

테이블 구조를 수정하고 인덱스나 제약 조건을 관리하는 데 자주 사용되는 ALTER TABLE 작업들입니다.

열(Column) 추가, 삭제 및 수정

-- 새로운 열 추가
ALTER TABLE project_tasks ADD COLUMN priority_level TINYINT DEFAULT 0 NOT NULL;

-- 기존 열 삭제
ALTER TABLE project_tasks DROP COLUMN legacy_status;

-- 열 이름 및 데이터 타입 변경
ALTER TABLE project_tasks CHANGE task_desc task_description TEXT NOT NULL;

-- 열의 데이터 타입만 변경
ALTER TABLE project_tasks MODIFY COLUMN priority_level SMALLINT NOT NULL;

-- 열의 위치 조정 (특정 열 뒤로 이동)
ALTER TABLE project_tasks MODIFY COLUMN task_description TEXT NOT NULL AFTER task_title;

인덱스(Index) 및 제약 조건 관리

-- 기본 키 추가
ALTER TABLE project_tasks ADD PRIMARY KEY (task_id);

-- 일반 인덱스 추가
ALTER TABLE project_tasks ADD INDEX idx_assignee (assignee_id);

-- 고유(Unique) 인덱스 추가
ALTER TABLE project_tasks ADD UNIQUE idx_task_code (task_code);

-- 인덱스 삭제
ALTER TABLE project_tasks DROP INDEX idx_assignee;

테이블 이름 변경

ALTER TABLE project_tasks RENAME TO sprint_tasks;

외래 키(Foreign Key) 및 캐스케이드(Cascade) 설정

참조 무결성을 유지하면서 부모 테이블의 변경 사항이 자식 테이블에 자동으로 반영되도록 설정하는 방법입니다.

DROP TABLE IF EXISTS `articles`;
DROP TABLE IF EXISTS `categories`;

CREATE TABLE `categories` (
    `category_id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `articles` (
    `article_id` INT AUTO_INCREMENT PRIMARY KEY,
    `category_id` INT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    INDEX `idx_category` (`category_id`),
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`category_id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;