MariaDB 강좌 / 테이블 관계 설계하기: 일대다·다대다·외래키

테이블 관계는 각 행의 의미를 정하고 기본키와 외래키로 연결해 설계합니다. 고객 한 명의 여러 주문은 일대다 관계이며, 주문과 상품의 다대다 관계는 수량·주문 당시 가격을 가진 주문 상세 테이블로 나누어 표현할 수 있습니다.

행의 의미와 관계의 방향부터 정하기

고객 이름과 상품 목록을 주문 한 행의 문자열에 모두 넣으면 상품별 수량을 계산하거나 고객 정보를 수정할 때 파싱과 중복 문제가 생깁니다. 고객·상품·주문·주문 상세를 나누고 각 행이 무엇을 나타내는지 먼저 정합니다. 테이블을 나눈다는 것 자체가 정답은 아니며 필요한 사실과 변경 규칙을 보존하는 설계여야 합니다. 정의한 관계를 실제 조회로 읽는 방법은 INNER JOIN으로 서로 연결된 데이터 조회하기, 반복 저장할 사실을 분리하는 설계 이유는 중복 데이터를 줄이는 정규화와 주문 데이터베이스 설계에서 확인할 수 있습니다.

주문 상세에는 상품의 현재 가격 대신 주문 당시 unit_price를 저장합니다. 현재 가격이 바뀌어도 과거 주문 금액을 재현해야 하기 때문입니다. 같은 상품을 한 주문에 여러 옵션으로 따로 담아야 한다면 이 예제의 복합키보다 별도의 상세 번호·옵션 키가 필요한지 검토합니다.

외래키가 있는 전체 실습 구조 만들기

학습용 MariaDB 서버의 shopcourse에 course_user로 접속합니다. 명령줄 클라이언트와 UTF-8 SQL 파일 실행 방법대로 코드를 UTF-8 파일에 저장하고 SOURCE C:/mariadb-lab/lesson.sql;처럼 실제 파일 경로로 실행할 수 있습니다. SQL 문은 운영체제 터미널이 아닌 MariaDB 연결 안에서 실행합니다.

먼저 SELECT VERSION(), DATABASE();와 SHOW TABLES;로 대상 서버·DB·이름 중복을 확인합니다. 아래 c25_customers, c25_products, c25_orders, c25_items는 이 글의 독립된 연습용 이름입니다. 이미 존재하면 정의와 행을 확인하고, 보관할 자료가 없는 이 글의 실습 테이블임이 확실할 때만 마지막 절의 정리 순서로 지운 뒤 다시 만듭니다. 기존 강좌의 동명 없는 테이블이나 운영 데이터는 삭제하지 않습니다.

아래 코드는 고객·상품부터 만든 뒤 이를 참조하는 주문·상세를 만듭니다. 부모 행을 먼저 넣고 자식 행을 나중에 넣는 순서도 지킵니다. 기존 기본키와 복합키 설명이 이 구조의 식별 기준을 이해하는 데 도움이 됩니다.

USE shopcourse;
CREATE TABLE c25_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c25_order_customer FOREIGN KEY (customer_id)
  REFERENCES c25_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c25_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c25_item_order FOREIGN KEY (order_id)
  REFERENCES c25_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c25_item_product FOREIGN KEY (product_id)
  REFERENCES c25_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c25_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c25_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c25_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c25_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

일대다와 다대다를 실제 행으로 확인하기

SHOW CREATE TABLE c25_items;
SELECT c.customer_id,c.customer_name,o.order_id
FROM c25_customers AS c JOIN c25_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;
SELECT COUNT(*) AS customers FROM c25_customers;
SELECT COUNT(*) AS orders_count FROM c25_orders;
SELECT COUNT(*) AS detail_rows FROM c25_items;

고객은 4명, 주문은 4건, 상세는 6행입니다. 김민수 고객은 101·102번 주문을 갖고, 101번 주문은 노트·볼펜 두 상품을 가집니다. 노트는 101·103번 주문에 등장하므로 주문과 상품 사이에는 여러 연결이 생깁니다. 그 연결 자체가 수량·단가를 가진 주문 상세의 행입니다.

관계의 기준은 이름이 아니라 식별 키입니다. 같은 이름의 다른 고객이 있을 수 있고 이름은 바뀔 수 있습니다. 숫자 기본키와 업무상의 고유 조건을 구분하면 이름 변경이 모든 주문 연결을 다시 만드는 작업이 되지 않습니다.

존재하지 않는 부모와 삭제 제한 시험하기

정상 구조를 확인한 뒤 다음 두 문장을 하나씩 실행합니다. 첫 문장은 없는 고객 999를 참조하므로 외래키 오류가 예상됩니다. 둘째 문장은 주문이 남은 고객을 지우려 하므로 ON DELETE RESTRICT에 의해 거절됩니다.

INSERT INTO c25_orders(order_id,customer_id,ordered_at,status)
VALUES(999,999,'2026-03-01 10:00:00','paid');

DELETE FROM c25_customers WHERE customer_id=1;

오류 뒤 고객 1이 남아 있고 주문 수가 4인지 조회합니다. 오류를 통과시키려고 foreign_key_checks를 끄지 않습니다. 자식 열이 NULL을 허용하는 설계는 부모 참조가 없는 상태를 허용할 수 있지만, 이 예제는 주문 고객 번호를 NOT NULL로 정해 그런 상태를 막습니다. MariaDB 외래키의 조건과 삭제 동작을 확인하세요.

RESTRICT·CASCADE와 업무 삭제 정책 구분하기

CASCADE는 부모 삭제를 자식으로 전파할 수 있으므로 편리하다는 이유만으로 적용하지 않습니다. 고객 탈퇴 시 주문의 보존·익명화·삭제 요구가 다를 수 있고, 주문 상세는 회계·반품 기록과 연결될 수 있습니다. 이 학습용 구조는 실수로 관련 주문을 함께 지우지 않도록 RESTRICT를 선택했습니다.

외래키 열과 참조 열의 타입·부호, 인덱스, InnoDB 사용 여부도 확인합니다. CREATE TABLE에 참조 문구가 보인다고 실제로 의도한 제약이 생겼다고 가정하지 말고 SHOW CREATE TABLE과 실패 입력으로 검사합니다. 참조 무결성은 재고가 충분한지 같은 모든 업무 규칙을 대신하지 않습니다.

설계를 검토하고 자식부터 정리하기

고객은 주문 없이 존재할 수 있고 상품은 판매 없이 등록될 수 있는지, 주문은 고객 없이 생성될 수 있는지 질문해 필수·선택 관계를 정합니다. 데이터가 적을 때는 모든 연결을 조회하고, 잘못된 부모 입력이 거절되는지도 확인합니다. 정리에서는 외래키가 가리키는 부모를 먼저 삭제하지 않습니다.

반복하기 전 현재 DB와 실제 정의·행을 확인하고 필요한 자료를 백업합니다. 보관할 데이터가 없는 이 글의 전용 실습 객체만 아래 순서로 정리합니다. 외래키가 있으면 자식부터 지웁니다. DROP TABLE은 구조와 데이터를 모두 없애며 일반적인 ROLLBACK으로 복원되지 않으므로 취소 가능한 DML 실습과 분리합니다.

DROP TABLE shopcourse.c25_items;
DROP TABLE shopcourse.c25_orders;
DROP TABLE shopcourse.c25_products;
DROP TABLE shopcourse.c25_customers;