Exactly one payment reference (XOR check)

Exactly one payment reference (XOR check)

Hard SQL Constraints 23 views
Explanation Complexity

Problem Statement

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.

Input Format

SQL DDL

Output Format

DDL statement(s)

Example

Payments(payment_id, order_id, card_txn_id, upi_txn_id)
Constraints added

Constraints

Assume table exists

Input / Output Format

Input Format
SQL DDL
Output Format
DDL statement(s)
Constraints
Assume table exists

Examples

Input:
Payments(payment_id, order_id, card_txn_id, upi_txn_id)
Output:
Constraints added

Example Solution (Public)

SQL 
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);

Official Solution Code

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);
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.