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

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

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

고객 이름은 같은 사람이 아니어도 같을 수 있으므로 기본키로 쓰기 어렵습니다. 이 실습에서는 변하지 않는 고객 번호를 기본키로 정하고, 이메일은 별도의 중복 조건으로 다룹니다. 기본키는 행을 가리키는 기준, UNIQUE는 업무 규칙에 따라 겹치면 안 되는 값의 조건입니다. 값의 존재와 범위까지 검사하려면 NULL·기본값·NOT NULL·CHECK로 입력 조건 정하기의 조건을 함께 적용하고, 업무 식별 키가 결정하는 사실은 중복 데이터를 줄이는 정규화와 주문 데이터베이스 설계처럼 설계 단계에서 분석합니다.

기본키를 만든다는 말이 기본키 열을 하나만 써야 한다는 뜻은 아닙니다. 여러 열을 묶은 복합 기본키도 하나의 기본키 정의입니다. 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 테이블은 이 초기화 대상에 포함하지 않습니다.