MySQL FOREIGN KEY — buytbl 구매 테이블 완전 정리

 

 

Web Development · MySQL

MySQL FOREIGN KEY —
buytbl 구매 테이블 완전 정리

AUTO_INCREMENT 기본키 · 외래키로 두 테이블을 연결하는 방법

시작 오늘 분석할 코드

usertbl(회원)과 연결되는 buytbl(구매 내역) 테이블이다. AUTO_INCREMENT 기본키와 FOREIGN KEY가 핵심이다.

CREATE TABLE buytbl ( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK) userID CHAR(8) NOT NULL, -- 아이디(FK) prodName CHAR(6) NOT NULL, -- 물품명 groupName CHAR(4), -- 분류 price INT NOT NULL, -- 단가 amount SMALLINT NOT NULL, -- 수량 FOREIGN KEY (userID) REFERENCES usertbl(userID) );

용어 용어 정리

AUTO_INCREMENTINSERT 시 자동으로 1씩 증가하는 숫자 부여

FOREIGN KEY외래키 — 다른 테이블의 기본키를 참조하는 컬럼

REFERENCES"이 테이블의 이 컬럼을 참조한다"는 선언

참조 무결성FK에 usertbl에 없는 userID는 삽입 불가

---

1 AUTO_INCREMENT — 번호 자동 부여

num INT AUTO_INCREMENT NOT NULL PRIMARY KEY는 INSERT 시 num 값을 직접 넣지 않아도 1, 2, 3...으로 자동 증가한다. 주문 번호처럼 "고유한 순번"이 필요한 컬럼에 쓴다.

-- num을 NULL로 넘기면 자동 채워짐 INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2); -- num = 1 자동 부여 INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1); -- num = 2 자동 부여

※ AUTO_INCREMENT는 PRIMARY KEY와 함께 쓰는 것이 일반적이다. 이미 삭제된 번호는 재사용되지 않는다 (1,2,3 삭제 후 다음은 4가 아니라 계속 이어짐).

---

2 FOREIGN KEY — 두 테이블을 연결

FOREIGN KEY (userID) REFERENCES usertbl(userID)는 buytbl의 userID가 반드시 usertbl에 존재하는 userID여야 한다고 선언한다. 이를 참조 무결성이라 한다.

usertbl (부모 테이블)

userID = 'KBS' ← 기본키
여기 없는 ID는
buytbl에 삽입 불가

buytbl (자식 테이블)

userID = 'KBS' ← 외래키
usertbl.userID를
참조 (REFERENCES)

※ buytbl을 usertbl보다 먼저 만들면 FOREIGN KEY 설정 시 오류가 난다. 참조 대상(usertbl)이 먼저 존재해야 한다. 삭제 시에도 자식 테이블(buytbl)을 먼저 삭제해야 한다.

---

3 CASCADE — 부모 변경 시 자식도 같이 바꾸기

ON DELETE / ON UPDATE 옵션

기본 외래키는 부모(usertbl) 레코드를 삭제하려 하면 자식(buytbl)에 해당 userID가 남아있어 오류가 난다. CASCADE 옵션을 추가하면 부모 삭제 시 자식 레코드도 자동으로 삭제된다.

FOREIGN KEY (userID) REFERENCES usertbl(userID) ON DELETE CASCADE -- usertbl 행 삭제 시 buytbl 관련 행도 삭제 ON UPDATE CASCADE -- usertbl userID 변경 시 buytbl도 같이 변경

옵션	동작	적합한 상황
CASCADE	부모 변경/삭제 시 자식도 함께	구매내역도 같이 삭제해도 되는 경우
SET NULL	부모 삭제 시 자식 FK를 NULL로	구매기록은 남기되 회원 연결만 끊기
RESTRICT (기본)	자식이 있으면 부모 삭제 불가	실수로 부모를 지우는 것을 방지

---

 

반응형

 

4 두 테이블 완전한 생성 스크립트

sqldb 전체 세팅 — 올바른 순서로 실행

DROP DATABASE IF EXISTS sqldb; CREATE DATABASE sqldb; USE sqldb; -- 1단계: 부모 테이블 먼저 생성 DROP TABLE IF EXISTS usertbl; CREATE TABLE usertbl ( userID CHAR(8) NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, birthYear INT NOT NULL, height SMALLINT ); -- 2단계: 자식 테이블 생성 (부모가 있어야 REFERENCES 가능) DROP TABLE IF EXISTS buytbl; CREATE TABLE buytbl ( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, userID CHAR(8) NOT NULL, prodName CHAR(6) NOT NULL, price INT NOT NULL, amount SMALLINT NOT NULL, FOREIGN KEY (userID) REFERENCES usertbl(userID) );

---

5 관계형 DB 핵심 개념 — PK · FK · 참조 무결성

usertbl과 buytbl의 관계는 관계형 데이터베이스(RDBMS)의 핵심 개념을 잘 보여준다. 두 테이블이 userID라는 공통 컬럼으로 연결되어 있고, FK가 이 연결을 보장한다.

개념	usertbl	buytbl
역할	부모 테이블 (회원 정보)	자식 테이블 (구매 내역)
키 종류	userID = PRIMARY KEY	userID = FOREIGN KEY
데이터 무결성	userID는 중복·NULL 불가	usertbl에 없는 ID 삽입 불가
1:N 관계	회원 1명	구매 기록 N개 (여러 번 구매)

1:N 관계란 — 회원 1명이 구매 내역 여러 개를 가질 수 있다. usertbl의 'KBS' 1행에 buytbl의 KBS 행이 여러 개 연결된다.

---

6 외래키 확인 명령어

어떤 FK가 걸려있는지 확인하는 방법

테이블에 외래키가 있는지, 어느 컬럼이 어떤 테이블을 참조하는지 확인하려면 다음 명령어를 쓴다.

SHOW CREATE TABLE buytbl; -- 테이블 생성 SQL 전체 출력 → FOREIGN KEY CONSTRAINT 확인 가능 SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'buytbl' AND REFERENCED_TABLE_NAME IS NOT NULL; -- information_schema에서 FK 관계 직접 조회

SHOW CREATE TABLE이 가장 빠르다. FK 이름(CONSTRAINT이름), 참조 컬럼, 참조 대상 테이블을 한 번에 볼 수 있다.

---

7 buytbl 데이터 INSERT 예시

실제 구매 데이터를 넣고 SELECT로 확인하기

usertbl 데이터가 먼저 있어야 buytbl에 삽입 가능하다. num은 AUTO_INCREMENT이므로 NULL로 전달한다. INSERT 후 SELECT로 num이 자동 부여됐는지 확인한다.

-- usertbl에 KBS, BBK 회원이 있다고 가정 INSERT INTO buytbl VALUES (NULL, 'KBS', '운동화', NULL, 30, 2), -- num=1 자동 (NULL, 'KBS', '노트북', '전자', 1000, 1), -- num=2 자동 (NULL, 'BBK', '모니터', '전자', 200, 5); -- num=3 자동 SELECT * FROM buytbl; -- num 자동 채워진 것 확인 -- usertbl에 없는 ID 삽입 시도 → 오류 INSERT INTO buytbl VALUES(NULL, 'ZZZ', '물건', NULL, 100, 1); -- ERROR: Cannot add or update a child row: a foreign key constraint fails

---

8 buytbl 컬럼 선택 근거 정리

각 컬럼의 타입·제약 조건을 선택한 이유

컬럼	타입	제약	선택 이유
num	INT	AUTO_INCREMENT, PK	주문번호는 자동 채번이 편리
userID	CHAR(8)	NOT NULL, FK	usertbl.userID와 타입 일치 필수
prodName	CHAR(6)	NOT NULL	짧은 상품명 → 고정길이
groupName	CHAR(4)	NULL 허용	분류 없는 상품도 있음
price	INT	NOT NULL	가격 범위 넓음 (30~1000)
amount	SMALLINT	NOT NULL	수량은 작은 숫자

---

9 자주 하는 실수

실수	증상	해결
usertbl보다 buytbl 먼저 생성	FOREIGN KEY 오류	부모 테이블(usertbl) 먼저 생성
usertbl에 없는 userID INSERT	Cannot add or update a child row	부모 테이블에 해당 ID 먼저 삽입
부모 테이블 먼저 DROP	외래키 제약 오류	자식 테이블(buytbl) 먼저 삭제
AUTO_INCREMENT에 0 또는 음수 지정	예상치 못한 번호 부여	NULL로 전달하거나 생략

핵심 한 줄 요약

AUTO_INCREMENTINSERT마다 자동으로 +1 — 순번 컬럼에 사용, NULL 전달

FOREIGN KEY다른 테이블의 PK를 참조 — 없는 ID는 삽입 불가

REFERENCES어느 테이블의 어느 컬럼을 참조할지 선언

생성 순서부모(usertbl) 먼저 → 자식(buytbl) 나중

ON DELETE CASCADE부모 삭제 시 자식도 함께 삭제 — 기본은 RESTRICT(불가)

1:N 관계회원 1명 : 구매기록 N개 — FK로 연결된 관계형 DB 핵심

SHOW CREATE TABLEFK 이름·참조 컬럼·CASCADE 옵션 한 번에 확인

DROP 순서자식(buytbl) 먼저 DROP → 부모(usertbl) 나중 DROP

컬럼 타입 일치FK 컬럼과 참조 PK 컬럼의 타입이 동일해야 함 — 둘 다 CHAR(8)

FK 오류 메시지Cannot add or update a child row → 부모에 없는 ID 삽입 시도

buytbl.num주문번호 = AUTO_INCREMENT PK — NULL 전달로 자동 채번

groupName NULL 허용분류 없는 상품도 있음 — NOT NULL 없이 선언하면 NULL 저장 가능

price * amount총액은 컬럼에 저장 안 함 — SELECT로 계산: price * amount AS 총액

SMALLINT amount수량은 작은 숫자이므로 SMALLINT(-32768~32767)로 충분

AUTO_INCREMENT 재시작ALTER TABLE buytbl AUTO_INCREMENT = 1; 로 번호 초기화 가능

FOREIGN KEY 이름CONSTRAINT fk_name FOREIGN KEY ... 로 이름 지정 가능 (생략 시 자동)

참조 무결성 보장FK가 있으면 orphan record 발생 불가 — 데이터 신뢰성 보장

정규화중복 데이터 대신 FK로 연결 — usertbl에 회원정보 1번만 저장

buytbl 전체 흐름DROP DATABASE → CREATE → USE → usertbl → buytbl → INSERT → SELECT

INT vs SMALLINTprice=INT(넓은 범위), amount=SMALLINT(작은 수) — 컬럼 용도에 맞게 선택

NOT NULL 컬럼num/userID/prodName/price/amount = 필수. groupName만 선택(NULL 허용)

INNER JOIN 예고SELECT ... FROM usertbl u INNER JOIN buytbl b ON u.userID = b.userID — 다음 단계

물리적 데이터 모델usertbl-buytbl = 회원-구매 1:N 관계 — ERD 다이어그램의 실제 구현

AUTO_INCREMENT 확인SELECT LAST_INSERT_ID(); → 마지막 삽입된 AUTO_INCREMENT 값 확인

관계형 DB 핵심중복 없는 PK + FK로 연결 — 데이터를 한 곳에만 저장하고 참조로 공유

buytbl의 역할회원(usertbl)이 무엇을 얼마나 샀는지 기록 — 구매내역 N:1 구조

Tags

#MySQL #FOREIGN KEY #AUTO_INCREMENT #CREATE TABLE #외래키 #참조무결성 #관계형테이블 #REFERENCES #PK #FK #웹개발 #티스토리

▼ 티스토리 태그 입력란 복사용

MySQL, FOREIGN KEY, AUTO_INCREMENT, CREATE TABLE, 외래키, 참조무결성, 관계형테이블, REFERENCES, PK, FK, 웹개발, 티스토리