【Function編】SQLでテーブル作成と更新

Functionでテーブル操作を行いますが、その前に操作用のテーブルをSQLで作る学習をしてみます。以前はテンプレートでテーブルを作りましたが、今回は独自のテーブルをSQLで作ってみます。こちらの記事を参考にさせていただきました。

Supabaseのデータベースを使うときに役立つ情報

テーブルの想定

ゲームソフト名・ジャンル・発売年を格納したテーブルを作ろうと思います。

かつ、ゲームジャンルの表示は別のゲームジャンル名テーブルを持ち、そこに参照をする形を想定しています。

gamegenre(ゲームジャンル)

ゲームジャンルとその名前を格納したテーブルです。

• genre_id…ジャンルidです。このテーブルのPRIMARY KEY（主キー）です。int型です。
• genre_name…ゲームジャンルの名前です。32文字以内の文字列となります。

全てNullを非許容とします。

softrelease

ゲームソフトとそのジャンル、発売年を格納したテーブルです。ジャンルは先ほどのgame_genreテーブルのgenre_idの参照としています。

• soft_id…ゲームソフトのidです。このテーブルのPRIMARY KEY（主キー）です。int型です。
• genre_id…ゲームジャンルのidで、上のgame_genreテーブルのgenre_idを参照する形になります。
• title_name…ゲームジャンルの名前です。32文字以内の文字列となります。
• release_year…ゲームソフトの発売年です。int型です。

単一のテーブル作成

左メニューのSQLEditorのアイコンをクリック、New Queryを押して新しいクエリ―を作ります。名前を「TableAdd」と変えてみましょう。

その後クエリエディタに記載します。こちらが上で想定した「gamegenre」テーブルを作る命令となります。

CREATE TABLE public.gamegenre (
    genre_id int PRIMARY KEY,
    genre_name varchar(32) NOT NULL
);

これでRunボタンを押して、成功メッセージが出るとテーブルが作成されます。

左メニューのTable Editorを選ぶと定義した「gamegenre」テーブルが出来ている事が確認出来ます。無い場合はAll Tablesの横にある更新ボタンを押してみてください。

次のステップに進むためにこのテーブルは削除します。テーブルの横にある▼マークをクリック、「Delete Table」を選択すると消去されます。（他のテーブルを間違って消さないように気をつけてください）

複数のテーブルをまとめて作成

今度はTableAddの中身をまるっと下記に書き換えます。2つのCREATE TABLE文があると思いますがこれがまとめて実行されます。

CREATE TABLE public.gamegenre (
    genre_id int PRIMARY KEY,
    genre_name varchar(32) NOT NULL
);

CREATE TABLE public.softrelease (
    soft_id int PRIMARY KEY,
    genre_id int REFERENCES public.gamegenre (genre_id) NOT NULL,
    title_name varchar(32) NOT NULL,
    release_year int NOT NULL
);

データをSupabase上で入れる

gamegenreにダミーデータを入れてみる

Table Editorに移動して、ダミーのデータを入れてみましょう。gamegenreテーブルを選択して右上の「Insart row」ボタンを押します。

そうすると横から行追加用のウィジェットが出現します。ここに下記を入れて下にあるSaveを押します。

• genre_id…1
• genre_name…アクション

これで追加されました。

同じく、id2のRPGとid3のレースも入れましょう。

softreleaseにダミーデータを入れてみる

今度はsoftreleaseテーブルにダミーデータを入れます。

• soft_id…1
• genre_id…1
• title_name…FF7
• release_year…1997

ここで、genre_idの横にある「View data」をクリックすると、参照しているgenre_idのデータが確認出来ます。

また、genre_idにgamegenreのidに未定義のid（例えば5とか）を入れると、エラーが出て保存が出来ないようになっています。

データの消去と消去時の注意

ここもSQLで自動化するので再度、2つのテーブルを消します。

消す時の注意として、参照されているテーブルは消す事が出来ません。なのでgamegenre側を先に消すようにしてください。

クエリで行の挿入

今度はTableAddの中身をこのように書き換えます。

CREATE TABLE public.gamegenre (
    genre_id int PRIMARY KEY,
    genre_name varchar(32) NOT NULL
);

CREATE TABLE public.softrelease (
    soft_id int PRIMARY KEY,
    genre_id int REFERENCES public.gamegenre (genre_id) NOT NULL,
    title_name varchar(32) NOT NULL,
    release_year int NOT NULL
);

INSERT INTO public.gamegenre
(genre_id, genre_name)
VALUES
(1, 'アクション'),
(2, 'RPG'),
(3, 'レース');

INSERT INTO public.softrelease
(soft_id, genre_id, title_name, release_year)
VALUES
(1, 2, 'FF7', 1997),
(2, 1, 'スマブラDX', 2001),
(3, 2, 'キングダムハーツ', 2002),
(4, 3, 'マリオカート64', 1996),
(5, 1, 'モンハンワールド', 2018);

上の2つのCREATE TABLEは前のままですが、その下のINSERT INTO public.gamegenre以下が初出で行の追加を行っています。

【1】　INSERT INTO public.gamegenre
【2】　(genre_id, genre_name)
【3】　VALUES
      (1, 'アクション'),
      (2, 'RPG'),
      (3, 'レース');

• 【1】INSERT INTO public.追加するテーブル名となります。
• 【2】追加したい列名を宣言します。今回はgenre_id列とgenre_name列への記載を意図します。
• 【3】VALUESの後に【2】で宣言した項目に対しての追加の値を記載します。

実行

実行するとテーブルが作られ、更にその中に記載した行が追加されている事が確認出来ます。