0

I'm creating 2 tables, one called users and the other called images, each user can have one avatar which refers to an image in image table, yet each image has an author to identify who uploaded/created the image, and this author references to a user in user table, and I added foreign key constraints for both, so these 2 tables become mutually dependent, and that makes the insert/delete operation a mess... I wonder if anyone could come up with a better design pattern maybe, like creating another table or anything so I can avoid this mutual dependent situation? Thanks.

Ken Y-N
  • 14,644
  • 21
  • 71
  • 114
dulan
  • 1,584
  • 6
  • 22
  • 50

1 Answers1

0

As soon as mysql does not provide a way to perform deferred constraint checks the only way to do it for you is to disable constraint checks at all with

SET FOREIGN_KEY_CHECKS = 0;

and then enable it back as soon as you are done

SET FOREIGN_KEY_CHECKS = 1;

But you need to be very careful and not break database integrity while you're in a "sparta"-mode.

PS: this option is session-specific, so the other connections will still respect constraints as usually.

PPS: as soon as you're going to put your DB into a broken state - I highly recommend to make all the changes in a single transaction, so that other sessions could not observe the inconsistent state.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • I'm trying to figure out a design to avoid being mutually dependant, this feels like some hack... Besides, I can't and won't temporarily disable foreign key checks everytime user updates an avatar and enable it back after the avatar updated... – dulan Oct 08 '14 at 03:49
  • @dulan: I don't see anything bad in having 2 way dependencies. It does not violate any Normal Forms or other "best practices" I'm aware of. – zerkms Oct 08 '14 at 03:54
  • Yeah you're right, I just thought one way dependency is simpler for design and has less code and would like to keep it that way. – dulan Oct 08 '14 at 04:04