具有多个表的 SQL 外键
如果我有三个表：
music_genres
-----------
music_type_id
genres
[other unique fields]
minerals
--------
mineral_id
mineral
[other unique fields]
verbs
-----
verb_id
verbs
[other unique fields]
并且这些表填充有：
rock
jazz
funk
punk
rock
boulder
stone
shale
rock
wobble
shake
vibrate
现在假设我正在为每个项目显示一个网页，每个项目都使用单独的模板，并将这些信息存储在下表中：
pages
-----
page_id
page_url
template_url
foreign_key_id
包含以下数据：
/page/music-genres/rock/
/music-genres-template.html
1
/page/verbs/rock/
/verb-template.html
1
/page/minerals/rock/
/mineral-template.html
1
/page/minerals/rock/images/
/mineral-images-template.html
1
模板将知道外键与特定的相关表相关，因此矿物模板知道要查询矿物表以获取该表的其他字段。但是，我没有任何引用完整性。如果从矿物表中删除岩石，我无法使用级联删除从页面中删除行，因为外键不是唯一的。我可以想出很多方法来解决这个不优雅的
- 问题 ： 触发器 将
- 项目类型存储在 每行的 music_types、矿物质 和动词表，并使用这个 附加外键
- 在页面中存储适当的表名 保持表
- 的完整性 。
我认为必须有更好的方法来存储我的数据或维护数据库的完整性 有什么想法吗？
If I have three tables:
music_genres
-----------
music_type_id
genres
[other unique fields]
minerals
--------
mineral_id
mineral
[other unique fields]
verbs
-----
verb_id
verbs
[other unique fields]
and these are populated with:
rock
jazz
funk
punk
rock
boulder
stone
shale
rock
wobble
shake
vibrate
Now let's say I was displaying a web page for each of these items, each using a separate template, and stored this information in the following table:
pages
-----
page_id
page_url
template_url
foreign_key_id
with data such as:
/page/music-genres/rock/
/music-genres-template.html
1
/page/verbs/rock/
/verb-template.html
1
/page/minerals/rock/
/mineral-template.html
1
/page/minerals/rock/images/
/mineral-images-template.html
1
The templates will know that the a foreign key relates to the specific related table, so the mineral template with know to query the minerals table for that table's other fields. However, I don't have any referential integrity. If rock is deleted from the minerals table, I can't use cascading deletes to remove rows from pages, as foreign keys aren't unique. I can think of numerous ways to get around this inelegantly:
- Emulating cascading deletes with
triggers - Storing the item type in
each row of the music_types, minerals
and verbs tables, and using this a an
additional foreign key - Storing the appropriate table name in the pages
table - Maintaining the integrity of
the database with PHP etc.
I'm thinking that there must be a better way of either storing my data or maintaining the integrity of the database. Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为你的问题出在这里
这是不存储在数据库中任何地方的知识。
我看到了两种解决方法：
鉴于您实际上是为每种类型的“事物”创建单独的表，因此您应该为每种类型的事物创建一个不同的列，引用页面表中的相应表，设置所有列为空，除了一个（这可以通过约束强制执行）
I think that your problem resides here
That's knowledge which is not stored anywhere in the database.
I see two ways out of it:
Given that you are actually creating separate tables for each type of 'thing', you should have a differente column for each type of thing referencing the appropriate table in your pages table, setting all columns to null except one (this could be enforced via a constraint)
Have a 'master things' table with a unique id which then pages could reference, having both a column to identify the type, and a column pointing to the rest of the unique data, which would be stored in a different table.