CODEWITHSUNDEEP
sqldatabaset-sql
Pecheneg
Pecheneg

Reputation: 808

Foreign Key to check two columns

I have a foreign key between two columns of the two of my tables. Problem is, I also have a "Deleted" column and I consider the record as deleted if it's set to true from my UI. So my foreign key must check that column is set to true or false as well.

Is there any way to do this? I need to create rule like: "Don't allow the second table's Deleted column to set to false, if there are any related records on the first table."

Here is a long explanation if the above is little too complicated:

**Customer**      **StatusType**
Id               Id
Name             StatusName
Surname          Deleted
StatusId
Deleted

As you can see, I have two tables, and the "StatusId" of the Customer table contains the primary Keys from the StatusType. So I assigned a foreign key to hold them together.

From my interface, I never delete any data from the database, I just set "Deleted" column to "true". And I show only the data which set as "false" on the deleted column anyway.

So here is the question: My foreign key must not allow the "deleted" column of the StatusType table to set as "false", if there are related Customer.StatusId-StatusType.Id records on Customer table.

Upvotes: 1

Views: 71

Answers (2)

Nithila Shanmugananthan
Nithila Shanmugananthan

Reputation: 336

try this 

update StatusType  set Deleted='false'
 where(select count (id) from customer where deleted='true' and StatusType.id=StatusId )=0

Upvotes: 0

trincot
trincot

Reputation: 349989

You could use a trigger for that:

create trigger trg_upd_status before update on StatusType
for each row begin
    declare customer_count int;
    if new.Deleted = 'true' then
        select count(*)
        into   customer_count
        from   Customer
        where  Deleted = 'false';
        if (customer_count > 0) then
            signal sqlstate '45001' 
            set message_text = "Not allowed to delete this record";
        end if;
    end if;
end;
/

This assumes the data type of the Deleted column is varchar, but you could adapt it easily if it is numerical or a bit...

Note that this only checks whether there are Customer records that are not deleted. So this means you also have to do the opposite: if you un-delete a Customer record, the corresponding StatusType record should not be in deleted status. Same if you update the Customer record and change the StatusId. That would be a trigger on the Customer table...

Upvotes: 1

Related Questions