这是错误的：一列可以引用不同表中的主键取决于另一列的值吗？
我的一位同事设计了一个表模式，在其中一个表中，一列可以引用不同表中的主键，具体取决于另一列的值。我知道这是不同的错误，但找不到理论来支持我。他的模式是这样的：
table video: (id, name, ...)
table audio:(id, name, ...)
table review_item( item_type, item_id, reason, ...)
当 item_type='V' 时，则 item_id 是表视频的 id 而当 item_type='A' 时，则 item_id 是表 video 中的 id
(item_type, item_id) 的组合是唯一的，但实际上它是根本不是外键，您不能将其定义为外键，因为它不指向单个表。 DDL 语法不允许。
有人能找出这里违反了哪些原则或规则吗？
One of my colleague designed a table schema, and in one of the tables a column can references primary keys in different tables depends on value of another column. I know it is differently wrong, but can not find theory to support me. His schema like this:
table video: (id, name, ...)
table audio:(id, name, ...)
table review_item( item_type, item_id, reason, ...)
when item_type='V', then item_id is id of table video
and when item_type='A' then item_id is id in table video
The combination of (item_type, item_id) is unique, but actually it is not a foreign key at all, you can not define it as foreign key as it does not point to a single table. DDL syntax does not allow it.
Can someone find out which principle or rules are violated here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
它会起作用，但它指出了其他可能的错误。我可以想到更糟糕的设计。加强诚信将是艰难的。
您当然可以加入，但需要在“where”中添加一个额外条件来按 item_type 进行约束。
这种类型的排列可能表明音频和视频表应该是一个表，其他列区分音频/视频，以及指向纯音频或纯视频信息的 1-1 链接（如果您得到）陷入那种果酱中。但在现实世界中，似乎很多相同的信息都适用于两者。事实上，除非它是无声电影，否则视频表不必拥有所有音频信息:-) ???如果只有一项适用，则填入空值。
如果您可以使用 oo-db，这会很容易，对吗？杰/克...
It'll work but it points to other possible mistakes. I could think of worse designs. Enforcing integrity would be rought.
You could join of course but need an extra condition in the 'where' to constrain by item_type.
This type of arrangment probably indicates that the audio and video tables ought to be one table with other columns distinguishing the audio/videoness, and 1-1 links to audio-only or video-only info if you get into that kind of jam. But it seems like a lot of the same info - in the real world - applies to both. In fact would not a video table have to have all the audio info anyway unless it were a silent movie :-) ??? If only one applies, put in nulls.
This would be easy if you could use an oo-db, right? j/k...
显然，这是错误的。您可以声称它违反了“单一责任原则”；即一件事情应该有一个具体的、明确的目的。但要通过例子清楚地说明这有什么不好（不要只陈述术语/短语并期望它足够好）。
我遇到的主要问题是，我认为数据库无法在此基础上强制执行外键约束（也许可以通过某些自定义规则）。即，在 MS SQL 中，您无法引用其他表主键并让它强制执行它，并且考虑到完整性（即约束的强制执行）是 FK 的主要优点之一，这将是我的主要论点。根据您的 OR/Mapper 的操作方式（如果您使用的话），我也会提出这一点。这可能不支持他所采取的策略。
Clearly, it's wrong. You may claim it violates the 'Single Responsibility Principal'; i.e. a thing should have a specific, clear purpose. But be clear, with examples, as to how this is bad (don't just state the term/phrase and expect it to be good enough).
The main problem I'd have with it, is that I don't think a DB would be able to enforce the foreign key constraint on such a basis (maybe it can via some custom rule). I.e. in MS SQL you couldn't reference the other tables primary key and have it enforce it, and given that integrity - i.e. the enforcement of constraints - is one of the main benefits of FKs, this would be my main argument. Depending on how your OR/Mapper operates (if you use one) I'd bring this up as well. It may not support the strategy he has taken.
请参阅以下示例，了解如何在这种情况下强制执行外键：
http://consultingblogs。 emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx
item_type 列违反了第二范式，但我认为这没关系，因为：A）您可以轻松实现检查约束意味着不会出现异常，B）为了强制执行非常有用的约束（即一个项目不能属于多种类型），只需付出很小的代价。大多数 SQL DBMS 无法以声明方式强制执行该规则，除非将类型列推入每个表中。
See this example of how you can enforce foreign keys in this situation:
http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx
The item_type column violates 2nd Normal Form, BUT I think that's OK because: A) You can easily implement CHECK constraints which mean anomalies cannot arise, B) it's a small price to pay in order to enforce the very useful constraint that an item cannot be of more than one type. Most SQL DBMSs cannot enforce that rule declaratively unless you push the type column into each table.