diff options
| author | Masaya Tojo <masaya@tojo.tokyo> | 2023-03-05 14:00:25 +0900 |
|---|---|---|
| committer | Masaya Tojo <masaya@tojo.tokyo> | 2023-03-05 14:00:25 +0900 |
| commit | 830e7f51b597cc29750ac017beda663f6b31bfbe (patch) | |
| tree | f74ec81d384db9c0d10ed306625d965f420cdb0a | |
| parent | 1c6642ef3ea7c6e4f4e1a90b07e1b60be6a7429b (diff) | |
Add foreign key constraint to transactions referencing types.
| -rw-r--r-- | deploy/transactions_type_code_fkey.sql | 12 | ||||
| -rw-r--r-- | revert/transactions_type_code_fkey.sql | 8 | ||||
| -rw-r--r-- | sqitch.plan | 1 | ||||
| -rw-r--r-- | verify/transactions_type_code_fkey.sql | 16 |
4 files changed, 37 insertions, 0 deletions
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 <masaya@tojo.tokyo> # Add types table for manage type column of transactions. transactions_type_code [transactions] 2023-03-05T03:53:38Z Masaya Tojo <masaya@tojo.tokyo> # Rename column of transactions from type to type_code. +transactions_type_code_fkey [transactions types] 2023-03-05T04:00:45Z Masaya Tojo <masaya@tojo.tokyo> # 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; |