외래키 개념과 실무 지침

물리적 외래키는 데이터베이스 레벨에서 테이블 간 제약 조건을 강제하는 메커니즘입니다. 하지만 실무 환경에서는 지양하는 것이 일반적입니다.

외래키 선언 방식

테이블 생성 시 외래키를 정의하는 방법과 이미 존재하는 테이블에 추가하는 두 가지 방식이 있습니다.

-- 학년 정보를 저장하는 기본 테이블
CREATE TABLE `academic_year`(
  `year_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '학년 식별자',
  `year_name` VARCHAR(50) NOT NULL COMMENT '학년 명칭',
  PRIMARY KEY(`year_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 학생 테이블에 외래키 포함하여 생성
CREATE TABLE IF NOT EXISTS `enrolled_student`(
  `student_no` INT(4) NOT NULL AUTO_INCREMENT COMMENT '학번',
  `full_name` VARCHAR(30) NOT NULL DEFAULT '미등록' COMMENT '성명',
  `password_hash` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '비밀번호',
  `gender_code` INT(4) NOT NULL COMMENT '성별',
  `date_of_birth` DATETIME DEFAULT NULL COMMENT '생년월일',
  `enrolled_year_id` INT(10) NOT NULL COMMENT '소속 학년',
  `residence` VARCHAR(30) NOT NULL COMMENT '거주지',
  `contact_email` VARCHAR(30) DEFAULT NULL COMMENT '이메일',
  PRIMARY KEY(`student_no`),
  KEY `IDX_year_reference` (`enrolled_year_id`),
  CONSTRAINT `FK_year_reference` FOREIGN KEY(`enrolled_year_id`) 
    REFERENCES `academic_year`(`year_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 기 테이블에 외래키 추가
ALTER TABLE `enrolled_student`
ADD CONSTRAINT `FK_year_reference` FOREIGN KEY(`enrolled_year_id`) 
  REFERENCES `academic_year`(`year_id`);

실무 권장사항

데이터베이스는 순수한 데이터 저장소로만 활용하고, 참조 무결성은 애플리케이션 계층에서 처리하는 것이 바람직합니다.

• 테이블은 행(데이터)과 열(속성)만 포함하는 단순한 구조로 설계
• 다중 테이블 연계는 프로그램 로직으로 구현
• 외래키와 CASCADE 연산은 애플리케이션 레벨에서 해결 (알리바바 개발 규약)

DML (Data Manipulation Language)

데이터 삽입 (INSERT)

새로운 레코드를 테이블에 추가하는 명령어입니다. 컬럼을 명시적으로 지정하는 습관이 중요합니다.

-- 단일 행 삽입
INSERT INTO `academic_year`(`year_name`) 
VALUES('3학년');

-- 컬럼 미지정 시 모든 값을 순서대로 제공해야 함
-- INSERT INTO `academic_year` VALUES('3학년'); -- 잠재적 오류 위험

-- 다중 행 삽입
INSERT INTO `academic_year`(`year_name`)
VALUES('2학년'), ('1학년');

-- 전체 컬럼 대상 삽입
INSERT INTO `enrolled_student`(
  `full_name`, `password_hash`, `gender_code`, 
  `date_of_birth`, `enrolled_year_id`, `residence`, `contact_email`
) VALUES(
  '김철수', 'securePass1', 1, 
  '2000-05-15 00:00:00', 1, '서울시 강남구', 'chulsoo@example.com'
);

주의사항:

• 컬럼과 값은 1:1 대응해야 함
• 다중 삽입 시 각 값 세트는 쉼표로 구분
• 외래키가 설정된 경우 참조 키 값이 반드시 존재해야 함

데이터 수정 (UPDATE)

기존 레코드의 값을 변경합니다. WHERE 절 누락 시 전체 데이터가 영향받으므로 주의가 필요합니다.

-- 조건을 포함한 단일 속성 수정
UPDATE `enrolled_student` 
SET `full_name`='홍길동' 
WHERE `student_no`=3;

-- 다중 속성 동시 수정
UPDATE `enrolled_student` 
SET 
  `full_name`='홍길동',
  `residence`='부산시 해운대구' 
WHERE `student_no`=3;

-- 현재 시각을 활용한 수정
UPDATE `enrolled_student` 
SET 
  `full_name`='홍길동',
  `residence`='부산시 해운대구',
  `date_of_birth`=CURRENT_TIMESTAMP 
WHERE `student_no`=3;

비교 연산자:

• <> 또는 != : 같지 않음
• AND, OR : 다중 조건 결합

데이터 삭제 (DELETE vs TRUNCATE)

DELETE 문

조건에 맞는 특정 행을 제거하며, 트랜잭션 로그에 기록니다.

-- 위험: 조건 미지정 시 전체 데이터 삭제
-- DELETE FROM `enrolled_student`;

-- 안전: 조건 명시
DELETE FROM `enrolled_student` WHERE `student_no`=4;

TRUNCATE 문

테이블을 완전히 비우며, 구조와 인덱스는 유지합니다.

TRUNCATE TABLE `enrolled_student`;

동작 비교

스토리지 엔진별 AUTO_INCREMENT 동작 차이

-- 테스트 테이블 생성
CREATE TABLE `sequence_test`(
  `seq_id` INT(10) NOT NULL AUTO_INCREMENT,
  `sample_data` VARCHAR(20) NOT NULL,
  PRIMARY KEY(`seq_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 데이터 삽입 후 전체 삭제
INSERT INTO `sequence_test` (`sample_data`) VALUES('A'),('B'),('C');
DELETE FROM `sequence_test`;        -- 또는 TRUNCATE

-- 재시작 후 확인
INSERT INTO `sequence_test` (`sample_data`) VALUES('D');
SELECT * FROM `sequence_test`;      -- InnoDB: 4, MyISAM: 4 (TRUNCATE 시 1)

엔진별 차이점:

• InnoDB: 자동 증가 값을 메모리에 캐싱, 서버 재시작 시 손실 가능
• MyISAM: 파일에 영구 저장, 재시작 후에도 이어서 증가