SQLで主キーを使用する方法を説明してください。

筆者は、Write for Donationsプログラムの一環として、寄付金を受けるためにDiversity in Tech Fundを選びました。

イントロダクション

リレーショナルデータベースの貴重な特徴の一つは、データを明確に定義された構造に整形することです。この構造は、固定された列を持つテーブルを使用し、厳密に定義されたデータ型を遵守し、各行が同じ形を持つようにすることで実現されます。データをテーブルの行として保存する際には、それらを明確に特定し、曖昧さなく参照することが同じく重要です。構造化クエリ言語（SQL）では、これは個々の行をテーブル内で識別するためのプライマリキーで実現することができます。

このチュートリアルでは、主キーについて学び、データベースのテーブル内のユニークな行を特定するためにいくつかの異なる種類を使用します。いくつかのサンプルデータセットを使用して、単一の列や複数の列、自動連番のキーに主キーを作成します。

前提条件 (ぜんていじょうけん)

このガイドに従うには、SQLベースの関係型データベース管理システム（RDBMS）を実行するコンピュータが必要です。このガイドの手順や例は、以下の環境で検証されました。

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.
• Basic familiarity with executing SELECT queries to retrieve data from the database as described in our How To SELECT Rows FROM Tables in SQL guide.

以下のネイティブな日本語の1つのオプションを使用して言い換えてください：
あなたはまた、プライマリキーの使用を示すテーブルを作成できる空のデータベースが必要です。このガイド全体の例で使用されるテスト用データベースを作成するためのMySQLサーバーへの接続と設定に関する詳細については、「MySQLへの接続とサンプルデータベースの設定」のセクションを参照することをお勧めします。

MySQLに接続してサンプルデータベースを設定する。

このセクションでは、MySQLサーバーに接続し、サンプルデータベースを作成することで、このガイドの例に従うことができます。

もしSQLデータベースシステムがリモートサーバー上で稼働している場合、ローカルマシンからサーバーにSSH接続してください。

1. ssh sammy@your_server_ip

 

その後、MySQLサーバープロンプトを開き、MySQLユーザーアカウントの名前である” sammy “を入力してください。

1. mysql -u sammy -p

 

「primary_keys」という名前のデータベースを作成してください。

1. CREATE DATABASE primary_keys;

 

データベースが正常に作成された場合、次のような出力を受け取ります。

Query OK, 1 row affected (0.01 sec)

主キーのデータベースを選択するには、次のUSE文を実行してください。

1. USE primary_keys;

 

次の出力を受け取ります。

Database changed

データベースを選択した後、その内部にサンプルテーブルを作成することができます。それでは、ガイドの残りの部分に従い、MySQLで主キーを使った作業を始める準備が整いました。

主キーについての概要

データベース内のデータは、個々の行の特定で統一的な構造を持つテーブルに格納されます。テーブルの定義には、どのような列があるか、個々の列に保存できるデータ型が記述されています。これだけでも、データベース内の情報を格納し、後でWHERE句を使用して異なるフィルタリング条件を使って検索することができます。ただし、この構造では1つの行を明確に見つける可能性を保証しません。

公道を走行することが許可されている全登録車両のデータベースを想像してみてください。このデータベースには、車のブランド、モデル、製造年、塗装の色などの情報が含まれます。ただし、2007年製の赤いシボレーカマロを探している場合は、複数の車が見つかる可能性があります。なぜなら、自動車メーカーは似たような車種を複数の顧客に販売するからです。そのため、登録車には車両ごとに固有のナンバープレート番号が割り当てられています。もしも、ナンバープレートが「OFP857」の車を調べた場合、この基準ではただ1台の車が見つかることが確実です。なぜなら、法律でナンバープレート番号が登録車両を一意に特定するものとされているからです。リレーショナルデータベースでは、このようなデータのことをプライマリキーと呼びます。

主キーは、データベーステーブルの各行を一意に特定することができる、単一の列または複数の列に存在する一意の識別子です。主キーは、いくつかのルールによって技術的な特性が反映されます。

• A primary key must use unique values. If the primary key consists of more than one column, the combination of values in these columns must be unique across the whole table. Since the key is meant to identify every row uniquely, it can’t appear more than once.
• A primary key must not contain NULL values.
• Each database table can use only one primary key.

テーブルに主キーが定義されている場合、データベースエンジンはこれらのルールを強制するため、それらの特性が真であることを信頼できます。

これらの技術的な特性に加えて、データの内容も考慮して、どのようなデータが主キーに適した選択肢かを決める必要があります。ナチュラルキーはデータセット内で既に存在する識別子であり、サロゲートキーは人工的に作られた識別子です。

一部のデータ構造には、自然にデータセット内に存在する主キーがあります。例えば、車のデータベースではナンバープレート番号やアメリカ市民の名簿では社会保障番号などがあります。時にはこれらの識別子は単一の値ではなく、複数の値の組み合わせである場合もあります。例えば、地元の住宅ディレクトリでは、単に通りの名前や番号だけでは家を一意に特定することはできません。同じ通りには複数の家が存在するか、同じ番号が複数の通りに存在することがあります。しかし、通りの名前と番号の組み合わせならば、一意の家の識別子となり得ます。これらの自然に発生する識別子を「ナチュラルキー」と呼びます。

しかし、しばしばデータは単一の列の値または小さなサブセットの列の値だけでは一意に特定できない場合があります。その場合、例えば数の連続やランダムに生成された識別子（UUIDなど）を使用して、人工的な主キーが作成されます。このようなキーは代理キーと呼ばれます。

以下のセクションでは、単一の列または複数の列に基づいて自然キーを作成し、自然キーのオプションがないテーブルに対してサロゲートキーを生成します。

単一の列にプライマリキーを作成する

多くの場合、データセットには、テーブル内の行を一意に識別するために使用できる単一の列が自然に含まれています。これらの場合、データを説明するために自然キーを作成することができます。登録された車のデータベースの先ほどの例に続いて、次の構造を持つテーブルを想像してください。

+—————+———–+————+——-+——+ | 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 | +—————+———–+————+——-+——+

最初と2番目の行は、ともに2018年製の赤いフォード・マスタングを説明しています。車のメーカーとモデルだけでは、車を独自に識別することはできません。両方の場合、ナンバープレートが異なっており、テーブル内の各行に対して良い一意の識別子を提供しています。ナンバープレート番号は既にデータの一部となっているため、それを主キーとして使用することで、自然なキーが作成されます。ナンバープレート列に主キーを使用せずにテーブルを作成すると、いつかデータセットに重複または空のプレートが表示されるリスクがあります。

次に、上記のテーブルに似たテーブルを作成します。その際、license_plate列を主キーとして使用し、以下の列を追加します。

• license_plate: This column holds the license plate number, represented by the varchar data type.
• brand: This column holds the brand of the car, expressed using the varchar data type with a maximum of 50 characters.
• model: This column holds the car’s model, expressed using the varchar data type with a maximum of 50 characters.
• color: This column holds the color, expressed using the varchar data type with a maximum of 20 characters.
• year: This column holds the year the car was made, expressed using the int data type to store numerical data.

「cars」というテーブルを作成するために、次のSQLステートメントを実行してください。

1. CREATE TABLE cars (
2. license_plate varchar(8) PRIMARY KEY,
3. brand varchar(50),
4. model varchar(50),
5. color varchar(20),
6. year int
7. );

 

PRIMARY KEYの節は、license_plateデータ型の定義の後に続きます。単一の列に基づく主キーを扱う場合は、キーを作成するために簡略な構文を使用することができます。この場合、列の定義にPRIMARY KEYを記述します。

もし以下の出力が表示された場合、テーブルが作成されています。

Query OK, 0 rows affected (0.00 sec)

それに続いて、以下のINSERT INTO操作を実行して、上記の例に示されているサンプル行をテーブルにロードしてください。

1. INSERT INTO cars VALUES
2. (‘ABC123’, ‘Ford’, ‘Mustang’, ‘Red’, 2018),
3. (‘CES214’, ‘Ford’, ‘Mustang’, ‘Red’, 2018),
4. (‘DEF456’, ‘Chevrolet’, ‘Camaro’, ‘Blue’, 2016),
5. (‘GHI789’, ‘Dodge’, ‘Challenger’, ‘Black’, 2014);

 

データベースは成功メッセージで応答します。

Query OK, 4 rows affected (0.010 sec) Records: 4 Duplicates: 0 Warnings: 0

新しく作成したテーブルに、予想されたデータと形式が含まれているかを、SELECT文を使用して確認できます。

1. SELECT * FROM cars;

 

出力は、セクションの最初にあるテーブルと似たようなものが表示されます。

+—————+———–+————+——-+——+ | 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. INSERT INTO cars VALUES (‘DEF456‘, ‘Jeep’, ‘Wrangler’, ‘Yellow’, 2019);

 

MySQLは、DEF456のライセンスプレートがプライマリーキーの重複エントリーになることを示すエラーメッセージで応答します。

ERROR 1062 (23000): Duplicate entry ‘DEF456’ for key ‘cars.PRIMARY’

今では、重複したナンバープレートは許可されていないことが確実です。次に、空のナンバープレートを持つ車を挿入できるかどうかを確認してください。

1. INSERT INTO cars VALUES (NULL, ‘Jeep’, ‘Wrangler’, ‘Yellow’, 2019);

 

今回は、データベースが別のエラーメッセージで返答します。

ERROR 1048 (23000): Column ‘license_plate’ cannot be null

これらのデータベースによって強制された2つのルールによって、ライセンスプレートがテーブル内のすべての行を一意に識別することが確実になります。テーブルをライセンスプレートに基づいてクエリする場合、毎回必ず1行が返されることが期待できます。

次のセクションでは、複数列を持つ主キーの使用方法について学びます。

複数の列に主キーを作成する

テーブル内の行を一意に特定するために、1つの列だけでは十分でない場合、複数の列を使用する主キーを作成することができます。

たとえば、個々の家を特定するために道路名や住所番号のいずれかだけでは不十分な家屋登録を想像してみてください。

+——————-+—————+——————-+——+ | 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: This column holds the name of the street where the house is located, represented by the varchar data type limited to 50 characters.
• street_number: This column holds the house’s street number, represented by the varchar data type. This column can store up to 5 characters. It doesn’t use numerical int data type because some street numbers might contain letters (for example, 200B).
• house_owner: This column holds the name of the house’s owner, represented by the varchar data type limited to 50 characters.
• year: This column holds the year the house was built, represented by the int data type to store numerical values.

今回は、単一の列ではなく、street_nameとstreet_numberの両方を使用して主キーを設定します。次のSQL文を実行してください。

1. CREATE TABLE houses (
2. street_name varchar(50),
3. street_number varchar(5),
4. house_owner varchar(50),
5. year int,
6. PRIMARY KEY(street_name, street_number)
7. );

 

今回は、以前の例とは異なり、PRIMARY KEY節は列の定義の下に現れます。PRIMARY KEY文は、括弧内にstreet_nameとstreet_numberという2つの列名を持っています。この構文によって、housesテーブルの主キーが2つの列にまたがって作成されます。

もし以下の出力が表示された場合、テーブルが作成されています。

Query OK, 0 rows affected (0.00 sec)

それに続いて、次のINSERT INTO操作を実行して、前の例で提示されたサンプル行をテーブルに読み込んでください。

1. INSERT INTO houses VALUES
2. (‘Central Park West’, ‘100’, ‘John Doe’, 2014),
3. (‘Broadway’, ‘1500’, ‘Jane Smith’, 2016),
4. (‘5th Avenue’, ‘100’, ‘Bob Johnson’, 2018),
5. (‘Lexington Avenue’, ‘5001’, ‘Samantha Davis’, 2010),
6. (‘Park Avenue’, ‘7000’, ‘Michael Rodriguez’, 2012),
7. (‘Central Park West’, ‘200’, ‘Tom Thompson’, 2015);

 

データベースは成功メッセージで応答します。 (Dētabēsu wa seikō messēji de ōtō shimasu.)

Query OK, 6 rows affected (0.000 sec) Records: 6 Duplicates: 0 Warnings: 0

新しく作成されたテーブルに、予定されたデータと形式が含まれているかを、SELECTステートメントを使用して確認できます。

1. SELECT * FROM houses;

 

出力結果は、セクションの初めにある表と似た表が表示されます。

+——————-+—————+——————-+——+ | 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)

さて、データベースが道路名と番地が重複する行の挿入を許可し、しかしテーブルには重複した住所を制限するかを検証しましょう。パークアベニューに別の家を追加してみましょう。

1. INSERT INTO houses VALUES (‘Park Avenue’, ‘8000’, ‘Emily Brown’, 2011);

 

MySQLは、以前のテーブルに8000 Park Avenueのアドレスが表示されなかったため、成功メッセージを返します。

Query OK, 1 row affected (0.010 sec)

同じような結果が得られます。メインストリート8000番に家を追加する際、通り番号を繰り返すと。

1. INSERT INTO houses VALUES (‘Main Street’, ‘8000’, ‘David Jones’, 2009);

 

再度、住所の全体が重複しないため、新しい行が適切に挿入されます。

Query OK, 1 row affected (0.010 sec)

しかし、以下のINSERT文を使用して、100 5th Avenueに別の家を追加してみてください。

1. INSERT INTO houses VALUES (‘5th Avenue’, ‘100’, ‘Josh Gordon’, 2008);

 

データベースは、5番街と100の値の組み合わせに対して主キーの重複エントリがあることを通知するエラーメッセージで応答します。

ERROR 1062 (23000): Duplicate entry ‘5th Avenue–100‘ for key ‘houses.PRIMARY’

データベースは、主キールールを正しく実施しており、キーは2つの列に定義されています。テーブル内には、ストリート名と番地から成る住所が重複することはありませんので、安心してください。

このセクションでは、ハウステーブルの各行を一意に識別するために、2つの列で自然キーを作成しました。しかし、データセットから常にプライマリキーを導出することはできません。次のセクションでは、データから直接的には得られない人工的なプライマリキーを使用します。

シーケンシャルなプライマリキーを作成する。

これまでは、サンプルデータセット内の既存の列を使用してユニークな主キーを作成してきました。しかし、いくつかの場合では、データが重複してしまい、どの列も良い一意の識別子として機能しなくなります。このような場合には、生成された識別子を使用して連続的な主キーを作成することができます。手元のデータを一意に識別するために新しい識別子を考案する必要がある場合、その人工的な識別子で作成された主キーはサロゲートキーと呼ばれます。

ある日、本のクラブのメンバーのリストを思い浮かべてください。ここでは、官庁発行の身分証明書を見せる必要はなく、誰でも参加できる、カジュアルな集まりです。同じ名前の人々がいつかクラブに参加する可能性もあります。

+————+———–+ | first_name | last_name | +————+———–+ | John | Doe | | Jane | Smith | | Bob | Johnson | | Samantha | Davis | | Michael | Rodriguez | | Tom | Thompson | | Sara | Johnson | | David | Jones | | Jane | Smith | | Bob | Johnson | +————+———–+

テーブルには、ボブ・ジョンソンとジェーン・スミスという名前が繰り返されています。誰が誰かを確実に特定するために、追加の識別子を使用する必要があり、そのテーブルでは行を一意に特定することはできません。もし本のクラブメンバーのリストを紙で保持している場合、同じ名前の人々を区別するために補助識別子を使用することができます。

リレーショナルデータベースでは、追加の列を使用して、テーブル内のすべての行を一意に区別するための目的だけで生成された事実のない識別子を保持することで、同様のことができます。member_idと呼びましょう。

しかし、データベースにもう一人の読書クラブメンバーを追加したい場合に、そのような識別子を考えることは負担になります。この問題を解決するために、MySQLは自動的に数値が増加する列の自動インクリメント機能を提供しています。

上記に表示されているテーブルに似たものを作りましょう。追加の自動増分カラム（member_id）を作成し、クラブメンバーごとに自動的に割り当てられる番号を保持します。その自動的に割り当てられる番号は、テーブルの主キーとして機能します。

• member_id: This column holds an auto-incremented, numerical identifier represented by the int data type.
• first_name: This column holds the first name of the club members, represented by the varchar data type limited to 50 characters.
• last_name: This column holds the last name of the club members, represented by the varchar data type limited to 50 characters.

テーブルを作成するために、以下のSQL文を実行してください。

1. CREATE TABLE club_members (
2. member_id int AUTO_INCREMENT PRIMARY KEY,
3. first_name varchar(50),
4. last_name varchar(50)
5. );

 

PRIMARY KEY節は、単一の列の主キーと同様に、列の型定義の後に表示されますが、その前に追加の属性が表示されます：AUTO_INCREMENTです。これはMySQLに対して、列の値を自動的に生成するよう指示し、明示的に指定されていない場合は、成長する数列を使用します。

もし以下の出力が表示されれば、テーブルが作成されています。

Query OK, 0 rows affected (0.00 sec)

その後、次のINSERT INTO操作を実行することにより、例に示されたサンプル行をテーブルにロードしてください。

1. INSERT INTO club_members (first_name, last_name) VALUES
2. (‘John’, ‘Doe’),
3. (‘Jane’, ‘Smith’),
4. (‘Bob’, ‘Johnson’),
5. (‘Samantha’, ‘Davis’),
6. (‘Michael’, ‘Rodriguez’),
7. (‘Tom’, ‘Thompson’),
8. (‘Sara’, ‘Johnson’),
9. (‘David’, ‘Jones’),
10. (‘Jane’, ‘Smith’),
11. (‘Bob’, ‘Johnson’);

 

INSERT文には、カラム名（first_nameとlast_name）のリストが含まれており、これによってデータベースはメンバーIDカラムがデータセットに含まれていないことを知ることができます。そのため、代わりにデフォルト値を使用する必要があります。

データベースは成功メッセージで応答します。 (De-ta-be-su wa seikou messēji de ōto shimasu.)

Query OK, 10 rows affected (0.002 sec) Records: 10 Duplicates: 0 Warnings: 0

新しく作成されたテーブルのデータを確認するためにSELECT文を使用してください。

1. SELECT * FROM club_members;

 

出力は、セクションの最初にある表に似た表を表示します。

+———–+————+———–+ | 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）と関連付けられているからです。

さて、データベースがクラブメンバーリストにもう1人のトム・トンプソンの追加を許可するか、確認しましょう。

1. INSERT INTO club_members (first_name, last_name) VALUES (‘Tom‘, ‘Thompson‘);

 

MySQLは成功メッセージで応答します。

Query OK, 1 row affected (0.009 sec)

新しいエントリーにデータベースが割り当てた数値識別子を確認するには、再びSELECTクエリを実行してください。

1. SELECT * FROM club_members;

 

出力にはもう1行あります。

+———–+————+———–+ | 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機能により、新しい行が自動的にメンバーID列に11番として割り当てられました。

データベースに追加するたびに特定の主キー候補がなく、新しいデータに都度創造的な識別子を提供することを望まない場合、連続的に生成された識別子を主キーとして安全に利用できます。

結論を言えば

このガイドに従うことで、プライマリキーとは何か、MySQLで一意の行を特定するために一般的に作成されるタイプの方法を学びました。あなたは自然なプライマリキーを作成し、複数の列にまたがるプライマリキーを作成し、自然キーが存在しない場合には自動増分シーケンシャルキーを使用しました。

データベースの構造をより具体的にするために、主キーを使用することができます。主キーを使うことで、データの行は一意に識別できるようになります。このチュートリアルでは主キーの基本的な使い方のみを説明しましたが、さらに学びたい場合はMySQLの制約に関するドキュメンテーションを参照してください。また、SQL制約の理解やSQLでの制約の使い方についても弊社のガイドを参照することができます。

SQL言語とその使い方についてより詳しく学びたい場合は、『SQLの使い方シリーズ』の他のガイドをチェックしてみることをおすすめします。