How do you check constraints from another table when entering a row into a table?

Question

Say I have two tables Account and Withdraw. Account has an attribute Balance. Every time a value is entered into the table Withdraw I would like to check if there is sufficient balance to do so. If yes, I'd like to subtract that amount from the Balance.

This isn't exactly what I want to do, but a simplified version of my requirement.

PS: Is "check constraints" the correct phrase? I'm not really sure. Thanks!

what have you tried so far? We're here to help you learn yourself, not write the code for you! Please and thanks :) — jbutler483, Sep 16 '14 at 12:51
@jbutler483 So far all my searches have come with results that help me check if the withdrawal is less than a certain fixed value. — rohithpr, Sep 16 '14 at 12:55
So, realistically, you're going to (possibly) have to read the users balance from the db, check if it's enough, then subtract if accepted and *then* write it to your db. That sound ok? that way you can also notify the user if it was accepted/declined quickly enough — jbutler483, Sep 16 '14 at 12:57
Yes, but how do I perform checks? I really wish I could use if's here! :( — rohithpr, Sep 16 '14 at 13:00
Are you just writing in sql? or are you using winforms/c# as well? — jbutler483, Sep 16 '14 at 13:01
I've just started learning SQL. So I'm not using it with anything else for now. I will be using it with Python later. — rohithpr, Sep 16 '14 at 13:05
have a look at this: SQL allows *if* logic: http://msdn.microsoft.com/en-gb/library/ms182717.aspx . The next link will show you how to tell if it is greater than another element in the db: http://stackoverflow.com/questions/16211774/sql-constraint-that-one-column-value-cannot-be-greater-than-another-in-a-differe — jbutler483, Sep 16 '14 at 13:11

Manwal · Answer 1 · 2014-09-17T03:52:08.277

0

This is short example:

INSERT INTO mytable 
            (id, name) 
SELECT 1, 'test' 
WHERE  NOT EXISTS(SELECT id 
                  FROM   myanother_table 
                  WHERE  id = 1)

Note: Its hard to assume anything without understand or taking a look of OP's code. So i have just provided example.

edited Sep 17 '14 at 03:52

answered Sep 16 '14 at 12:54

Manwal

23,450
12
63
93

not sure this is excatly what the OP is looking for? – jbutler483 Sep 16 '14 at 13:00

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

You can create a function where you verify if the balance is > than your @value.
And then you add that function to the constraint.

Try to look at this:

Can a Check constraint relate to another table?

edited May 23 '17 at 12:21

Community

1
1

answered Sep 16 '14 at 12:59

SanRyu

210
1
2
13

score 0 · Answer 3 · answered Sep 16 '14 at 13:24

What you're wanting not a constraint. You're looking for a trigger. Take a look at this example.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Account]')
                    AND type IN ( N'U' ) )
    DROP TABLE [dbo].[Account]
GO

CREATE TABLE dbo.Account
    (
      AccountID INT NOT NULL ,
      AccountBalance DECIMAL(19, 2) NOT NULL
    )
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Transaction]')
                    AND type IN ( N'U' ) )
    DROP TABLE [dbo].[Transaction]
GO

CREATE TABLE dbo.[Transaction]
    (
      TransactionID INT NOT NULL
                        IDENTITY(1, 1) ,
      AccountID INT NOT NULL ,
      TransactionAmount DECIMAL(19, 2) NOT NULL
    )
GO

CREATE TRIGGER dbo.TRI_Transaction ON dbo.[Transaction]
    AFTER INSERT
AS
    UPDATE  a
    SET     a.AccountBalance = a.AccountBalance + i.TransactionAmount
    FROM    Account a
            JOIN INSERTED i ON ( a.AccountID = i.AccountID )
GO

INSERT  INTO dbo.Account
        ( AccountID, AccountBalance )
VALUES  ( 1234, 0 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, 10 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )


INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, 20 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, -15 )


SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

How do you check constraints from another table when entering a row into a table?

3 Answers3

Linked