2

By having into consideration the following scheme:

table relations

We need to make sure that, if an association gets deleted, all the dogs that belong to that association, should also be deleted.

However, it makes sense to, while doing this, keep the relation that actually exists between Association and Dog tables, because, each association can have several Dogs, however, one Dog belong to only one Association. So I believe the foreign key configuration is correct.

I believe I should apply Cascade somewhere, but I'm not seeing where. :(

Please advice

MEM
  • 30,529
  • 42
  • 121
  • 191

2 Answers2

5

Selecting 'Dog' as your target table, then selecting the 'Foreign Keys' tab, you should have a foreign key there for the 'association_id' field selected. Once you find that just check the popup value showing in the 'Foreign Key Options' section for 'On Delete' it should show 'CASCADE' (similar to what your screenshot above is showing - if it isn't showing 'CASCADE' simply select it).

Phil Street
  • 2,735
  • 2
  • 20
  • 25
  • Oh... 1) I though that, if I do that, when I deleted a dog the association will also be deleted! But that's not the case then, hm ? 2) By looking to this screen shot, I intended to, IF an Association gets deleted, the user should also be deleted. Have I accomplish that with the above ? – MEM May 01 '11 at 15:18
  • 1
    No the association will remain, as you have a one to many relationship between an 'association' and 'dog'. The foreign key you're providing on dog relates to an association, so when an 'association' is deleted, the cascaded delete will occur on the 'dog' table where any row contains the 'association_id' matching the id of the deleted 'association'. I hope that helps clear it up. Deleting a 'dog' row won't cause an 'association' to be deleted. – Phil Street May 01 '11 at 15:20
2

To add a relation between Dog and association: On Dog properties add a new foreign key for association_id column that refers association.id. Choose On Delete CASCADE.

You can also do these steps in query window as well (personally, I use graphical tools only if I need to print database structure). 

ALTER TABLE Dog ADD CONSTRAINT `FK_byAssociationIdDog` 
 FOREIGN KEY(association_id)  
REFERENCES Association(id) ON UPDATE CASCADE ON DELETE CASCADE;
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Indeed, I need to update the record on association if the user gets updated. (I thought that, since they are related by their keys, and that those keys never get changed, that I didn't need that?) but I was wrong? – MEM May 01 '11 at 15:24
  • 2
    I don't think that a UNIQUE index is necessary on dog.association_id as a one to one relationship wasn't required, as @MEM pointed out an association is able to have multiple dogs, and a dog is defined as being unique I imagine based on the id primary key. If you set the UNIQUE index, then only one dog could belong to one association which isn't desired behaviour from what I understand of the requirements. – Phil Street May 01 '11 at 15:27
  • When you say that I need a Unique index, are you refering to a identity relation somehow ? Because we cannot have a dog without an association ? Thanks again. – MEM May 01 '11 at 15:27
  • If you wanted to ensure that a dog **had** to have an association, you could just ensure the 'association_id' field was also marked as being NOT NULL I believe. – Phil Street May 01 '11 at 15:30
  • @Phil Street - Yes I believe you are right. I recall to, sometimes get a message telling me that I already have a primary key with autoincrement, and that, since I had that, I didn't need another unique key. (i believe it as some phpmyadmin error or warning)... can't recall, and I can be wrong. – MEM May 01 '11 at 15:31
  • 2
    Yeah guys, you are right, I misunderstood requirements. You don't need a unique constraint. – a1ex07 May 01 '11 at 15:33
  • Thanks for clarifying a1lex07. So, let's keep on going with this huge database. ;) – MEM May 01 '11 at 15:34
  • +1 for the corrected answer, and I agree with using the query window I tend to avoid graphical tools as well when creating my schema. (Personal preference I guess) – Phil Street May 01 '11 at 15:39
  • I use gui, but I always look at the code generated, that we can also edit btw. :) Oh, btw, should the user on the screenshot provided have an ON UPDATE CASCADE ? – MEM May 01 '11 at 15:42
  • 1
    Possibly yes, but it does depend on what you want to occur when/if a user id is changed. If you don't put in the ON UPDATE CASCADE then when the user id is changed, all association rows that have a user_id matching the changed id will simply remain as they are (probably not a good thing if you want to maintain referential integrity). I recommend looking at this stackoverflow question [here](http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade) – Phil Street May 01 '11 at 15:52
  • 1
    That depends on your needs. If you want user_id in Association to change it's value automatically when you change User.id, then change `CASCADE` to On update. If you want to restrict changes to User.id column if it has at least one related row in Association table, then leave it as it is. – a1ex07 May 01 '11 at 15:52
  • 1
    @MEM: You can also specify `RESTRICT` which means the same as `NO ACTION` in mysql, but sounds a bit more self-explanatory (it's just my opinion, there is no difference) – a1ex07 May 01 '11 at 15:59