34 lines
1.5 KiB
SQL
34 lines
1.5 KiB
SQL
-- A purchase is charged either to a team (a parent product category) or to Pusat.
|
|
-- Pusat has no category of its own, so it is stored as a scope rather than a row;
|
|
-- which outlet's Pusat it is comes from purchase_orders.outlet_id.
|
|
-- team_scope IS NULL means the team was never chosen, which is deliberately
|
|
-- distinct from a purchase that belongs to Pusat.
|
|
ALTER TABLE purchase_orders
|
|
ADD COLUMN IF NOT EXISTS team_scope VARCHAR(20),
|
|
ADD COLUMN IF NOT EXISTS team_category_id UUID;
|
|
|
|
ALTER TABLE purchase_orders
|
|
ADD CONSTRAINT fk_purchase_orders_team_category
|
|
FOREIGN KEY (team_category_id) REFERENCES categories(id) ON DELETE RESTRICT;
|
|
|
|
-- Deleting a category that is still charged on a purchase order must fail rather
|
|
-- than silently drop the attribution, hence RESTRICT above and this pairing check.
|
|
-- Written as a CASE because an OR chain would evaluate to NULL when team_scope is
|
|
-- NULL, and a CHECK only rejects FALSE — a stray team_category_id would slip past.
|
|
ALTER TABLE purchase_orders
|
|
ADD CONSTRAINT chk_purchase_orders_team
|
|
CHECK (
|
|
CASE
|
|
WHEN team_scope IS NULL THEN team_category_id IS NULL
|
|
WHEN team_scope = 'category' THEN team_category_id IS NOT NULL
|
|
WHEN team_scope = 'central' THEN team_category_id IS NULL
|
|
ELSE false
|
|
END
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_team_category_id
|
|
ON purchase_orders(team_category_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_purchase_orders_team_scope
|
|
ON purchase_orders(team_scope);
|