CODING FACTORY

MariaDB講座 / PRIMARY KEY・AUTO_INCREMENT・UNIQUEで行を識別する

MariaDB講座

PRIMARY KEYは行を一意に識別し、UNIQUEはその他の重複禁止規則を定めます。AUTO_INCREMENTは整数を生成する機能で、キーの宣言や欠番のない番号体系を代替するものではありません。

識別子と業務上の重複禁止を分ける

同姓同名の顧客が存在できるため、名前だけでは行を区別できません。ここでは安定した顧客IDを主キーにし、メールの重複禁止を別の規則にします。主キーは一表に一つの定義ですが、複数列を組み合わせることもできます。

UNIQUEは複数定義でき、NULLを許す列なら複数のNULLが入る場合があります。AUTO_INCREMENTは一表に一列でインデックスが必要ですが、すべての設計で主キーそのものでなければならないわけではありません。この例では単純な整数主キーと組み合わせます。存在の規則はNOT NULLとCHECK、値の従属関係は正規化と分けて考えます。

顧客・複合キー・欠番の例を作る

実習用接続で対象を確認します。

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

通常例全体をUTF-8のSQLファイルに保存するか順に実行します。customers_key_labは後の注文表とは別の練習表です。

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
  ('Alex Chen', 'alex@example.com'),
  ('Sam Patel', NULL),
  ('Jordan Lee', 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', 'Notebook'),
  (2, 'N001', 'Notebook from supplier two');
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 ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;

新規表の顧客はID1～3で、Alexだけがalex@example.com、他の二人はNULLです。UNIQUE(email)は二つのNULLを許します。メールを必須にするならNOT NULLなどの方針が別に必要で、既存NULLの確認も必要です。example.comのアドレスは実習値で、送信先ではありません。

重複エラーを区別する

通常例の後に、意図した失敗を個別に実行します。

INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');

次は主キーの重複です。

INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');

どちらも顧客件数は3のままですが、エラーの制約名が違います。INSERT IGNOREやREPLACEで理由を隠さないでください。REPLACEは競合した行の置き換えを伴うため、無害な回避方法とは限りません。

utf8mb4_unicode_ciでは大文字小文字の違いだけでも同じと判定される場合があります。ALEX@example.comを必ず別の値として登録できるとは考えず、メールの正規化と比較方針を定めます。照合順序と保存が関係します。

複合主キーは組み合わせを一意にする

product_codes_key_labでは(supplier_id, supplier_code)を主キーにします。仕入先1のN001と仕入先2のN001は、組が違うため両方保存できます。各列を個別に一意にするわけではありません。同じ組を再び入れると失敗します。

INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');

参照や検索には組全体が必要になる場合があります。業務コードが将来変わるなら、安定した自動生成IDと別のUNIQUE組み合わせに分ける案も検討します。

自動採番に欠番が生じる理由

auto_gap_labは一行を追加してROLLBACKし、その後で別の行を保存します。この新規InnoDB例では保存行がID2、ラベルSaved rowになり、予約された1は戻りません。削除、失敗や同時実行でも欠番が生じます。

したがってAUTO_INCREMENTは行数でも欠番のない請求書番号でもありません。行数はCOUNT(*)、MAX(id)は最大識別子です。生成値を使う場合は、関連するINSERTが成功した直後に同じ接続でLAST_INSERT_ID()を読みます。他接続や失敗後の値を成功の証拠にしません。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が別であること、複合主キーに両列があることを確認します。DESCRIBEのExtraとSHOW INDEXは、それぞれ生成機能と索引構造という異なる点を読みます。

再作成するときはDB、表の定義と必要なデータを確認して保存し、削除してよい三表にだけ実行します。

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

DROP TABLEは通常のROLLBACKで復元できません。他のproductsなどは残したまま、この例だけを繰り返します。

関連する公式リファレンス

MariaDB講座
同じカテゴリーの他の記事