Exactly one payment reference (XOR check)
SQL
Hard
8 views
Problem Description
In Payments(payment_id, order_id, card_txn_id, upi_txn_id), a payment can be done either by card or by UPI. Enforce rule: exactly one of card_txn_id and upi_txn_id must be filled (not both, not none). Also keep each txn id unique.
Output Format
DDL statement(s)
Sample Test Case
Input:
Payments(payment_id, order_id, card_txn_id, upi_txn_id)
Output:
Constraints added
Constraints
Assume table exists
Official Solution
ALTER TABLE Payments ADD CONSTRAINT chk_payment_ref CHECK ((card_txn_id IS NOT NULL AND upi_txn_id IS NULL) OR (card_txn_id IS NULL AND upi_txn_id IS NOT NULL)); ALTER TABLE Payments ADD CONSTRAINT uq_card_txn UNIQUE (card_txn_id); ALTER TABLE Payments ADD CONSTRAINT uq_upi_txn UNIQUE (upi_txn_id);
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!