Check child records of a record before making status of it as false in SQL Server

Question

I have a application with some relational tables in my SQL Server database.

In the application, whenever user deletes and records I never delete it from my database, instead I make 'status' (table column) as 'False'.

Eg: User will delete a record from tblAccounts, the records has a column 'Status' which can be either true/false. On delete action record is set to false.

Now the problem is this account may be referred in other transactions. If it is used in other tables then it should not allow user to delete (make it false).

If I allow user to delete the record physically from table, it will throw foreign key error but in this scenario (making it false) how can I check the child rows without deleting and prompt the User.

I can do it by a select query on each table but that will be slow down my application.

Is there any other way/idea to achieve it?

I'm not completely clear on what you want. More importantly are you having problems with this setup and if so exactly what are they, or are you just speculating about potential issues. — juharr, Aug 17 '18 at 14:22
This answer explains a check constraint calling a UDF which may be a possible solution. --> https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint — Ross Bush, Aug 17 '18 at 14:24
I would suggest using a lookup table for status instead of True/False strings. And honestly true and false for a status kind of makes no sense. Maybe IsDeleted would be a better name and then use the bit datatype. But I digress. Are you asking how to do this in sql server or your application? Sadly the question is not very clear. We probably also need to see some table definitions and sample data. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Aug 17 '18 at 14:25
If you want to prevent soft deletes in the same manner as hard deletes, you can extend the foreign key to refer to `ID, Status` rather than just `ID` (this requires a unique index on these two columns). Because the foreign keys will always have `account_Status` set to `1` (or `True`, I assume this is a `BIT` and like Sean said, should really be named `Deleted` and its meaning inverted) you can make this a persisted computed column with a constant `0` value. In this manner, updates are blocked in the same way as deletes would be, at the cost of some storage space for redundant data. — Jeroen Mostert, Aug 17 '18 at 14:42

score 0 · Answer 1 · answered Aug 17 '18 at 14:39

I can do it by a select query on each table but that will be slow down my application.

Is there any other way/idea to achieve it?

No there is not. You can do the automation with either a CHECK constraint that calls a function or with a TRIGGER, but in that code a SELECT statement against the other tables will have to be performed. There is no way around it.

score 0 · Answer 2 · answered Aug 17 '18 at 15:16

You could do it using a foreign key, the trick here is to make both columns (AccountID and Status) Primary in tblAccounts. Then in the transaction table, you create a foreign key to both (AccountID and Status) with cascade on UPDATE/Delete. This means, if you ever change/delete an account id or its status from tblAccounts, the changes will be applied on all foreign keys as well.

Here is an example :

CREATE TABLE tblAccounts(
    ID INT,
    AccountID INT NOT NULL,
    [Status] BIT NOT NULL
)
ALTER TABLE tblAccounts
ADD PRIMARY KEY (AccountID, [Status])


CREATE TABLE tblTransactions(
       [ID] INT,
      [TransID]  INT NOT NULL PRIMARY KEY,
      [AcctID]   INT NOT NULL,
      [Status]   BIT NOT NULL
)
ALTER TABLE tblTransactions
ADD FOREIGN KEY (AcctID,[Status]) REFERENCES tblAccounts(AccountID, [Status])
ON UPDATE CASCADE
ON DELETE CASCADE 


INSERT INTO tblAccounts (ID, AccountID, [Status])
VALUES 
(1,1000,1), 
(2,1100,1),
(3,1200,1),
(4,1300,1)

INSERT INTO tblTransactions(ID, TransID, AcctID,[Status])
VALUES 
(1,5000,1000,1),
(2,3258,1300,1),
(3,5852,1000,1),
(4,9631,1100,1),
(5,1870,1200,1)

tblAccounts

| ID | AccountID | Status |
|----|-----------|--------|
|  1 |      1000 |   true |
|  2 |      1100 |   true |
|  3 |      1200 |   true |
|  4 |      1300 |   true |

tblTransactions

| ID | TransID | AcctID | Status |
|----|---------|--------|--------|
|  1 |    5000 |   1000 |   true |
|  2 |    3258 |   1300 |   true |
|  3 |    5852 |   1000 |   true |
|  4 |    9631 |   1100 |   true |
|  5 |    1870 |   1200 |   true |

Let's change the status of AccountID 1100 to false

UPDATE tblAccounts
SET 
    [Status] = 0
WHERE 
    AccountID = 1100

Check tblAccount

| ID | AccountID | Status |
|----|-----------|--------|
|  1 |      1000 |   true |
|  2 |      1100 |   false|
|  3 |      1200 |   true |
|  4 |      1300 |   true |

Check tblTransactions

| ID | TransID | AcctID | Status |
|----|---------|--------|--------|
|  1 |    5000 |   1000 |   true |
|  2 |    3258 |   1300 |   true |
|  3 |    5852 |   1000 |   true |
|  4 |    9631 |   1100 |   false|
|  5 |    1870 |   1200 |   true |

I appreciate your effort and answer but update on tblaccounts should not proceed. The other options of foreign key will do the trick? — user10239717, Aug 17 '18 at 16:31
@user10239717 a foreign key is related to its parent key (primary key). any changes on the parent, will affect its children as well. So, it won't work without doing the changes on the primary key (which is status on tblaccount). You could do the same method but using different column (maybe a new one ? ) — iSR5, Aug 17 '18 at 16:44

Check child records of a record before making status of it as false in SQL Server

2 Answers2