CODEWITHSUNDEEP
sql-serverdatabase-designforeign-keysconstraints
x13
x13

Reputation: 2237

MSSQL - Foreign key to the same column IF other column is not equal to the referring row

I have a database in which i have two tables:

CREATE TABLE Transactions (
    ID BIGINT IDENTITY(1,1) NOT NULL,
    AccountID BIGINT NOT NULL,
    Amount BIGINT NOT NULL,
    CONSTRAINT PK_Transactions PRIMARY KEY CLUSTERED (ID ASC,AccountID ASC),
    CONSTRAINT FK_Transaction_Account FOREIGN KEY (AccountID) REFERENCES Accounts(ID)
);
CREATE TABLE Accounts (
    ID BIGINT IDENTITY(1,11) NOT NULL,
    Balance BIGINT NOT NULL,
    CONSTRAINT PK_Accounts PRIMARY KEY (ID)
);

Transactions are inserted to their table by a stored procedure i wrote, so that two rows are generated when Account 1 transfers 25 "coins" to Account 21:

 ID | AccountID | Amount
-------------------------
 1  | 1         | -25
-------------------------
 1  | 21        | 25

In the above schema, i want the first row to reference the bottom row based on ID and the AccountID being unequal to the AccountID of the bottom row.
And vica versa.

What i want to do would look something like this:

CONSTRAINT FK_Transaction_Counterpart FOREIGN KEY (ID) REFERENCES Transactions(ID) WHERE thisRow.AccountID != referencedRow.AccountID

I haven't found this possibility in the documentation on the table constraints.
So both out of curiosity and intent to use this i ask, is this possible? And if yes, how?

Edit:

Answers reflect that this is not possible, and i should adjust my design or intentions.
I think i will settle with assigning the two transaction rows to each other in the functional code.

Upvotes: 0

Views: 75

Answers (2)

Philip Kelley
Philip Kelley

Reputation: 40359

If the data model you are trying to implement is:

  • One transaction (ID) has two and only two entries in table Transactions
  • For the two rows of a given Transaction ID, the AccountIDs cannot be the same

Then one perhaps overly-complex way you could enforce this business rule within the database table structures would be as follows:

Table Accounts, as you have defined

Table Transactions, as you have defined

New table TransactionPair with:

Columns (all are NOT NULL)

  • ID

  • LowAccountID

  • HighAccountID

Constraints

  • Primary key on ID (only one entry per Transaction ID)
  • Foreign key on (ID, LowAccountID) into Transactions
  • Foreign key on (ID, HighAccountID) into Transactions
  • Check constraint on the row such that LowAccountID < HighAccountID

Process:

  • Add pair of rows to Transactions table
  • Add single row to TransactionPair referencing the rows just added
  • If that row cannot be added, something failed, roll everything back

Seems neat and tidy, but quite possibly overly complex. Your mileage may vary.

Upvotes: 1

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

A traditional foreign key can't be conditional (i.e. no WHERE clause attached). In your case, I'd probably just make sure that the inserts are atomic (in the same transaction) so that there'd be no possibility of only one of them inserting.

Upvotes: 1

Related Questions