13

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?

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
MaxRecursion
  • 4,773
  • 12
  • 42
  • 76

2 Answers2

29

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))
Chris Van Opstal
  • 36,423
  • 9
  • 73
  • 90
0

If you are handling via Front-end (your application), then the issue of both Credit and Debit being null won't arise. At least one entry is inserted, either in Debit or Credit.

I guess you want to put a constraint when someone is handling via back-end and directly inserting values in a table. In that case, Chris suggested a solution.

But even if you are inserting from back-end, I am not following why I would record a transaction when neither it is Debit nor Credit.

Geek
  • 429
  • 2
  • 5
  • Actually its a service and as a service dev I cant totally rely on data sent to me. So, its just for precaution. – MaxRecursion Dec 22 '12 at 18:35
  • 1
    Its good to validate your data at all levels. C.f. the case where someone is manually entering data into the db and bypassing any front-end or indeed business layer validation logic. – immutabl May 15 '13 at 10:56