自引用表的部分键

I am designing a datatable (-s) for storing the list of shareholders. In case a shareholder is a nominee, it discloses the list of share owners. These all need to have a pile of similar references. Thus, I would like to store all in one table. Nominees are registered shareholders and thus, they have an account number in the system, from which I get the data. Opposed to this, share owners that are coming from nominee disclosure have no their own account number, and share account number of the nominee.

我正在设计一个数据表(-s)来存储股东名单。如果股东是被提名人,则会披露股东名单。这些都需要有一堆类似的参考。因此,我想将所有内容存储在一个表中。被提名人是注册股东,因此他们在系统中有一个账号,我可以从中获得数据。与此相反,来自被提名人披露的股东没有自己的账号,并且分享了被提名人的账号。

I add a uniquifier to the table. I would like to assume that all registered shareholders and the nominees have 0 in this field. The disclosure list (non-registered share owners, that work via the nominee) have a value above zero in this uniquifier filed. Basically, a numbered list.

我在表中添加了一个uniquifier。我想假设所有注册股东和被提名人在这个领域都有0。披露清单(通过被提名人工作的非登记股份所有人)在此提名者中的值大于零。基本上,编号列表。

I need to know the nominee, to whom this disclosed share owner belongs. So, we are coming to a self-reference. It could be Ok, if not for the uniquifier field. It is illegal to make a foreign key on part of a primary key and assume that there is zero in the uniquifier field. So, could you please suggest a walkthrough.

我需要知道这位被披露的股东所属的被提名人。所以,我们正在进行自我参考。如果不是uniquifier字段,那可能是好的。在主键的一部分上创建外键并假设uniquifier字段中存在零是非法的。那么,请你推荐一个演练。

To better explain, here is the excerpt:

为了更好地解释,这里是摘录:

Create Table Account (
  AccountId Int,
  Uniquifier Int,

  NomineeId Int,

  Constraint PK$Account Primary Key (AccountId, Uniquifier)     
)

What I'm trying to do is something like:

我想要做的是:

Alter Table Account 
  Add Constraint FK$SelfReference Foreign Key (NomineeId) References Account (AccountId)

which is illegal, since

这是非法的,因为

There are no primary or candidate keys in the referenced table 'Account' that match the referencing column list in the foreign key 'FK$SelfReference'.

Any help is appreciated.

任何帮助表示赞赏。

1 个解决方案

#1

You can add a computed column which will be 0 when the FK ought to be enforced and NULL otherwise. This can then be used in the foreign key reference:

您可以添加一个计算列,当FK应该强制执行时为0,否则为NULL。然后可以在外键引用中使用它:

Create Table Account (
  AccountId Int,
  Uniquifier Int,

  NomineeId Int,
  NomineeXRef as CASE WHEN Uniqueifier > 0 THEN 0 END persisted

  Constraint PK$Account Primary Key (AccountId, Uniquifier),
  Constraint FK$SelfReference Foreign Key (NomineeId,NomineeXRef) References Account (AccountId, Uniquifier)     
)