Aurora DSQL で外部キー制約を使う

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2)
);

CREATE TABLE products (
    product_no INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    price NUMERIC
);

CREATE TABLE order_items (
    order_id INTEGER PRIMARY KEY,
    product_no INTEGER,
    quantity INTEGER,
    CONSTRAINT fk_product FOREIGN KEY (product_no)
        REFERENCES products (product_no)
        DEFERRABLE INITIALLY IMMEDIATE
);

CREATE TABLE inventory (
    warehouse_id INTEGER,
    product_no INTEGER,
    quantity INTEGER,
    PRIMARY KEY (warehouse_id, product_no)
);

CREATE TABLE shipments (
    shipment_id INTEGER PRIMARY KEY,
    warehouse_id INTEGER,
    product_no INTEGER,
    FOREIGN KEY (warehouse_id, product_no)
        REFERENCES inventory (warehouse_id, product_no)
);

CREATE TABLE tree (
    node_id INTEGER PRIMARY KEY,
    parent_id INTEGER REFERENCES tree,
    name TEXT
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_no INTEGER REFERENCES products ON DELETE RESTRICT,
    quantity INTEGER
);

ALTER TABLE order_items
ADD CONSTRAINT fk_product
FOREIGN KEY (product_no) REFERENCES products (product_no)
NOT VALID;

ALTER TABLE ASYNC order_items
VALIDATE CONSTRAINT fk_product;

ALTER TABLE order_items
ALTER CONSTRAINT fk_product DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE order_items DROP CONSTRAINT fk_product;

SELECT
    conname AS constraint_name,
    conrelid::regclass AS child_table,
    confrelid::regclass AS parent_table,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f';

INSERT INTO products VALUES (1, 'Laptop', 999.99);
INSERT INTO products VALUES (2, 'Mouse', 29.99);
INSERT INTO products VALUES (3, 'Keyboard', 79.99);

-- Valid order referencing an existing product succeeds.
INSERT INTO order_items VALUES (100, 1, 2);

-- Order for non-existent product fails immediately.
INSERT INTO order_items VALUES (101, 999, 1);
-- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product"

DELETE FROM products WHERE product_no = 1;
-- ERROR: update or delete on table "products" violates foreign key constraint "fk_product" on table "order_items"

UPDATE products SET product_no = 99 WHERE product_no = 1;
-- ERROR: update or delete on table "products" violates foreign key constraint "fk_product" on table "order_items"

-- Product 3 has no orders referencing it.
DELETE FROM products WHERE product_no = 3;
-- OK

BEGIN;
SET CONSTRAINTS fk_product DEFERRED;

INSERT INTO order_items VALUES (200, 80, 1); -- product 80 doesn't exist yet
INSERT INTO products VALUES (80, 'Monitor', 349.99);

COMMIT;
-- OK: constraint satisfied at commit time

BEGIN;
SET CONSTRAINTS fk_product DEFERRED;

INSERT INTO order_items VALUES (201, 777, 1); -- product 777 never created

COMMIT;
-- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product"
-- entire transaction rolled back

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO order_items VALUES (202, 90, 1);
INSERT INTO products VALUES (90, 'Speakers', 129.99);

COMMIT;
-- OK

BEGIN;
SET CONSTRAINTS fk_product DEFERRED;
INSERT INTO order_items VALUES (203, 888, 1); -- no error yet (deferred)
SET CONSTRAINTS fk_product IMMEDIATE; -- triggers check NOW
-- ERROR: insert or update on table "order_items" violates foreign key constraint "fk_product"
ROLLBACK;

-- Seed the products used by the concurrency examples
INSERT INTO products VALUES (4, 'Cable', 9.99);
INSERT INTO products VALUES (5, 'Dock', 199.99);
INSERT INTO products VALUES (6, 'Charger', 19.99);

-- Session A:
BEGIN;
DELETE FROM products WHERE product_no = 4;
COMMIT; -- succeeds

-- Session B (started concurrently, before Session A committed):
BEGIN;
INSERT INTO order_items VALUES (103, 4, 1);
COMMIT;
-- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001)

-- Session A:
BEGIN;
UPDATE products SET product_no = 50 WHERE product_no = 5;
COMMIT;

-- Session B (concurrent):
BEGIN;
INSERT INTO order_items VALUES (104, 5, 1);
COMMIT;
-- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001)

-- Session A:
BEGIN;
UPDATE products SET price = 29.99 WHERE product_no = 6;
COMMIT; -- succeeds

-- Session B (concurrent):
BEGIN;
INSERT INTO order_items VALUES (105, 6, 1);
COMMIT; -- succeeds
-- Updating price (a non-key column) doesn't conflict with the foreign key on product_no.

-- Session A:
BEGIN;
UPDATE products SET name = 'Fast Charger' WHERE product_no = 6;
COMMIT;

-- Session B (concurrent):
BEGIN;
INSERT INTO order_items VALUES (108, 6, 1);
COMMIT;
-- ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001)

-- Session A:
BEGIN;
INSERT INTO order_items VALUES (106, 2, 1);
COMMIT;

-- Session B (concurrent):
BEGIN;
INSERT INTO order_items VALUES (107, 2, 2);
COMMIT;
-- OK: both succeed

-- AVOID: CASCADE on unbounded relationship
CREATE TABLE order_items (
    line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products ON DELETE CASCADE, -- dangerous at scale
    quantity INTEGER
);

-- PREFERRED: RESTRICT + soft-delete pattern
CREATE TABLE order_items (
    line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID REFERENCES products ON DELETE RESTRICT,
    quantity INTEGER
);

-- Soft-delete the product instead of hard-deleting:
UPDATE products SET is_active = false WHERE product_id = 'prod-xyz'; -- replace with actual UUID
-- Clean up old order_items in batched transactions (≤3,000 rows each)

CREATE TABLE order_items (
    line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders,
    product_id UUID REFERENCES products,
    quantity INTEGER NOT NULL,
    -- Snapshot mutable attributes at order time
    unit_price NUMERIC NOT NULL,
    product_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- AVOID: unbounded query without time bounds or pagination
SELECT * FROM order_items WHERE product_id = 'prod-xyz'; -- replace with actual UUID

-- PREFERRED: time-bounded with index support
SELECT * FROM order_items
WHERE product_id = 'prod-xyz' -- replace with actual UUID
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 100;

-- AVOID: FK to a 5-row lookup table
CREATE TABLE task_statuses (status_id INT PRIMARY KEY, label TEXT);
CREATE TABLE tasks (task_id UUID PRIMARY KEY, status_id INT REFERENCES task_statuses);

-- PREFERRED: CHECK constraint (no FK overhead, no parent-row reads)
CREATE TABLE tasks (
    task_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    status VARCHAR(20) CHECK (status IN ('open', 'in_progress', 'done', 'blocked', 'cancelled'))
);

CREATE TABLE customers (
    customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(50) UNIQUE,
    name TEXT,
    price NUMERIC,
    is_active BOOLEAN DEFAULT true -- soft-delete instead of hard-delete
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID REFERENCES customers,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_lines (
    line_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID,
    product_id UUID REFERENCES products ON DELETE RESTRICT, -- unbounded: never CASCADE
    quantity INTEGER NOT NULL,
    -- Snapshot mutable attributes at order time
    unit_price NUMERIC NOT NULL,
    product_name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_order FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE -- bounded: safe to CASCADE
        DEFERRABLE INITIALLY DEFERRED
);

-- Setup: seed the referenced parents first
INSERT INTO customers (customer_id, name, email)
VALUES ('d3eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'Alice', 'alice@example.com');
INSERT INTO products (product_id, sku, name, price)
VALUES ('b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 'WM-100', 'Wireless Mouse', 49.99),
    ('c2eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 'HUB-200', 'USB-C Hub', 129.99);

-- Now place an order with deferred FK on order_id
BEGIN;

-- Insert order lines first (fk_order to orders is deferred; product FKs are immediate
-- but the products already exist above, so they pass instantly)
INSERT INTO order_lines (order_id, product_id, quantity, unit_price, product_name)
VALUES
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'b1eebc99-9c0b-4ef8-bb6d-6bb9bd380a22', 2, 49.99, 'Wireless Mouse'),
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'c2eebc99-9c0b-4ef8-bb6d-6bb9bd380a33', 1, 129.99, 'USB-C Hub');

-- Insert the order header (satisfies the deferred fk_order at commit)
INSERT INTO orders (order_id, customer_id, status)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'd3eebc99-9c0b-4ef8-bb6d-6bb9bd380a44', 'pending');

COMMIT;
-- OK: fk_order validated at commit time; product FKs were checked immediately and passed

-- From PostgreSQL:
COPY customers TO '/export/customers.csv' WITH (FORMAT csv, HEADER true);
COPY products TO '/export/products.csv' WITH (FORMAT csv, HEADER true);
COPY orders TO '/export/orders.csv' WITH (FORMAT csv, HEADER true);
COPY order_lines TO '/export/order_lines.csv' WITH (FORMAT csv, HEADER true);

# Load parent tables first (no FK dependencies)
aurora-dsql-loader load \
    --endpoint cluster-id.dsql.us-east-1.on.aws \
    --source-uri /export/customers.csv \
    --table customers

aurora-dsql-loader load \
    --endpoint cluster-id.dsql.us-east-1.on.aws \
    --source-uri /export/products.csv \
    --table products

# Load child tables after parents are loaded
aurora-dsql-loader load \
    --endpoint cluster-id.dsql.us-east-1.on.aws \
    --source-uri /export/orders.csv \
    --table orders

aurora-dsql-loader load \
    --endpoint cluster-id.dsql.us-east-1.on.aws \
    --source-uri /export/order_lines.csv \
    --table order_lines

-- Step 1: Load all data (no FK constraints yet - maximum throughput)
-- Use aurora-dsql-loader for each table in any order

-- Step 2: Add constraints without validating existing data (instant DDL)
ALTER TABLE order_lines
ADD CONSTRAINT fk_order FOREIGN KEY (order_id)
REFERENCES orders (order_id)
NOT VALID;

-- Step 3: Validate existing data asynchronously (non-blocking)
ALTER TABLE ASYNC order_lines
VALIDATE CONSTRAINT fk_order;

-- Step 4: Monitor progress
SELECT * FROM sys.jobs WHERE job_type = 'VALIDATE_CONSTRAINT';

-- Or block the session until validation completes:
CALL sys.wait_for_job('<job_id>');

-- Drop children before parents (respecting FK dependencies)
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS shipments;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS task_statuses;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS tree;
DROP TABLE IF EXISTS customers;