MariaDB 강좌 / 기본키·AUTO_INCREMENT·UNIQUE로 행 식별하기

PRIMARY KEY는 테이블의 각 행을 중복 없이 식별하고, UNIQUE는 다른 업무 식별값의 중복을 막습니다. AUTO_INCREMENT는 정수 번호를 생성하는 속성이며 기본키 선언이나 빠짐없는 순번을 대신하지 않으므로, 세 기능을 필요한 조건에 맞게 함께 사용해야 합니다.

행 번호와 업무상 중복값 구분하기

고객 이름은 같은 사람이 아니어도 같을 수 있으므로 기본키로 쓰기 어렵습니다. 이 실습에서는 변하지 않는 고객 번호를 기본키로 정하고, 이메일은 별도의 중복 조건으로 다룹니다. 기본키는 행을 가리키는 기준, UNIQUE는 업무 규칙에 따라 겹치면 안 되는 값의 조건입니다.

기본키를 만든다는 말이 기본키 열을 하나만 써야 한다는 뜻은 아닙니다. 여러 열을 묶은 복합 기본키도 하나의 기본키 정의입니다. AUTO_INCREMENT는 반드시 PRIMARY KEY라는 규칙은 아니지만, 초보 실습에서는 단일 정수 기본키에 함께 쓰는 구성이 이해하기 쉽습니다.

고객 번호와 선택 이메일의 전체 실습

2~5강에서 준비한 course_user로 shopcourse 데이터베이스에 접속한 상태에서 실행합니다. 접속·SQL 파일 실행 방법은 명령줄 클라이언트 설명을 참고하세요. 서버 버전과 선택한 데이터베이스를 먼저 확인하고, 운영 데이터가 들어 있는 연결에서는 이 실습을 실행하지 않습니다.

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

customers_key_lab은 이후 고객·주문 관계를 배우기 위한 별도 연습 테이블입니다. 이메일을 아직 모르는 고객도 저장한다는 정책을 택해 email에 NULL을 허용했습니다. 기본키 열은 명시적으로 NOT NULL을 선언하고 INSERT에서는 번호를 생략합니다.

USE shopcourse;
CREATE TABLE customers_key_lab (
  customer_id INT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(254) NULL,
  PRIMARY KEY (customer_id),
  CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
  ('김민수', 'minsu@example.com'),
  ('이서연', NULL),
  ('박지우', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
  supplier_id INT NOT NULL,
  supplier_code VARCHAR(30) NOT NULL,
  label VARCHAR(100) NOT NULL,
  PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
  (1, 'N001', '노트'),
  (2, 'N001', '다른 공급자의 노트');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('취소할 행');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('저장한 행');
SELECT item_id, label FROM auto_gap_lab;

새 테이블의 첫 세 행 번호는 자동으로 1·2·3이 됩니다. 이름이 다른 두 고객의 이메일이 모두 NULL이어도 UNIQUE(email)에 의해 거절되지 않습니다. 이메일을 반드시 입력해야 한다면 NULL·기본값·입력 제약에서 다루는 NOT NULL도 필요하며, 이미 NULL이 있는 운영 테이블에 그 조건을 추가하기 전에는 데이터를 점검해야 합니다.

번호 생성과 제약 문법은 AUTO_INCREMENT 공식 문서와 PRIMARY KEY·UNIQUE 제약 조건 설명을 참고하세요. 이 실습에서 쓰는 example.com 이메일은 실제 발송을 위한 주소가 아니라 입력값 예시입니다.

중복 이메일과 중복 기본키를 따로 시험하기

정상 실습을 마친 뒤 아래 문장을 하나씩 실행합니다. 첫 문장은 기존 이메일과 같아 UNIQUE 오류가 발생하고, 둘째 문장은 기존 고객 번호 1을 지정해 PRIMARY KEY 오류가 발생합니다. 어떤 제약 이름이 오류에 표시되는지 비교하세요.

INSERT INTO customers_key_lab (customer_name, email) VALUES ('동명이인', 'minsu@example.com');

INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, '중복 번호');

각 오류 뒤 SELECT COUNT(*) FROM customers_key_lab;가 3인지 확인합니다. 오류를 감추려고 INSERT IGNORE나 REPLACE로 바로 바꾸지 않습니다. REPLACE는 충돌하는 기존 행을 대체하는 동작을 포함할 수 있으므로 중복 검사의 단순한 대안이 아닙니다.

이메일 비교에는 열의 정렬 규칙이 영향을 줍니다. utf8mb4_unicode_ci에서는 대소문자를 다르게 입력해도 중복으로 판정될 수 있습니다. 실제 이메일 정책에 맞춰 정규화·비교 기준을 정하고, 저장된 글자가 다른지만 보고 UNIQUE 통과를 판단하지 않습니다.

여러 열을 묶은 복합 기본키 이해하기

두 공급자가 각각 N001이라는 상품 코드를 쓸 수 있다고 가정합니다. 상품 코드만 기본키로 정하면 두 번째 공급자의 정상 상품을 저장하지 못하므로 supplier_id와 supplier_code를 함께 묶습니다. 본문의 product_codes_key_lab은 그 조합을 기본키로 정의했습니다.

INSERT INTO product_codes_key_lab VALUES (1, 'N001', '중복 코드');

복합 기본키는 각 구성 열을 따로 중복 금지하는 것이 아니라 조합 전체를 제한합니다. 같은 supplier_id에 다른 코드, 다른 supplier_id에 같은 코드는 허용됩니다. 조회와 외래키 참조에도 열 조합이 필요할 수 있으므로 편의만 보고 열 수를 늘리지 않습니다. 실제 업무 키가 변경될 수 있다면 별도의 안정적인 번호와 UNIQUE 조합을 함께 사용하는 설계도 검토합니다.

자동 번호에 빈 구간이 생겨도 정상인 이유

전체 예제의 auto_gap_lab은 첫 INSERT를 트랜잭션에서 취소하고 두 번째 행만 저장합니다. InnoDB에서 첫 행이 예약한 번호가 ROLLBACK으로 되돌아가지 않으므로 저장된 행의 item_id는 2가 됩니다. 한 행만 있다고 번호도 반드시 1이어야 하는 것은 아닙니다.

삭제, 실패한 INSERT와 동시 작업에서도 번호가 빠질 수 있습니다. AUTO_INCREMENT를 빠짐없는 청구 번호나 행 개수로 사용하지 않습니다. SELECT COUNT(*) FROM 테이블명;이 행 개수를 확인하는 방법이며 MAX(id)는 마지막으로 보이는 번호일 뿐입니다.

방금 추가한 번호가 필요하면 같은 연결에서 성공한 INSERT 직후 LAST_INSERT_ID()를 사용할 수 있습니다. 다른 연결의 작업 결과와 섞지 않고, 실패한 INSERT 뒤 값으로 성공을 판정하지 않습니다. MAX(id)+1을 직접 계산해 새 번호로 넣으면 여러 연결이 같은 값을 선택할 수 있어 번호 생성 방식의 대안으로 적절하지 않습니다.

키 구조 확인과 반복 실습 정리

SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;

PRIMARY와 uq_customers_key_email이 별개의 키인지, 복합 기본키에 두 열이 포함되는지 확인합니다. AUTO_INCREMENT 속성과 UNIQUE 조건은 서로 다른 기능이므로 DESCRIBE의 Extra와 SHOW INDEX 결과를 함께 보는 것이 도움이 됩니다.

반복 실습 전에는 SELECT DATABASE();와 SHOW CREATE TABLE 테이블명;으로 대상이 이 강좌에서 만든 실습 테이블인지 확인합니다. 보관할 데이터가 있다면 먼저 백업합니다. 아래 명령은 지정한 테이블과 그 안의 데이터를 삭제하므로 확인한 학습용 테이블에만 실행하세요.

DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;

삭제 후에는 본문의 전체 예제를 다시 실행할 수 있습니다. DROP TABLE 같은 데이터 정의 문장은 일반적인 트랜잭션의 ROLLBACK으로 복구할 수 있다고 기대하지 않습니다. 기존 products 테이블은 이 초기화 대상에 포함하지 않습니다.