如何处理引用两个表的可为空的主键 [英] How to handle nullable primary key that references two tables

问题描述

我有一个包含三个表的数据库，分别称为 foo ， bar 和 baz 。每个 bar 实例引用一个 baz 实例，尽管可能存在 baz 未在 bar 表中引用。在大多数情况下， foo 的实例将引用 bar 的实例（因此可以映射到以下实例： baz 。）但是在某些情况下， foo 的实例将引用 baz 表直接。

I have a database with three tables, call them foo, bar and baz. Each instance of bar references an instance of baz, although there may be instances of baz not referenced in the bar table. In most cases, an instance of foo will reference an instance of bar (and therefore can be mapped to an instance of baz.) However in some cases an instance of foo will reference the baz table directly.

在简化版本中， foo - baz 关系不存在，我可以轻松地创建 barid 部分 foo 表的主键的值，这是我想在这里做的，除了 barid 偶尔为空。

In a simplified version where the foo-baz relationship doesn't exist, I can easily make barid part of the primary key for the foo table, which is what I'd like to do here, except that barid will occasionally be null.

因此， foo 表中的示例如下所示：

Therefore a sample from the foo table might look like this:

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL

问题是如何处理为 foo 创建主键表。我应该使用唯一索引而不是约束吗？我还可以创建 bar 的虚拟实例，以连接foo和 baz 表。在那种情况下，我可能会添加一个 isreal 字段，这样我就知道是什么情况。我敢肯定有人会告诉我为什么这是个坏主意。

The issue is how to handle creating a primary key for the foo table. Should I be using unique indexes instead of a constraint? I could also create dummy instances of bar for the purpose of connecting the foo and baz tables. In that case I might add an isreal field so I know when this is the case. I'm sure someone can tell me why this is a bad idea though.

推荐答案

在不了解应用程序和实现的情况下，这个问题没有简单的答案。请参见下面为关系创建的其他排列

There is not straightforward answer to this question without understanding the application and the implementation.Please see below the other permutations created for relations amongst the table.

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL
    5       18     6
    6       14     6
    6       18     10

该问题的通用答案是请不要创建伪值，而bar id主键将仅是fooid。如果fooid，barid和bazid总是唯一的，则添加唯一约束。

Generic answer to the question is please do not create dummy values , instead the bar id primary key will be the fooid only. And add a unique constraint if the fooid,barid and bazid is always going to be unique.

这篇关于如何处理引用两个表的可为空的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！