CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012mutual-exclusion
MaxRecursion
MaxRecursion

Reputation: 4903

How to create mutually exclusive table columns

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?

Upvotes: 15

Views: 7528

Answers (1)

Chris Van Opstal
Chris Van Opstal

Reputation: 37567

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

Upvotes: 32

Related Questions