title: Foreign keys and constraints on pivot tables, on delete cascade
body: <p>I have designed my database structure, like below to support the creation of multiple shops and users assigned to those shops. My tables use InnoDB storage engine.  </p>
  
  <pre><code>[user]  
  user_id 
  
  [user_profile]  
  user_profile_id  
  user_id
  
  [user_to_shop] PIVOT 
  user_id  
  shop_id
  
  [shop]  
  shop_id
  
  [shop_profile]  
  shop_profile_id  
  shop_id  
  
  [category_to_shop] PIVOT  
  category_id  
  shop_id
  
  [category]  
  category_id
  
  [category_description]  
  category_description_id  
  category_id 
  
  [product_to_category] PIVOT  
  product_id
  category_id
  
  [product_to_shop] PIVOT
  product_id
  shop_id
  
  [product]
  product_id
  
  ...  
  ...
  </code></pre>
  
  <p>What I want to achieve is to delete all related records(category records, shop records, ..., ...) when a user(user_id) is deleted.  </p>
  
  <p>I have created constraints per entity. So when I remove a user_id(1), engine takes care of removing user_profile(user_id(1)) also. </p>
  
  <pre><code>ALTER TABLE `user_profile`
  ADD CONSTRAINT `user_profile_cs1`
  FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`)
  ON DELETE CASCADE
  </code></pre>
  
  <p>But what should I do with pivot tables? How should I declare constraints or foreign keys on those tables to get the job done and be safe?</p>