How to create mutually exclusive table columns

Question

I have a table Transactions where I am saving two records for one transaction, one for debiting and other crediting.

So I have two columns in table creditAmount(Money) and debitAmount(Money).

I want a table level constraint that either of the column is not null in each row. i.e. If row #3 creditAmount is null then debitAmount must hold some value and Vice Versa.

How to ensure the same when inserting a record in a row?

Answer 1

You can add a CHECK constraint to the table:

ALTER TABLE Transactions ADD CONSTRAINT CK_Transactions_DebitOrCreditExists
CHECK ((creditAmount IS NULL AND debitAmount IS NOT NULL) 
   OR (creditAmount IS NOT NULL AND debitAmount IS NULL))