19 lines
558 B
MySQL
19 lines
558 B
MySQL
|
-- 004_balance_constraints.sql
|
||
|
|
-- Enforce balance integrity constraints
|
||
|
|
-- WARNING: Apply after data cleanup if you have existing inconsistent data
|
||
|
|
|
||
|
|
ALTER TABLE bank_accounts
|
||
|
|
ADD CONSTRAINT bank_accounts_reserved_nonnegative
|
||
|
|
CHECK (reserved_balance >= 0);
|
||
|
|
|
||
|
|
ALTER TABLE bank_accounts
|
||
|
|
ADD CONSTRAINT bank_accounts_available_nonnegative
|
||
|
|
CHECK (available_balance >= 0);
|
||
|
|
|
||
|
|
ALTER TABLE bank_accounts
|
||
|
|
ADD CONSTRAINT bank_accounts_balance_consistency
|
||
|
|
CHECK (
|
||
|
|
available_balance <= balance
|
||
|
|
AND (available_balance + reserved_balance) <= balance
|
||
|
|
);
|