当外键指向别的东西时，移动其他数据 [英] when foreign key points to something else, move other data as well

问题描述

假设您有以下模型。

 书有文章（有外键的书）
 
文章有图像（文章有多达#max的图像数量）
 
图像也有一个外键的书。 
  

（编辑某些图片包含在书籍中， ）

假设您将文章从book1移动到book2。

您想要文章中的图片

解决方案

数据模型不必要复杂。你应该避免这样的解决方案。但是如果你必须这样做，你可以在文章上使用触发器，例如

  create function trigger_before_update_on_articles（）
 ... 
如果new.book_id<> old.book_id then 
 update images set book_id = new.book_id 
其中article_id = new.article_id 
 ... 
  

为了规范化模型，您可以使用 dummy 只包含图书的文章。

Suppose you have the following models.

Book has Articles (Article has foreign key to Book)

Article has Images (Article has upto #max number of images)

Image also has a foreign key to Book. 

(edit Certain images are included in the book but not owned by article)

Say you moved an article from book1 to book2.

You'd like to images in the article to point to book2 as well.

What's the preferred way of handling this?

解决方案

The data model is unnecessary complicated. You should avoid such solutions. But if you have to live with this, you can use trigger before update on articles, e.g.

create function trigger_before_update_on_articles()
...
    if new.book_id <> old.book_id then
        update images set book_id = new.book_id
        where article_id = new.article_id
...

To normalize the model you can use dummy articles containing only images for a book.

这篇关于当外键指向别的东西时，移动其他数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！