在 SQL Server 2014 中将一个外键引用到多个主键 [英] Reference one foreign key to multiple primary keys in SQL Server 2014

问题描述

我的数据库中有三个表.

I have three tables in my database.

1. MedicalTest [MedicalTestID PK]
2. SportTest [SportTestID PK]
3. GeneralNeeds [GeneralNeedsID PK]

1. MedicalTest [MedicalTestID PK]
2. SportTest [SportTestID PK]
3. GeneralNeeds [GeneralNeedsID PK]

在 GeneralNeeds 里面有 ProcessID 需要保留 MedicalTestID 和 SportTestID GUID.

Inside GeneralNeeds there is ProcessID which needs to keep MedicalTestID and SportTestID GUID.

一旦我从任一表中插入现有 GUID，我就会收到冲突"错误消息，我认为这意味着给定的 GUID 不存在于另一个表中.

As soon as I insert an existing GUID from either tables I get "Conflict" error message which I believe it means the given GUID doesn't exist in the other table.

例如:如果我从 MedicalTest 插入 GUID，那么它会告诉我 SportTest 没有给定的 GUID.

For example: If I insert the GUID from MedicalTest then it will tell me that the SportTest doesn't have the given GUID.

是否可以将多个键引用到一列?

Is it possible to reference multiple keys to one column?

推荐答案

我在这里推荐的设计是表/子表解决方案.这意味着，引入第四个表，Test.Test 表仅用作将所有不同测试类型链接在一起的表.如果您熟悉面向对象编程，请将其视为基本类型.所以本质上，您的 ProcessID 指向 Test，然后它指向一种特定的测试类型(SportsTest、MedicalTest、WhateverTest).如果您愿意，还可以在 Test 表中包含一个 Type 以帮助您了解它是哪种类型的测试.这里有一个关键:在MedicalTest 和SportsTest 表中，主键(比如MedicalTestID)也是一个指向Test 中的 TestID.通过这种方式，您可以在 GeneralNeeds 中使用单个列，因为引用是对单个表类型 Test 的引用.由于 Test 和相应的 MedicalTest 或 SportsTest 将具有相同的主键，因此您可以轻松地进行查询所需的连接.

The design I'd recommend here is a table/sub-table solution. What that means is, introduce a 4th table, Test. The Test table just serves as a table that links all the different test types together. If you're familiar with Object Oriented Programming, think of it as a base type. So essentially, your ProcessID points to Test which then points to one of the specific test types (SportsTest, MedicalTest, WhateverTest). If you like, you could also include a Type in the Test table to help you know which type of test it is. There is a key thing here: In the MedicalTest and SportsTest table, the primary key (say MedicalTestID) is also a foreign key that points to the TestID in Test. In this way you can use a single column in your GeneralNeeds because the reference is to a single table type, Test. Since Test and the corresponding MedicalTest or SportsTest would have the same primary key, you can easily do the joins you need to do the query.

这里是一个示例图，说明了它:

Here is a sample diagram illustrating it:

简单例子:假设您有一个 ID 为 1 的 MedicalTest(让我们暂时忘记 GUID，以便于阅读)然后，您还将在 Test 中拥有一个 ID 为 1 的记录.然后 1 的 ProcessID 真正指向 Test 记录，然后您可以将其连接到 MedicalTest 记录.

Simple example: Say you have a MedicalTest with ID of 1 (let's forget GUIDs for a minute to make it easier to read) Then you'd also have a record in Test with an ID of 1. Then the ProcessID of 1 really points to the Test record which you could then join to the MedicalTest record.

如果您仍然感到困惑，请告诉我，我会尝试添加更多细节，但希望这能让您朝着有效的方向前进.

If you're still confused let me know and I'll try to add a bit more detail but hopefully this gets you going in a direction that works.

这篇关于在 SQL Server 2014 中将一个外键引用到多个主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！