双向关系（数据库设计缺陷）
我有两个表通过一对多关系相互连接。 问题是引用的表也有第二个表的外键！
我知道这很令人困惑，所以我将向您展示我的数据库的简单设计：
Authors
AuthorId Name DefaultBookId (FK)
-------- ------- -------------
1 John 1
2 Mike 3
3 Mig 5
Books
BookId Title AuthorId (FK)
-------- ------- -------------
1 TitleInfo1 1
2 TitleInfo2 3
3 TitleInfo3 2
4 TitleInfo4 1
5 TitleInfo5 3
6 TitleInfo6 3
7 TitleInfo7 1
当然，我必须将 AuthorId books 表有一个外键，这是正常情况，但现在我需要为每个作者设置一本默认书籍，因此我将在新列 (DefaultBookId) 上定义一个外键来指定默认值为每位作家写的书。
现在，两个表都相互依赖，因此我无法删除任何项目，除非删除它们之间的关系，但感觉这样做不对！
关于这个设计是否有缺陷以及我能做什么？
I've two tables which are connected to each other through a one to many relation.
the problem is that the referenced table also has a foreign key of the second table!
I know it's confusing, So I'll show you a simple design of my database:
Authors
AuthorId Name DefaultBookId (FK)
-------- ------- -------------
1 John 1
2 Mike 3
3 Mig 5
Books
BookId Title AuthorId (FK)
-------- ------- -------------
1 TitleInfo1 1
2 TitleInfo2 3
3 TitleInfo3 2
4 TitleInfo4 1
5 TitleInfo5 3
6 TitleInfo6 3
7 TitleInfo7 1
Of course I have to make the AuthorId in the books table a foreign key and that's the normal case, but now I need to set a default book for every author so I'll define a foreign key on a new column (DefaultBookId) to specify the default book for each writer.
Now, both tables are dependent on each other so I can't delete any item unless I remove the relation between them and it doesn't feel the right thing to do!
Any ideas on whether this design flawed or not and what can I do about it ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我将使用连接另外两个表的第三个表来实现此目的。您最终会得到：
Authors
DefaultBook
Books
通过在
DefaultBook.AuthorId上放置 UNIQUE 约束，您可以防止每个作者都有多个默认值。如果您需要删除某个作者，只需删除他的默认值以及与其关联的所有书籍，即可删除该作者。这样做的一个问题是，很难强制每个作者都有一本默认的书，但这一要求首先导致了这个问题。
I would implement this with a 3rd table that joins the other two. You'd end up with:
Authors
DefaultBook
Books
By putting a UNIQUE constraing on
DefaultBook.AuthorIdyou can prevent each author from having more than one default. If you need to delete an author, simply delete his default and any books associated with him, then you can delete the author.The one problem this has is that it's hard to enforce that each author has a default book, but that requirement is what led you to this problem in the first place.
您可以从作者中删除
DefaultBookId，向图书中添加IsDefaultForAuthor列，然后应用约束，使每个作者只有一本标记为IsDefaultForAuthor的图书>。当然，这并不能解决诸如由多个作者撰写的书籍之类的问题，但我假设这是您呈现的一个玩具示例，而不是对您的实际系统进行建模。
You could remove
DefaultBookIdfrom Authors, add anIsDefaultForAuthorcolumn to Books, and then apply a constraint such that each author only has one book marked asIsDefaultForAuthor.Of course, this doesn't address issues such as books which are authored by multiple Authors, but I'm assuming that this is a toy example you're presenting, rather than your actual system being modelled.
原则上这个设计是可以的，但是当然你在实践中已经发现了问题。
您可以尝试以下一些操作：
如果您的数据库支持（Oracle 确实支持），则可以推迟您的约束。这只会检查提交的约束，因此您可以在一个事务中删除一本书及其作者。 （缺点：非常特定于数据库）
使用 books.isAuthorsDefaultBook 代替authors.defaultBookId。 （缺点：很难为每个作者强制执行一本默认书籍；应该可以使用 FunctionBased Indexes 索引和/或物化视图，这些索引和/或物化视图可能依赖于数据库）
删除 defaultBookId 上的非空约束并相信您的应用程序会正确执行此操作（缺点：失去数据库的一些权力）
澄清：
删除 not null defaultBookId 的约束允许将其设置为 null，删除以前引用的书籍，然后删除作者。对于创造来说，同样的工作，但顺序相反。
In principle this design is ok, but of course you have already found a problem in praxis.
Here are some things you can try:
if your database supports it (oracle does) make your constraints deferred. This will only check the constraints on commit, so you can delete a book and its author in one transaction. (con: very database specific)
instead of the authors.defaultBookId use a books.isAuthorsDefaultBook. (con: hard to enforce exactly one default book per Author; should be possible with FunctionBased Indexes indexes and/or materialized views, which probably are db dependend)
drop the not null constraint on defaultBookId and trust your application to do it right (con: loosing some of the powers of a database)
Clarification:
Dropping the the not null constraint on defaultBookId allows to set it to null, remove the formerly referenced book, then remove the author. For creation the same works, but in opposite order.