Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Day 2

初アドカレにひとりで挑戦🎄🦌🎁 Advent Calendar 2025

@Ryuki4648

sqlファイルに書かれていることを調べてみた（1）

Last updated at Posted at 2025-12-02

はじめに

環境構築にともないデータベースへの接続や、データの作成が必要になりました。
普段sqlファイルに触れる機会が少ないので、調べたことを記載していきます。

経歴

看護師3年 → HTMLコーダー/Webデザイナー 3〜4年 → フロントエンドエンジニア2年弱

目次

全体の記述

hoge_schema.sqlファイル


--
-- PostgreSQL database dump
--

-- Dumped from database version 17.4
-- Dumped by pg_dump version 17.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

ALTER TABLE IF EXISTS ONLY hoge.t_user DROP CONSTRAINT IF EXISTS t_user_pkey;
ALTER TABLE IF EXISTS ONLY hoge.t_user DROP CONSTRAINT IF EXISTS t_user_mail_address_key;
ALTER TABLE IF EXISTS hoge.t_user ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE IF EXISTS hoge.t_user_id_seq;
DROP TABLE IF EXISTS hoge.t_user;
DROP SCHEMA IF EXISTS hoge;
--
-- Name: hoge; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA hoge;


--
-- Name: SCHEMA hoge; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA hoge IS 'standard hoge schema';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t_user; Type: TABLE; Schema: hoge; Owner: -
--

CREATE TABLE hoge.t_user (
    id bigint NOT NULL,
    mail_address character varying(80) NOT NULL,
    name character varying(10) NOT NULL,
    hashed_password character varying(254) NOT NULL,
    user_role_id smallint NOT NULL,
    tenant_id integer,
    del_flg integer,
    failed_login_count integer NOT NULL,
    db_input_date timestamp without time zone NOT NULL,
    db_update_date timestamp without time zone NOT NULL
);


--
-- Name: t_user_id_seq; Type: SEQUENCE; Schema: hoge; Owner: -
--

CREATE SEQUENCE hoge.t_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: t_user_id_seq; Type: SEQUENCE OWNED BY; Schema: hoge; Owner: -
--

ALTER SEQUENCE hoge.t_user_id_seq OWNED BY hoge.t_user.id;


--
-- Name: t_user id; Type: DEFAULT; Schema: hoge; Owner: -
--

ALTER TABLE ONLY hoge.t_user ALTER COLUMN id SET DEFAULT nextval('hoge.t_user_id_seq'::regclass);


--
-- Name: t_user t_user_mail_address_key; Type: CONSTRAINT; Schema: hoge; Owner: -
--

ALTER TABLE ONLY hoge.t_user
    ADD CONSTRAINT t_user_mail_address_key UNIQUE (mail_address);


--
-- Name: t_user t_user_pkey; Type: CONSTRAINT; Schema: hoge; Owner: -
--

ALTER TABLE ONLY hoge.t_user
    ADD CONSTRAINT t_user_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

各コードの説明

1. 環境設定

pg_dumpが生成する実行環境の初期設定です。
※ pg_dump：PostgreSQLデータベースをバックアップするユーティリティ

# SQL の実行時間の上限（ミリ秒）を設定。0はタイムアウトなし（時間制限なし）
SET statement_timeout = 0;

# ロック（テーブルや行の排他）が解除されるのを待つ時間を設定。0はタイムアウトなし
SET lock_timeout = 0;

# トランザクションが待機状態のまま放置される最大時間。0は無制限
SET idle_in_transaction_session_timeout = 0;

# トランザクション全体のタイムアウト
SET transaction_timeout = 0;

# クライアント側の文字コード設定
SET client_encoding = 'UTF8';

# 文字列内のバックスラッシュを標準準拠で扱う設定
SET standard_conforming_strings = on;

# search_path（探索するスキーマの順序）を空にする。スキーマを自動推測せず明示を必須にする
SELECT pg_catalog.set_config('search_path', '', false);

# 関数の中身（PL/pgSQL など）の文法チェックを無効化
SET check_function_bodies = false;

# XMLと文字列値との変換時にDOCUMENT or CONTENTを設定。デフォルトはCONTENT
SET xmloption = content;

# クライアントに表示するログレベルの下限を設定
SET client_min_messages = warning;

# Row Level Security（行レベルセキュリティ）の無効化
SET row_security = off;
  • データベースの初期設定ファイルを作成
  • SQL の実行時設定（実行時間の上限、タイムアウトや文字コードなど） を設定

2. 既存オブジェクトの削除

2-1. 既存の制約を削除

# 主キー（pkey）制約を削除
ALTER TABLE IF EXISTS ONLY hoge.t_user DROP CONSTRAINT IF EXISTS t_user_pkey;

# UNIQUE制約を削除
ALTER TABLE IF EXISTS ONLY hoge.t_user DROP CONSTRAINT IF EXISTS t_user_mail_address_key;

  • hoge：スキーマ名
    hoge.t_userはhogeスキーマのt_userテーブル

  • ALTER TABLE：既存テーブルの構造を変更する
    例）カラム追加や削除、制約追加や削除、デフォルト値の変更

  • IF EXISTS：もし存在していれば、という意味
    対象の制約やテーブルが存在しなくてもエラーにならない

  • ONLY：指定のテーブルだけが対象（子テーブルは対象外）

  • DROP：削除する
    DROP CONSTRAINTは制約の削除

  • CONSTRAINT：テーブルに設定されている制約（ルール）
    例）PRIMARY KEY：主キー
    　　UNIQUE：重複禁止
    　　FOREIGN KEY：外部キー
    　　CHECK：値の条件指定

  • t_user テーブルにある主キー（pkey）制約とメールアドレスのUNIQUE制約を削除

  • hoge.t_user テーブルが存在する場合、それだけを対象に、主キー制約（t_user_pkey）があれば削除するという命令分

2-2. id の DEFAULT（連番設定）解除

ALTER TABLE IF EXISTS hoge.t_user ALTER COLUMN id DROP DEFAULT;
  • ALTER COLUMN id：t_user テーブルの id カラムに対して操作
  • DROP DEFAULT：カラムのデフォルト値を削除
  • hoge.t_user.idテーブルがシーケンスを使って自動採番しているとき、idカラムに設定されている「デフォルト値（自動採番の設定）」を削除する

2-3. シーケンス削除

DROP SEQUENCE IF EXISTS hoge.t_user_id_seq;

2-4. テーブル削除

DROP TABLE IF EXISTS hoge.t_user;

2-5. スキーマ削除

DROP SCHEMA IF EXISTS hoge;
  • hogeスキーマを削除

3. スキーマ再作成

CREATE SCHEMA hoge;

# スキーマにもコメントを付ける
COMMENT ON SCHEMA hoge IS 'standard hoge schema';
  • hogeスキーマを再作成
  • スキーマをまっさらな状態にするための処理

4. テーブル作成時の設定

# 新しく作るテーブルの tablespace のデフォルトを空にする
SET default_tablespace = '';

# テーブルを作成するときのストレージ形式（アクセス方法）を heap にする
SET default_table_access_method = heap;
  • default_tablespace：CREATEコマンドで明示的にテーブル空間を指定していない場合、オブジェクトの作成先となるデフォルトのテーブル空間を指定
  • 空文字 ''は「デフォルトのtablespace（= pg_default）を使う」という意味

5. t_userテーブルの作成

CREATE TABLE hoge.t_user (
    id bigint NOT NULL,
    mail_address character varying(80) NOT NULL,
    name character varying(10) NOT NULL,
    hashed_password character varying(254) NOT NULL,
    user_role_id smallint NOT NULL,
    tenant_id integer,
    del_flg integer,
    failed_login_count integer NOT NULL,
    db_input_date timestamp NOT NULL,
    db_update_date timestamp NOT NULL
);
カラム名 内容
id 主キー、bigint
mail_address メールアドレス（UNIQUEになる）
name 名前
hashed_password パスワードのハッシュ文字列
user_role_id 権限
tenant_id テナント情報
del_flg 削除フラグ
failed_login_count ログイン失敗回数
db_input_date 登録日時
db_update_date 更新日時

6. id用シーケンス作成

CREATE SEQUENCE hoge.t_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

6-1. テーブルのidとシーケンスを紐付け

ALTER SEQUENCE hoge.t_user_id_seq OWNED BY hoge.t_user.id;
  • idを削除した時、このシーケンスも一緒に削除されるようにする

7. 自動採番

# idカラムを自動採番（AUTO INCREMENT）に設定
ALTER TABLE ONLY hoge.t_user ALTER COLUMN id SET DEFAULT nextval('hoge.t_user_id_seq'::regclass);
  • 新しい行を INSERT したとき、t_user_id_seq から自動で次の番号を採番して入れることができる
  • その前の処理で制約削除、シーケンス削除、DROP DEFAULTで自動採番解除しているため、最後に正しいシーケンスを再設定

8. 制約の追加

8-1. UNIQUE制約の追加

# mail_address カラムに「重複禁止」の制約を付ける処理
ALTER TABLE ONLY hoge.t_user
    ADD CONSTRAINT t_user_mail_address_key UNIQUE (mail_address);

8-2. PRIMARY KEY（主キー）制約の追加

# idカラムを主キーとして設定
ALTER TABLE ONLY hoge.t_user
    ADD CONSTRAINT t_user_pkey PRIMARY KEY (id);

参考サイト

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?