PostgreSQL: PostgreSQLのテーブル作成と変更

-- emailの一意性と自動タイムスタンプ付きのユーザーテーブル作��
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 価格 > 0 チェック付きの商品テーブル作成
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB DEFAULT '{}'::jsonb
);

-- 外部キーとステータスチェック付きの注文テーブル作成
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraint ...],
    ...
    [, table_constraint ...]
);

-- 複合主キー
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- カスケード付きの名前付き外部キー
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- 単一カラム主キー（最も一般的）
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 複合主キー（ジャンクションテーブル用）
CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (product_id, tag_id)
);

CREATE TABLE

-- 注文明細: 注文削除→その全明細も削除（CASCADE）
-- 商品: 商品削除→product_idをNULLに設定（SET NULL）
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
    quantity INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE

-- 価格は正の値、割引額は価格を超えない
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    discount_price DECIMAL(10, 2) CHECK (discount_price > 0),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    -- PGのCHECKは同じ行の他のカラムを参照可能！
    CHECK (end_date > start_date)
    -- 注意: CHECKはテーブルをまたぐサブクエリを参照できません。テーブル間の検証にはトリガーが必要です
);

CREATE TABLE

-- 新規カラム追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- デフォルト値付きで追加
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- カラム型の変更（互換性のない型にはUSINGが必要）
ALTER TABLE users ALTER COLUMN phone TYPE TEXT;

-- カラム名変更
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- デフォルト値設定
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- CHECK制約の追加
ALTER TABLE products ADD CONSTRAINT price_reasonable
    CHECK (price < 100000);

-- カラム削除
ALTER TABLE users DROP COLUMN IF EXISTS phone_number;

-- テーブル名変更
ALTER TABLE users RENAME TO customers;

-- SQL statement executed successfully

-- 安全な削除（テーブルが存在しなくてもエラーにならない）
DROP TABLE IF EXISTS test_table;

-- カスケード削除（ビューなどの依存オブジェクトも��除）
DROP TABLE IF EXISTS users CASCADE;

-- SQL statement executed successfully

# 現在のデータベースの全テーブル一覧
\dt

# usersテーブルの構造表示
\d users

                                        Table "public.users"
   Column    |          Type          | Collation | Nullable |              Default
-------------+------------------------+-----------+----------+-----------------------------------
 id          | integer                |           | not null | nextval('users_id_seq'::regclass)
 email       | character varying(255) |           | not null |
 name        | character varying(100) |           | not null |
 password_hash | character(60)        |           | not null |
 created_at  | timestamp with time zone |         |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

-- ============================================
-- 完全なサンプル: ECコアテーブル
-- 適切な制約付きのユーザー、商品、注文テーブル
-- ============================================

-- 1. ユーザーテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    role VARCHAR(20) DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'manager')),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. 商品テーブル
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    attributes JSONB DEFAULT '{}'::jsonb,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. 外部キー付き注文テーブル
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    shipping_address TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. 注文明細テーブル（ジャンクションテーブル）
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    subtotal DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- 5. サンプルデータ挿入
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$dummyhashforalice1234567890abcdefghijklmnopqr'),
    ('bob@example.com', 'Bob', '$2a$12$dummyhashforbob1234567890abcdefghijklmnopqrstuv');

INSERT INTO products (name, price, stock, category, attributes) VALUES
    ('Running Shoes', 89.99, 150, 'Footwear', '{"color": "red", "size": 42}'::jsonb),
    ('Laptop Backpack', 49.99, 300, 'Accessories', '{"color": "black", "material": "nylon"}'::jsonb);

-- 6. テーブル構造の確認
\d users
\d products
\d orders
\d order_items