【保存版】MySQLでユーザーとプロフィールテーブルを紐づける！1対1リレーションシップの3つの方法

2024-07-03

MySQLにおける1対1リレーションシップ

データベース設計において、エンティティ間の関係を定義することは重要です。エンティティ間の最も基本的な関係の1つが、1対1リレーションシップです。これは、あるエンティティのインスタンスが別のエンティティのインスタンスと厳密に1対1で関連付けられていることを意味します。

MySQLで1対1リレーションシップを表現する方法はいくつかあります。2つの一般的な方法は次のとおりです。

外部キー制約を使用する

最も一般的な方法は、外部キー制約を使用することです。これは、従属テーブルの列を主テーブルの列に関連付ける制約です。これにより、従属テーブルの各レコードが主テーブルの1つのレコードにのみ関連付けられていることが保証されます。

例：

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE profiles (
  user_id INT PRIMARY KEY,
  address VARCHAR(255),
  phone_number VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

この例では、profiles テーブルの user_id 列は users テーブルの user_id 列を参照する外部キーです。これにより、profiles テーブルの各レコードが users テーブルの1つのレコードにのみ関連付けられていることが保証されます。

サブクエリを使用する

もう1つの方法は、サブクエリを使用して1対1リレーションシップをエンフォースすることです。これは、従属テーブルのレコードが主テーブルに存在するレコードと一致するかどうかを確認するために、サブクエリを使用することを意味します。

INSERT INTO profiles (user_id, address, phone_number)
SELECT user_id, address, phone_number
FROM users
WHERE user_id = 1;

この例では、profiles テーブルに新しいレコードを挿入する前に、users テーブルに一致するレコードが存在するかどうかを確認するサブクエリが使用されます。

1対1リレーションシップを使用する際には、次の点に注意する必要があります。

主テーブルと従属テーブルの主キーは同じである必要があります。
外部キー制約を使用する場合は、主テーブルに対応する列にインデックスを作成する必要があります。
サブクエリを使用する場合は、パフォーマンス上の影響を考慮する必要があります。

1対1リレーションシップは、データベース設計において重要な概念です。MySQLで1対1リレーションシップを表現するには、外部キー制約またはサブクエリを使用できます。使用する方法は、特定の要件によって異なります。

サンプルコード：ユーザーとプロフィールテーブル間の1対1リレーションシップ

users テーブル

このテーブルは、ユーザーのID、名前、およびメールアドレスを格納します。

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

profiles テーブル

このテーブルは、ユーザーの住所、電話番号、およびプロフィール写真 (画像) を格納します。

CREATE TABLE profiles (
  user_id INT PRIMARY KEY,
  address VARCHAR(255),
  phone_number VARCHAR(255),
  profile_picture BLOB,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

外部キー制約を使用して users テーブルと profiles テーブルを関連付ける

ALTER TABLE profiles
ADD CONSTRAINT fk_profiles_users FOREIGN KEY (user_id) REFERENCES users(user_id);

ユーザーとプロフィールを作成する

次のステートメントを使用して、新しいユーザーとプロフィールを作成できます。

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]');

INSERT INTO profiles (user_id, address, phone_number, profile_picture)
VALUES (1, '123 Main Street', '555-555-5555', NULL);

このステートメントは、users テーブルに新しいレコードを作成し、user_id を1に設定します。次に、profiles テーブルに新しいレコードを作成し、user_id を1に設定し、address を '123 Main Street'、phone_number を '555-555-5555' に設定します。

SELECT p.address, p.phone_number, p.profile_picture
FROM profiles p
JOIN users u ON u.user_id = p.user_id
WHERE u.name = 'John Doe';

このステートメントは、users テーブルと profiles テーブルを user_id 列で結合し、name が 'John Doe' であるユーザーのプロフィールを返します。

この例は、MySQLで1対1リレーションシップをモデル化する方法を示すほんの一例です。特定の要件に応じて、さまざまな方法で1対1リレーションシップをモデル化できます。

MySQLで1対1リレーションシップを表現するその他の方法

上記に加えて、状況によっては以下の方法も検討できます。

サロゲートキーを使用する

サロゲートキーは、エンティティを一意に識別するために使用される人工的なキーです。1対1リレーションシップの場合、サロゲートキーを使用して従属テーブルの主キーを作成し、主テーブルの主キーを参照する外部キー制約を追加できます。

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE profiles (
  profile_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  address VARCHAR(255),
  phone_number VARCHAR(255),
  profile_picture BLOB,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

この例では、profiles テーブルの profile_id 列はサロゲートキーとして使用されます。user_id 列は、users テーブルの user_id 列を参照する外部キーです。

エンティティをマージする

エンティティ間の関係が非常に密接な場合、エンティティをマージして単一のテーブルを作成することがあります。これにより、冗長性が排除され、テーブル間の結合が簡素化されます。

CREATE TABLE users_profiles (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  address VARCHAR(255),
  phone_number VARCHAR(255),
  profile_picture BLOB
);