T-SQL 数据库关系 PK FK 同名？
场景
我有 3 个数据库表。一个用于图像，另外两个用于人物和图像。地方。由于每个人可以有很多图像，每个地方可以有很多图像，所以我想在人和图像以及地方和图像之间建立一对多的关系。
问题
外键必须与主键同名吗？或者我是否可以将图像表中的外键称为通用名称，例如“PKTableID”。这样我只需要一张图像表。
非常感谢帮助。
问候，
编辑：
只想拥有一个图像表的原因是因为每个图像仅引用一个其他表。除此之外，我在这里使用了两个表的示例，我将使用的实际数据库将有 20 个表，所以我想知道是否仍然可以使用单个图像表来处理 20 个一对多关系？
Scenario
I have 3 database tables. One is for Images and the other two are People & Places. Since each person can have many images and each place can have many images, I want to have a ONE TO MANY Relationship between both people and images, as well as places and images.
Question
Does the foreign key have to be called the same name as the primary key? Or is it possible for me to call the Foreign key in the images table something generic, for example "PKTableID". This way I only need one image table.
Help greatly appreciated.
Regards,
EDIT:
The reason for wanting to have only a single image table, is because each image only refers to a single other table. As well as this, I used the example here of two tables, the actually database I will be using will have 20 tables, so I wanted to know whether it was still possible to use a SINGLE IMAGE TABLE FOR 20 ONE-TO-MANY RELATIONSHIPS?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
编辑
如果一张图像仅由二十个表之一拥有，则此设计可能有效：
其中 OwnerTable 是 OwnerId 所属表的名称或代码。
这将为您在图像表中节省 20 个 FK，或 20 个关联表。然后，在联接中，您将指定 OwnerTable，具体取决于您要联接的表。
您需要对 Id 使用可转换类型（例如 TINYINT、SMALLINT 和 INT），并且最好对所有 Id 使用一种类型（例如 INT），并且您必须通过触发器或其他代码自行管理引用完整性。
/编辑
您需要 5 个表，而不是 3 个：
您可以随意命名字段。 People.Id、ImagesPeople.PersonId 等。
但是您不能做的是这样的事情：
嗯，您可以，但是数据库不会帮助您强制执行关系，或者告诉您 FK 属于哪个表。你怎么知道？有一些 hackish 解决方法，例如交错 id 增量、向图像添加类型列或使用 GUID。
或者：
您也可以使图像成为“事物”。有时你会看到这样的设计。它确实为您提供了引用完整性，但没有类型排他性。您可以在人物、地点和图像中拥有相同的 ThingId，而数据库不会关心。您必须自己编写该规则的代码。
编辑：根据 Cylon Cat 的建议，场景 4：
在这里，图像仅由一个人或一个地方拥有。与版本 2 类似，但具有声明的外键。与 5 表设计相比，它可能具有一些性能优势，因为所需的联接更少。您失去了作为独特实体的“图像”，取而代之的是“PeopleImage”和“PlaceImage”。
EDIT
If one image is only ever owned by one of the twenty tables, this design might work:
Where OwnerTable is the name or the code for the table that OwnerId belongs to.
This would save you 20 FKs in the image table, or 20 association tables. Then, in the joins, you would specify OwnerTable, depending on the table you are joining to.
You would need to use convertable types for the Ids (eg, TINYINT, SMALLINT, and INT), and preferably one type for all (eg, INT), and you would have to manage referential integrity yourself though triggers or some other code.
/EDIT
You need 5 tables, not 3:
You can call the fields whatever you want. People.Id, ImagesPeople.PersonId, etc.
But what you can't do is something like this:
Well, you can, but the database won't help you enforce the relationship, or tell you which table the FK belongs to. How would you know? There are hackish work-arounds like staggering the id increments, adding a type column to Images, or using GUIDs.
Alternatively:
You could also make Images a "Thing". Sometimes you see designs like this. It does give you referential integrity, but not type exclusivity. You could have the same ThingId in People, Places and Images, and the database wouldn't care. You would have to code that rule yourself.
Edit: at Cylon Cat's suggestion, scenario 4:
Here, images are exclusively owned by one person or place. Similar to version 2, but with declared foreign keys. It may have some performance benefits vs the 5 table design, since fewer joins are required. You lose "Image" as a distinct entity, replaced by "PeopleImage" and "PlaceImage".
理论上，您可以添加两个外键到图像表中，一个用于人物，一个用于地点。然后，您可以在运行查询时允许空值并连接适当的列。但这并不是一个很好的解决方案，因为当您有 14 个表需要与之连接时，该表会是什么样子？
如果您不打算使用 GUID，那么我建议您将其设置为多对多，以便下一个必须理解它的人。
You could add theoretically add two foreign keys to the images table, one for People and one for Places. Then you could allow nulls and join on the appropriate columns when running a query. This is not much of a solution though because what does this table look like when you have 14 tables that need to join with it?
If you are not going to use GUIDs, then I say you set it up as many-to-many for the sake of the next guy that has to understand it.
正如彼得指出的那样，你确实需要一种多对多的关系，除非你想将你的图像限制为只有一个人的图片，而且，一个好的地点索引将反映地名的层次性质（蒙马特，巴黎，法国=三种可能一个地方的名称）。
现在从技术上讲，您可以将索引和表称为任何非保留字且尚未使用过的名称，X、Y、Z12345 和 WOBBLY 都是索引的有效名称。
然而，在实践中，最好遵循命名约定，指出存储的内容和用途。因此，对于您的情况，表 PEOPLE_X_IMAGES 和 PLACES_X_IMAGES 将是一个好主意。您实际上不需要有关实际图像表中的人物或地点的任何信息。同样，您不需要有关 PEOPLE 和 PLACES 表中图像的任何信息。
As Peter pointed out you really need a many to many relationship, unless you want to restrict your images to pictures with only one person, also, a good place index will reflect the hierarchical nature of place names (Montmartre, Paris, France = three possible names for one place).
Now technically you can call your indexes and tables anything that is not a reserved word and has not been used already, X, Y , Z12345 and WOBBLY are all valid names for an index.
In practice however its best to follow a naming convention that points to whats being stored and what for. So tables PEOPLE_X_IMAGES and a PLACES_X_IMAGES would be a good idea in your case. You dont really need anything about people or places in the actual images table. Likewise you dont need anything about images in the PEOPLE and PLACES tables.
您可以使用一张表，但需要二十个不同的字段来建立关系。如果设置了外键关系，那么所有数据都必须与父表相关，不能在一张表中存储两个外键关系。所以你必须为每个你想要拥有的 fk 设置一个列。
You can use one table but you would need twenty different fields for the relationships. If you set up a foreign key relationship, then all the data must relate to the parent table, you can't store two foreign key relationships in one table. So you must set up a column for each fk you want to have.
怎么样
How about