DDL (データ定義言語)

DDL (Data Definition Language) は、データベースの「設計図」にあたる様々なオブジェクトを定義するための言語です。DDLを使うことで、データベース、テーブル、インデックス、ビューといった構造の作成(CREATE)、変更(ALTER)、削除(DROP)を行います。

このページでは、Google Colaboratory環境で実際に手を動かしながら、主要なDDLコマンドの使い方を学びます。

ユーザーとロールの管理

データベースを操作するユーザーや、権限をまとめたグループ（ロール）を管理します。PostgreSQLでは、ユーザーはログイン権限を持つロールとして扱われ、CREATE USERはCREATE ROLE ... WITH LOGINとほぼ同じ意味を持ちます。

本サイトではスーパーユーザーであるpostgresを主に使用しますが、ここではパスワードを設定する例を示します。

# 'postgres'ユーザーのパスワードを'password'に設定
!sudo -u postgres psql -U postgres -c "ALTER ROLE postgres WITH PASSWORD 'password';"

主なコマンド

CREATE ROLE role_name; / CREATE USER user_name;: 新しいロール/ユーザーを作成します。
ALTER ROLE role_name ...; / ALTER USER user_name ...;: ロール/ユーザーの設定を変更します。
DROP ROLE role_name; / DROP USER user_name;: ロール/ユーザーを削除します。

データベースの管理

テーブルなどのオブジェクトを格納する最上位の器であるデータベースを管理します。

# 'quiz'という名前のデータベースを作成
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE quiz;'

主なコマンド

CREATE DATABASE db_name;: 新しいデータベースを作成します。
ALTER DATABASE db_name ...;: データベースの設定（名前、所有者など）を変更します。
DROP DATABASE db_name;: データベースを削除します。

テーブルの管理

実際に行と列で構成されるデータを格納する、最も基本的なオブジェクトであるテーブルを管理します。

テーブル作成(CREATE TABLE)の例

本サイトで使用するクイズアプリケーションのテーブル群を作成するコマンドです。主キー(PRIMARY KEY)や外部キー(REFERENCES)といった制約の定義方法に注目してください。


# 各テーブルを作成するコマンド (一つのセルにまとめて実行可能)
# ※ -c の後のクエリはシングルクォートで囲みます
COMMAND_BODY="
  CREATE TABLE public.quiz_view_type (
    id SMALLSERIAL PRIMARY KEY,
    quiz_view_type_name VARCHAR(512) NOT NULL
  );
  CREATE TABLE public.ipa_type (
    id SMALLSERIAL PRIMARY KEY,
    ipa_type_name VARCHAR(512) NOT NULL
  );
  CREATE TABLE public.quiz_class (
    id SMALLSERIAL PRIMARY KEY,
    quiz_class_name VARCHAR(512) NOT NULL
  );
  CREATE TABLE public.quiz_type (
    id SMALLSERIAL PRIMARY KEY,
    quiz_type_name VARCHAR(512) NOT NULL,
    quiz_class_id SMALLINT NOT NULL REFERENCES quiz_class(id)
  );
  CREATE TABLE public.answer (
    answer_group_id BIGINT NOT NULL,
    consecutive_numbers SMALLINT NOT NULL,
    answer VARCHAR(4096) NOT NULL,
    PRIMARY KEY (answer_group_id, consecutive_numbers)
  );
  CREATE TABLE public.quiz (
    id BIGSERIAL PRIMARY KEY,
    quiz VARCHAR(4096) NOT NULL,
    quiz_image_url VARCHAR(512),
    quiz_type SMALLINT NOT NULL REFERENCES quiz_type(id),
    quiz_view_type SMALLINT NOT NULL REFERENCES quiz_view_type(id),
    answer_group_id BIGINT NOT NULL,
    right_consecutive_numbers SMALLINT NOT NULL,
    answer_explanation VARCHAR(8192),
    reference_url VARCHAR(512),
    FOREIGN KEY (answer_group_id, right_consecutive_numbers) REFERENCES answer(answer_group_id, consecutive_numbers)
  );
  CREATE TABLE public.app_user (
    id BIGSERIAL PRIMARY KEY,
    nickname VARCHAR(50) NOT NULL,
    password VARCHAR(4096) NOT NULL,
    sex SMALLINT NOT NULL,
    age SMALLINT NOT NULL,
    create_time TIMESTAMP NOT NULL
  );
  CREATE TABLE public.quiz_history (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES app_user(id),
    quiz_id BIGINT NOT NULL REFERENCES quiz(id),
    answer_group_id BIGINT NOT NULL,
    answer_id SMALLINT NOT NULL,
    quiz_view_time BIGINT NOT NULL,
    quiz_answer_time BIGINT NOT NULL,
    is_correct BOOLEAN NOT NULL,
    FOREIGN KEY (answer_group_id, answer_id) REFERENCES answer(answer_group_id, consecutive_numbers)
  );
"
!sudo -u postgres psql -U postgres -d quiz -c "$COMMAND_BODY"

主なコマンド

CREATE TABLE table_name (...);: 新しいテーブルを作成します。
ALTER TABLE table_name ...;: テーブル定義（カラムの追加/変更/削除、制約の変更など）を変更します。
DROP TABLE table_name;: テーブルを削除します。
TRUNCATE TABLE table_name;: テーブル内の全レコードを高速に削除します（テーブル自体は残ります）。

インデックスの管理

テーブルからのデータ検索速度を向上させるためのオブジェクトです。特定のカラムをキーとして索引を作成します。

-- quizテーブルのquiz_typeカラムにインデックスを作成
CREATE INDEX idx_quiz_quiz_type ON quiz(quiz_type);

ビューの管理

ビューは、一つ以上のテーブルに対するSELECT文の結果を、あたかも一つの仮想的なテーブルのように見せる仕組みです。複雑なクエリを単純化したり、見せたくないカラムを隠してセキュリティを確保したりする目的で使われます。

-- quizテーブルからIDと問題文だけを抽出するビューを作成
CREATE VIEW v_quiz_summary AS SELECT id, quiz FROM quiz;

その他のオブジェクト

シーケンス: 一意の連番を生成するためのオブジェクト。SERIAL型などを使うと内部的に自動作成されます。
トリガーと関数: テーブルへのINSERTやUPDATEなどのイベントをきっかけに、自動的に実行される処理（関数）を定義します。
プロシージャ: 一連の処理をまとめたもので、CALLコマンドで呼び出します。関数と似ていますが、値を返さない点が主な違いです。