🐘

SQLのリレーションとは？

2024/05/24に公開

PostgreSQL

supabase

tech

Tips💡

リレーションシップについて理解するためには、実際にSQLを学ぶことが一番の近道です。リレーションシップとは、複数のテーブル間でデータを関連付けることを指します。これは、特定の"キー"を使用して異なるテーブルのデータを結びつけ、必要な形式でデータを取得するための手法です。

例えば、ユーザーテーブルとポストテーブルがあるとします。ユーザーテーブルにはユーザーの情報が、ポストテーブルには各ユーザーが投稿した内容が格納されています。ここで、特定のユーザーが投稿した内容を取得したい場合、ユーザーテーブルとポストテーブルの間にリレーションシップを設定します。このリレーションシップは、ポストテーブルの'user_id'フィールド（これが"キー"となります）を通じてユーザーテーブルと結びつけられます。

このように、リレーションシップを理解し活用することで、複数のテーブルに分散したデータを一元的に扱い、より複雑なデータ操作を行うことが可能になります。

まず、ユーザーテーブル（users）とポストテーブル（posts）を作成します。ユーザーテーブルにはid、name、created_atのフィールドを、ポストテーブルにはid、user_id、content、created_atのフィールドを設定します。user_idはユーザーテーブルのidを参照する外部キーとします。

次に、ダミーデータを各テーブルに3件ずつ挿入します。

最後に、ユーザーテーブルからポストテーブルの情報を取得するためのSQLクエリを作成します。

以下に、これらの手順を実行するSQLコマンドを示します：

-- ユーザーテーブルの作成
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ポストテーブルの作成
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  content TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ダミーデータの挿入
INSERT INTO users (name) VALUES ('田中太郎'), ('山田花子'), ('佐藤次郎');
INSERT INTO posts (user_id, content) VALUES (1, 'カフェに行った'), (2, '電子工作をやった'), (3, '神戸観光をした');

-- ユーザーテーブルからポストテーブルの情報を取得
SELECT users.*, posts.content FROM users JOIN posts ON users.id = posts.user_id;

id1のユーザーの投稿データを追加

INSERT INTO posts (user_id, content) VALUES (1, '渋谷ヒカリエに行った'), (1, '技術同人誌を買いにく'), (1, 'ライブに行こう');

id1のユーザーの情報だけ参照して、投稿データを取得する。

SELECT users.*, posts.content 
FROM users 
JOIN posts ON users.id = posts.user_id 
WHERE users.id = 1;

このクエリでは、usersテーブルとpostsテーブルをuser_idで結合し、その結果からusers.idが1のレコードを抽出しています。結果として、IDが1のユーザーの情報と、そのユーザーが投稿したポストの内容が取得できます。

このSQLクエリをSupabaseのSQLエディタに貼り付けて実行することで、指定したユーザーの情報とそのユーザーのポスト情報を取得できます。

確認方法

リレーションが正しく張られているかを確認するためには、information_schemaというシステムカタログを使用します。これはPostgreSQLが提供するメタデータを格納するための特別なスキーマで、テーブル、列、データ型、リレーションなどの情報を提供します。

以下のクエリは、usersテーブルとpostsテーブル間のリレーションを確認します：

SELECT 
  conname AS constraint_name, 
  conrelid::regclass AS table_name, 
  a.attname AS column_name
FROM 
  pg_constraint AS c 
JOIN 
  pg_attribute AS a ON a.attnum = ANY(c.conkey)
WHERE 
  confrelid = 'users'::regclass AND 
  conrelid = 'posts'::regclass;

このクエリは、usersテーブルとpostsテーブル間に存在する外部キー制約を検索します。制約名、テーブル名、列名を結果として返します。

このSQLクエリをSupabaseのSQLエディタに貼り付けて実行することで、リレーションが正しく張られているかを確認できます。

Tips💡

確認方法

Discussion