CODEWITHSUNDEEP
mysqldatabaseforeign-keys
polmarex
polmarex

Reputation: 1383

Hard task with foreign keys

I have two related tables, quite common case: Clients(CID, name, surname) and Visits(VID, VCID, dateOfVisit) - VCID is the Client ID. How to manipulate foreign keys, when I want database to delete Visits records relative to some Client, who I DELETE, and to delete Client, when I DELETE the last Visit of that client left?

Upvotes: 0

Views: 53

Answers (2)

andrew cooke
andrew cooke

Reputation: 46872

you can automatically delete visits for deleted clients by using "on delete cascade". something like:

create table clients (id integer primary key);                                  
create table visits (id integer primary key,
                     client integer,
                     foreign key (client) references clients(id)
                       on delete cascade);

but going the other way (automatically deleting clients with no visits) is more difficult.

you can manually delete clients with no visits by executing:

delete from clients where id in
  (select cid from
     (select clients.id as cid, visits.id as vid
        from clients left join visits on (clients.id = visits.client))
     where vid is null);

(and maybe there's something simpler?). so either run that every now and then, or create a trigger that runs it when something is deleted from visits (although if you're going to add a trigger it could use the deletion info to do something smarter).

or maybe someone with more time/energy than me can write an answer with the trigger...?

(as others have said, automatically deleting clients is pretty drastic behaviour - it's not something you'd normally want to do in a production system - apart from anything else, if clients need more visits they are going to be pretty annoyed if they have to enter their details again...)

Upvotes: 1

Nix
Nix

Reputation: 58522

I would suggest you do a soft delete, so you can keep your Visits record.

Soft delete means you just add an extra field is_active default it to true, and when you delete the record flip it to false.

Upvotes: 1

Related Questions