How to create mutually exclusive table columns

Question

I have a Transactions table where I save two records for one transaction, one for debiting and another for lending.

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

I want a table-level restriction so that none of the columns are null in each row. those. if line # 3 creditAmount is zero, then debitAmount should contain some value and vice versa.

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

Answer 1

You can add 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)) 

Answer 2

If you apply through the Front-end (your application), then the problem with credit and debit is not equal to zero. At least one record is inserted either in debit or credit.

I assume that you want to set a limit when someone processes the server server and directly inserts the values ​​into the table. In this case, Chris proposed a solution.

But even if you paste from the back-end, I don’t understand why I should record the transaction when it is not debit or credit.