導入
リレーショナルデータベースの貴重な機能の一つは、明確に定義された構造でデータをフォーマットできることです。この構造は、固定列を持つテーブルを使用し、明確に定義されたデータ型に準拠し、各行の形式を統一することで実現されます。データをテーブルの行として保存する場合、それらの行を一意に見つけて参照できることも同様に重要です。構造化照会言語（SQL）では、主キーを使用することでこれを実現できます。主キーは、リレーショナルデータベースのテーブル内の個々の行の識別子として機能します。.
このチュートリアルでは、主キーについて学び、データベーステーブル内の一意の行を識別するための様々な種類の主キーの使い方を学びます。サンプルデータセットを使用して、単一列、複数列、そして自動増分シーケンシャルキーに主キーを作成します。.
前提条件
このガイドに従うには、SQLベースのリレーショナルデータベース管理システム（RDBMS）が動作するコンピュータが必要です。このガイドの手順と例は、以下の環境で検証されています。
- Ubuntu 20.04 を実行しているサーバー、管理者権限を持つ非ルートユーザー、および UFW で構成されたファイアウォール
- MySQL はサーバー上にインストールされ、保護されています。
- データベースからデータを取得するためのSELECTクエリの実行に関する基本的な紹介
注：多くのRDBMSは独自のSQL実装を使用していることにご注意ください。このチュートリアルで紹介するコマンドはほとんどのRDBMSで動作し、主キーはSQL標準の一部ですが、一部の機能はデータベース固有のため、MySQL以外のシステムでテストした場合、正確な構文や出力が異なる場合があります。.
また、主キーを使用してテーブルを作成するための空のデータベースも必要です。MySQLサーバーへの接続と、このガイドの例で使用するテストデータベースの作成の詳細については、以下の「MySQLへの接続とサンプルデータベースの設定」セクションをご覧ください。.
MySQLへの接続とサンプルデータベースの設定
このセクションでは、このガイドの例を使用できるように、MySQL サーバーに接続してデータベース インスタンスを作成します。.
SQL データベース システムがリモート サーバー上で実行されている場合は、ローカル マシンからサーバーに SSH で接続します。
ssh sammy@your_server_ip次に、MySQL サーバー プロンプトを開き、Sami を MySQL ユーザー名に置き換えます。
mysql -u sammy -p
primary_keys というデータベースを作成します。
CREATE DATABASE primary_keys;
データベースが正常に作成された場合、次の出力が表示されます。
Output
Query OK, 1 row affected (0.01 sec)primary_keys データベースを選択するには、次の USE ステートメントを実行します。
USE primary_keys;
次のような出力が得られます。
Output
Database changedデータベースを選択したら、その中にサンプルテーブルを作成できます。これで、ガイドの残りの部分に従って、MySQLで主キーの操作を開始する準備が整いました。.
長調入門
リレーショナルデータベースのデータは、個々の行からなる特定の均一な構造を持つテーブルに格納されます。テーブル定義は、どのような列が存在し、個々の列にどのような種類のデータを格納できるかを記述します。これだけで、データベースに情報を格納し、WHERE句を用いて様々なフィルター条件で検索することができます。しかし、この構造はすべての行が明確に見つかることを保証するものではありません。.
公道での走行が許可されているすべての登録車両のデータベースを想像してみてください。データベースには、メーカー、モデル、製造年、塗装色などの情報が含まれています。しかし、2007年製の赤いシボレー・カマロを探している場合、複数の車両が見つかる可能性があります。自動車メーカーは同じ車を複数の顧客に販売しているからです。そのため、登録車には各車両を識別するナンバープレートが付いています。OFP857というナンバープレートの車を検索した場合、この条件で見つかるのは1台だけです。これは、法律により、ナンバープレートは登録車を一意に識別するためです。リレーショナルデータベースでは、このようなデータは主キーと呼ばれます。.
主キーは、データベーステーブル内の各行を一意に識別できる、列または列セットに含まれる一意の識別子です。主キーの技術的な特性は、いくつかのルールによって反映されています。
- 主キーは一意の値を使用する必要があります。主キーが複数の列で構成されている場合、これらの列の値の組み合わせはテーブル全体で一意である必要があります。キーは各行を一意に識別するために使用されるため、複数回出現することはできません。.
- 主キーには NULL 値を含めることはできません。.
- 各データベース テーブルでは主キーを 1 つだけ使用できます。.
データベース エンジンはこれらのルールを適用するため、テーブルに主キーが定義されている場合は、それらの属性が正しいと信頼できます。.
これらの技術的な特徴に加えて、データの内容も考慮して、主キーとして適切なデータの種類を決定する必要があります。自然キーはデータセット内に既に存在する識別子ですが、代理キーは人工的な識別子です。.
一部のデータ構造には、データセット内に自然発生する主キーがあります。例えば、車両データベースのナンバープレート番号や米国市民登録簿の社会保障番号などが挙げられます。このような識別子は、単一の値ではなく、複数の値のペアまたは組み合わせである場合があります。例えば、地方自治体の住宅ディレクトリでは、通り名や番地だけでは家を一意に識別できません。1つの通りに複数の家屋がある場合があり、同じ番地が複数の通りに現れることもあります。しかし、通り名と番地のペアは、家屋を一意に識別できるとみなすことができます。このような自然発生する識別子は、自然キーと呼ばれます。.
しかし、多くの場合、単一の列の値、または列の小さなサブセットの値だけではデータを一意に識別できません。そのため、例えば数値のシーケンスやUUIDなどのランダムに生成された識別子を使用して、人工的な主キーが作成されます。このようなキーは代理キーと呼ばれます。.
次のセクションでは、1 つ以上の列に基づいて自然キーを作成し、自然キーが選択できないテーブルに代替キーを作成します。.
列に主キーを作成する
多くの場合、データセットには、テーブル内の行を一意に識別するために使用できる列が自然に含まれています。このような場合、データを表す自然キーを作成できます。先ほどの登録済み自動車データベースの例に続き、次のような構造のテーブルを想像してみてください。
Sample table
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+1行目と2行目はどちらも赤い2018年式Ford Mustangについて記述しています。メーカーとモデルだけでは、この車を一意に識別することはできません。ナンバープレートはどちらの場合も異なり、テーブルの各行に適切な一意の識別子を提供します。ナンバープレート番号は既にデータの一部であるため、これを主キーとして使用することで自然キーが作成されます。License_plate列に主キーを使用せずにテーブルを作成した場合、データセットのどこかの時点で重複ページや空ページが表示されるリスクがあります。.
次に、License_plate 列を主キーとして次の列を持つ上記と同様のテーブルを作成します。
- License_plate: この列には、varchar データ型で表されるナンバープレート番号が含まれます。.
- ブランド: この列は車のブランドを表し、最大 50 文字の varchar データ型を使用して表現されます。.
- モデル: この列には、最大 50 文字の varchar データ型を使用して表現される車のモデルが格納されます。.
- 色: この列には、最大 20 文字の varchar データ型を使用して表現された色が格納されます。.
- year: この列には、数値データを格納する int データ型を使用して表された、自動車の製造年が表示されます。.
マシン テーブルを作成するには、次の SQL ステートメントを実行します。
CREATE TABLE cars (
license_plate varchar(8) PRIMARY KEY,
brand varchar(50),
model varchar(50),
color varchar(20),
year int
);... */PRIMARY KEYステートメントは、License_plateデータ型の定義に従います。単一の列に基づく主キーを扱う場合は、キーを作成するための簡略化された構文を使用し、列定義に主キーを記述することができます。.
次の出力が印刷された場合、テーブルは作成されています。
Output
Query OK, 0 rows affected (0.00 sec)その後、次の INSERT INTO 操作を実行して、上記の例で示したサンプル行をテーブルにロードします。
INSERT INTO cars VALUES
('ABC123', 'Ford', 'Mustang', 'Red', 2018),
('CES214', 'Ford', 'Mustang', 'Red', 2018),
('DEF456', 'Chevrolet', 'Camaro', 'Blue', 2016),
('GHI789', 'Dodge', 'Challenger', 'Black', 2014);データベースは成功メッセージで応答します:
Output
Query OK, 4 rows affected (0.010 sec)
Records: 4 Duplicates: 0 Warnings: 0SELECT ステートメントを使用して、新しく作成されたテーブルに期待されるデータと形式が含まれていることを確認できます。
SELECT * FROM cars;
出力には、セクションの冒頭にあるものと同様の表が表示されます。
Output
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+次に、データベースエンジンが主キーのルールを保証しているかどうかを確認します。以下のコマンドを実行して、重複するナンバープレートを持つ車を挿入してみましょう。
INSERT INTO cars VALUES ('DEF456', 'Jeep', 'Wrangler', 'Yellow', 2019);
MySQL は、DEF456 タグによって主キーのエントリが重複していることを示すエラー メッセージを返します。
Output ERROR 1062 (23000): Duplicate entry 'DEF456' for key 'cars.PRIMARY'
注: 主キーは内部的には一意のインデックスで実装されており、テーブル内の他の列に手動で作成するインデックスと多くの点で共通しています。最も重要なのは、主キーインデックスは、インデックスが定義されている列に対するテーブルクエリのパフォーマンスも向上させることです。この目的でのインデックスの使用法の詳細については、このチュートリアルの「インデックスの使用方法」ガイドをご覧ください。.
これで、重複したナンバープレートが許可されていないことが確認できました。次に、空白のナンバープレートの車両を輸入できるかどうかを確認します。
INSERT INTO cars VALUES (NULL, 'Jeep', 'Wrangler', 'Yellow', 2019);
今回は、データベースは別のエラー メッセージで応答します。
Output
ERROR 1048 (23000): Column 'license_plate' cannot be nullデータベースによって強制されるこれらの2つのルールにより、License_plate がテーブル内の各行を一意に識別することが保証されます。各ナンバープレートに対してテーブルをクエリすると、毎回1行が返されることが期待できます。.
次のセクションでは、複数の列で主キーを使用する方法を学習します。.
複数の列にわたる主キーの作成
1 つの列ではテーブル内の行を一意に識別するのに不十分な場合は、複数の列を使用する主キーを作成できます。.
たとえば、通りの名前や番地だけでは各家を識別するのに十分ではない家屋登記所を想像してください。
Sample table
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+通り名「Central Park West」は、通り番号「100」と同様に、表に複数回出現します。しかし、通り名と通り番号のペアは重複していません。この場合、どちらの列も主キーにはなり得ませんが、これら2つの値のペアを使用して、表の各行を一意に識別することができます。.
次に、次の列を持つ上記のようなテーブルを作成します。
- street_name: この列は、家が位置する通りの名前を表します。varchar データ型は 50 文字に制限されています。.
- street_number: この列は、建物の番地をvarcharデータ型で表します。この列には最大5文字を格納できます。番地によっては文字が含まれる場合があるため（例：200B）、numeric intデータ型は使用しません。.
- house_owner: この列には、50 文字に制限された varchar データ型で表される家の所有者の名前が含まれます。.
- year: この列は家が建てられた年を表し、数値を格納する int データ型で表されます。.
今回は、主キーとして street_name 列と street_number 列の両方を使用します。これを行うには、次のSQL文を実行します。
CREATE TABLE houses (
street_name varchar(50),
street_number varchar(5),
house_owner varchar(50),
year int,
PRIMARY KEY(street_name, street_number)
);今回は、前の例とは異なり、PRIMARY KEY文が列定義の下に記述されています。PRIMARY KEY文は括弧で囲まれ、2つの列名（street_nameとstreet_number）が示されています。この構文により、2つの列を持つhousesテーブルに主キーが作成されます。.
次の出力が印刷された場合、テーブルは作成されています。
Output
Query OK, 0 rows affected (0.00 sec)その後、次の INSERT INTO 操作を実行して、前の例で示したサンプル行をテーブルにロードします。
INSERT INTO houses VALUES
('Central Park West', '100', 'John Doe', 2014),
('Broadway', '1500', 'Jane Smith', 2016),
('5th Avenue', '100', 'Bob Johnson', 2018),
('Lexington Avenue', '5001', 'Samantha Davis', 2010),
('Park Avenue', '7000', 'Michael Rodriguez', 2012),
('Central Park West', '200', 'Tom Thompson', 2015);
データベースは成功メッセージで応答します:
Output
Query OK, 6 rows affected (0.000 sec)
Records: 6 Duplicates: 0 Warnings: 0SELECT ステートメントを使用して、新しく作成されたテーブルに期待されるデータと形式が含まれていることを確認できます。
SELECT * FROM houses;
出力には、セクションの冒頭にあるものと同様の表が表示されます。
Output
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+
6 rows in set (0.000 sec)さて、データベースが通り名と番地が重複する行を許可しているかどうかを確認しましょう。ただし、完全な住所の重複はテーブルに表示しないように制限しています。まずは、パークストリートに別の家を追加してみましょう。
INSERT INTO houses VALUES ('Park Avenue', '8000', 'Emily Brown', 2011);
住所 8000 Park Avenue は以前はテーブルに表示されていなかったため、MySQL は成功メッセージで応答します。
Output
Query OK, 1 row affected (0.010 sec)8000 Main Street に家を追加し、番地を繰り返すと、同様の結果になります。
INSERT INTO houses VALUES ('Main Street', '8000', 'David Jones', 2009);
この場合も、アドレス全体が繰り返されないため、新しい行が正しく挿入されます。
Output
Query OK, 1 row affected (0.010 sec)ただし、次の INSERT ステートメントを使用して、100 5th Avenue に別の家を追加します。
INSERT INTO houses VALUES ('5th Avenue', '100', 'Josh Gordon', 2008);
データベースはエラー メッセージで応答し、値のペア 5th Avenue と 100 の主キーに重複したエントリがあることを通知します。
Output
ERROR 1062 (23000): Duplicate entry '5th Avenue-100' for key 'houses.PRIMARY'データベースは、2つの列にキーを定義することで主キールールを正しく適用します。これにより、通り名と番地を含む完全な住所がテーブル内で重複することがなくなります。.
このセクションでは、ホームテーブルの各行を一意に識別するために、2つの列を持つ自然キーを作成しました。しかし、主キーは必ずしもデータセットから抽出できるとは限りません。次のセクションでは、データから直接取得されない人工的な主キーを使用します。.
連続主キーの作成
これまで、サンプルデータセットの列を使用して一意の主キーを作成してきました。しかし、場合によってはデータが重複してしまい、列を一意の識別子として適切に使用できないことがあります。このような場合は、生成された識別子を使用して連続した主キーを作成できます。データによって行を識別するために新しい識別子を作成する必要がある場合、これらの人工的な識別子に基づいて作成された主キーは代理キーと呼ばれます。.
読書クラブのメンバーリストを想像してみてください。誰でも政府発行の身分証明書を提示せずに参加できる、非公式の集まりです。似たような名前の人がいつかクラブに参加する可能性は高いでしょう。
Sample table
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Jane | Smith |
| Bob | Johnson |
| Samantha | Davis |
| Michael | Rodriguez |
| Tom | Thompson |
| Sara | Johnson |
| David | Jones |
| Jane | Smith |
| Bob | Johnson |
+------------+-----------+Bob JohnsonとJane Smithという名前が表に重複しています。誰が誰なのかを確かめるには追加の識別子が必要ですが、この表では行を一意に識別する方法がありません。読書クラブのメンバー名簿を紙で管理しているのであれば、グループ内で似たような名前を持つ人を区別するために補助的な識別子を付与することができます。.
リレーショナルデータベースでも、テーブル内のすべての行を一意に識別する目的で生成された非実在IDを保持する列を追加することで、同様のことを実現できます。これをMember_idと呼びましょう。.
しかし、読書クラブのメンバーをデータベースに追加するたびに、このような識別子を作成するのは面倒です。この問題を解決するために、MySQLは数値列の自動増分機能を提供しています。この機能により、データベースは列の値を整数のシーケンスで自動的に増分します。.
上記のようなテーブルを作成しましょう。クラブメンバーごとに自動的に割り当てられた番号を保持するために、自動インクリメント列（member_id）を追加します。この自動的に割り当てられた番号がテーブルの主キーとして機能します。
Member_id: この列には、int データ型で表される自動増分数値 ID が含まれます。.
first_name: この列には、クラブ メンバーの名 (ファースト ネーム) が含まれます。これは、50 文字に制限された varchar データ型で表されます。.
last_name: この列にはクラブ メンバーの姓が格納されます。これは 50 文字に制限された varchar データ型で表示されます。.
テーブルを作成するには、次の SQL ステートメントを実行します。
CREATE TABLE club_members (
member_id int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50),
last_name varchar(50)
);PRIMARY KEY文は、単一列の主キーと同様に、列型定義の後に記述しますが、その前にAUTO_INCREMENT属性が追加されます。AUTO_INCREMENT属性は、明示的に指定されていない場合、MySQLにその列の値を昇順の数値列を使用して自動的に生成するように指示します。.
注: 列定義の AUTO_INCREMENT プロパティはMySQLに固有のものです。他のデータベースでも、連続キーを生成するための同様の方法が提供されることがよくありますが、構文はエンジンによって異なります。不明な場合は、RDBMSの公式ドキュメントを参照することをお勧めします。.
次の出力が印刷された場合、テーブルは作成されています。
Output
Query OK, 0 rows affected (0.00 sec)その後、次の INSERT INTO 操作を実行して、上記の例で示したサンプル行をテーブルにロードします。
INSERT INTO club_members (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Bob', 'Johnson'),
('Samantha', 'Davis'),
('Michael', 'Rodriguez'),
('Tom', 'Thompson'),
('Sara', 'Johnson'),
('David', 'Jones'),
('Jane', 'Smith'),
('Bob', 'Johnson');INSERT ステートメントには列名のリスト (first_name と last_name) が含まれるようになりました。これにより、データベースはデータセットに Member_id 列が提供されていないことを認識し、代わりにそのデフォルト値を取得するようになります。.
データベースは成功メッセージで応答します:
Output
Query OK, 10 rows affected (0.002 sec)
Records: 10 Duplicates: 0 Warnings: 0SELECT ステートメントを使用して、新しく作成されたテーブルのデータを検証します。
SELECT * FROM club_members;
出力には、セクションの冒頭にあるものと同様の表が表示されます。
Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
+-----------+------------+-----------+
10 rows in set (0.000 sec)ただし、今回は結果に Member_id 列が表示され、1 から 10 までの一連の数字が含まれます。この列では、それぞれの名前が一意の識別子 (Member_id) に関連付けられているため、Jane Smith と Bob Johnson の重複行は認識できなくなります。.
ここで、データベースで別の Tom Thompson をクラブの会員リストに追加できるかどうかを確認しましょう。
INSERT INTO club_members (first_name, last_name) VALUES ('Tom', 'Thompson');
MySQL は成功メッセージで応答します:
Output
Query OK, 1 row affected (0.009 sec)
データベースが新しいエントリに割り当てた数値 ID を確認するには、SELECT クエリを再度実行します。
SELECT * FROM club_members;
出力にはもう 1 行あります。
Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
| 11 | Tom | Thompson |
+-----------+------------+-----------+
11 rows in set (0.000 sec)データベースの AUTO_INCREMENT 属性により、新しい行の Member_id 列に番号 11 が自動的に割り当てられました。.
操作しているデータに主キーの自然な候補がなく、データベースに新しいデータを追加するたびに独自の識別子を作成したくない場合は、順番に生成された識別子を主キーとして安全に使用できます。.
結果
このガイドでは、主キーとは何か、そしてMySQLでデータベーステーブル内の一意の行を識別するための一般的な型を作成する方法を学びました。自然主キーの作成、複数の列にまたがる主キーの作成、そして自然キーが存在しない場合に自動増分シーケンシャルキーを使用する方法について学びました。.
