From 830e7f51b597cc29750ac017beda663f6b31bfbe Mon Sep 17 00:00:00 2001 From: Masaya Tojo Date: Sun, 5 Mar 2023 14:00:25 +0900 Subject: Add foreign key constraint to transactions referencing types. --- deploy/transactions_type_code_fkey.sql | 12 ++++++++++++ revert/transactions_type_code_fkey.sql | 8 ++++++++ sqitch.plan | 1 + verify/transactions_type_code_fkey.sql | 16 ++++++++++++++++ 4 files changed, 37 insertions(+) create mode 100644 deploy/transactions_type_code_fkey.sql create mode 100644 revert/transactions_type_code_fkey.sql create mode 100644 verify/transactions_type_code_fkey.sql diff --git a/deploy/transactions_type_code_fkey.sql b/deploy/transactions_type_code_fkey.sql new file mode 100644 index 0000000..ba4406b --- /dev/null +++ b/deploy/transactions_type_code_fkey.sql @@ -0,0 +1,12 @@ +-- Deploy kakeibo:transactions_type_code_fkey to pg +-- requires: transactions +-- requires: types + +BEGIN; + +ALTER TABLE kakeibo.transactions + ADD CONSTRAINT transactions_type_code_fkey + FOREIGN KEY (type_code) + REFERENCES kakeibo.types (code); + +COMMIT; diff --git a/revert/transactions_type_code_fkey.sql b/revert/transactions_type_code_fkey.sql new file mode 100644 index 0000000..7637e8e --- /dev/null +++ b/revert/transactions_type_code_fkey.sql @@ -0,0 +1,8 @@ +-- Revert kakeibo:transactions_type_code_fkey from pg + +BEGIN; + +ALTER TABLE kakeibo.transactions + DROP CONSTRAINT transactions_type_code_fkey; + +COMMIT; diff --git a/sqitch.plan b/sqitch.plan index 6780aca..804b9fd 100644 --- a/sqitch.plan +++ b/sqitch.plan @@ -22,3 +22,4 @@ transaction_type [transaction_type@v1.0.0-dev1 appschema] 2023-03-04T11:13:26Z M types [appschema] 2023-03-05T03:45:31Z Masaya Tojo # Add types table for manage type column of transactions. transactions_type_code [transactions] 2023-03-05T03:53:38Z Masaya Tojo # Rename column of transactions from type to type_code. +transactions_type_code_fkey [transactions types] 2023-03-05T04:00:45Z Masaya Tojo # Add foreign key constraint to transactions table referencing types table. diff --git a/verify/transactions_type_code_fkey.sql b/verify/transactions_type_code_fkey.sql new file mode 100644 index 0000000..bb2841c --- /dev/null +++ b/verify/transactions_type_code_fkey.sql @@ -0,0 +1,16 @@ +-- Verify kakeibo:transactions_type_code_fkey on pg + +BEGIN; + +DO $$ + BEGIN + ASSERT (SELECT 1 + FROM information_schema.table_constraints + WHERE table_schema = 'kakeibo' + AND table_name = 'transactions' + AND constraint_type = 'FOREIGN KEY' + AND constraint_name = 'transactions_type_code_fkey'); + END +$$; + +ROLLBACK; -- cgit v1.2.3