当前位置：文江博客话题详情

使用两个以上的表创建外键

发布于 2024-12-08 16:06:05 字数 1998 浏览 0 评论 0原文

我有以下表格。

表系统

sys_id
name

Primary(sys_id)

表请求

sys_id
request_id
subject

Primary(sys_id,request_id)
外键requests.sys_id --> system.sys_id

表字段

sys_id
field_id
name

Primary(sys_id,field_id)
外键字段.sys_id --> system.sys_id

表 requests_props

sys_id
request_id
field_id
field_value

主要(sys_id,request_id,field_id)
外键 requests_props( sys_id, request_id ) --> requests(sys_id,request_id)

到目前为止，一切都很好。所以在这里我可以

创建一个具有特定 sys_id 的系统（在表系统中）
在该系统中创建一些字段（属性）（在表字段中）
向系统添加请求（在表请求中）
为每个请求设置字段值（属性）对应于该系统的field_ids。（在表 requests_props 中）

现在我想创建一组草稿请求。因此每个请求可以有多个草稿。草稿只不过是临时请求。因此，我决定采用以下架构

表草稿

草稿_id
sys_id
request_id

Primary(draft_id)
外键草稿(sys_id,request_id) --> requests(sys_id,request_id)

表草稿属性

Draft_id
field_id
field_value

Primary(draft_id, field_id)
外键drafts_props(draft_id) -->草稿(draft_id)
外键????????? Drafts_props 和 fields 表之间 ???????????????

这里我想创建表 *drafts_props* 到 fields 之间的外键关系> 表，通过该表，我可以确保 *drafts_props* 具有相同的 *field_id*，这是 drafts 表和 *drafts_props* 表之间的 *draft_id's* 关联所允许的。 *sys_id* 与该草稿关联。

即，要找到草稿属性表中可以出现的有效字段 ID，我首先必须将草稿属性表连接到草稿表上的草稿 ID，并找到与该草稿关联的 sys_id，然后在字段表中找到与该 sys_id 关联的 field_ids。这将为我提供有效的 field_ids。

但据我所知，我不能使用3个表来创建外键约束。外键 [drafts_props].[draft_id], [drafts].[sys_id] 引用 [fields].[sys_id], [fields].[field_id]

当然，我也可以在表 Drafts_props 中包含 sys_id 列，以便我可以创建这样的限制，但我不想创建这种冗余。我也无法更改表系统、请求、requests_props 和字段。

提前致谢！

原文

I have following tables.

table system

sys_id
name

primary(sys_id)

table requests

sys_id
request_id
subject

primary(sys_id,request_id)
foreign key requests.sys_id --> system.sys_id

table field

sys_id
field_id
name

primary(sys_id,field_id)
foreign key field.sys_id --> system.sys_id

table requests_props

sys_id
request_id
field_id
field_value

primary(sys_id,request_id,field_id)
foreign key requests_props( sys_id, request_id ) --> requests(sys_id,request_id)

Until this point every thing is fine. So here I can

create a system with a particular sys_id ( in table system)
create some fields (properties) in this system ( in table fields )
add request to the system ( in table requests )
set the field values (properties) for each request corresponding to the field_ids of that system. (in table requests_props)

Now I want to create a set to Draft requests.
So each request can have multiple drafts for it.
Drafts are nothing but temporary requests.
So I have decided on following schema

table drafts

draft_id
sys_id
request_id

primary(draft_id)
foreign key drafts(sys_id,request_id) --> requests(sys_id,request_id)

table drafts_props

draft_id
field_id
field_value

primary(draft_id, field_id)
foreign key drafts_props(draft_id) --> drafts(draft_id)
foreign key ?????????? between drafts_props and fields table ??????????????

here I want to create the foreign key relation between the table *drafts_props* to fields table where by I can make sure that the *drafts_props* have the same *field_id* that are allowed by the *draft_id's* association between the drafts table and *drafts_props* table and the *sys_id* associated with that draft.

i.e. to find the valid field_ids that can come in drafts_props table, I first have to join the drafts_props table to drafts table on drafts_id and find the associated sys_id with that draft and then find the field_ids associated with that sys_id in fields table. This will give me the valid field_ids.

but as far as I know I cannot use 3 tables to create the foreign key constraint.
foreign key [drafts_props].[draft_id], [drafts].[sys_id] references [fields].[sys_id], [fields].[field_id]

of course I can include the column sys_id in table drafts_props also so that I can create such a constraint but I don't want to create this redundancy.
Also I cannot change the tables system, requests, requests_props and fields.

thanks in advance !

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无声情话 2024-12-15 16:06:05

在 drafts_props 中包含 sys_id 的问题并不是它会占用物理空间（它会占用物理空间，但这不是问题）。问题是，您最终可能会得到一个 drafts_props 行，该行引用具有一个 sys_id 的 drafts，同时又具有不同的sys_id 本身。

这应该合法吗？

如果是，只需在 PK 之外的 drafts_props 中包含 sys_id 即可。

如果没有，您需要更改数据库模型，以便 sys_id 自然地向下传播标识关系并成为 drafts_props 主键的一部分。像这样的事情：

草稿（
    草稿 ID PK
    系统 ID 主键
    请求ID
    FK（sys_id，request_id）->要求
）

草稿道具（
    草稿 ID PK
    系统 ID 主键
    字段 ID PK
    字段值
    FK（draft_id，sys_id）->草稿
    FK（sys_id，field_id）->场地
）

附加建议：

您可能还需要考虑某些主键中字段的顺序（即，您可能希望将 sys_id 放在第一位，具体取决于典型的查询模式）。
如果您担心物理存储空间，某些 DBMS（我知道 Oracle，其他可能也有）可以相当有效地压缩包含大量重复的复合索引（例如，在您的情况下可能是 sys_id ）。有些甚至可以压缩表数据。

The problem of including sys_id in drafts_props is not that it will take physical space (which it will, but this is not a problem). The problem is that you could end-up with a drafts_props row that references the drafts with one sys_id, while at the same time having a different sys_id itself.

Should that be legal?

f yes, just include sys_id in drafts_props, outside its PK.

If not, you'll need to change the database model so sys_id naturally propagates down identifying relationship and becomes part of the drafts_props primary key. Something like this:

drafts (
    draft_id PK
    sys_id PK
    request_id
    FK (sys_id, request_id) -> requests
)

drafts_props (
    draft_id PK
    sys_id PK
    field_id PK
    field_value
    FK (draft_id, sys_id) -> drafts
    FK (sys_id, field_id) -> field
)

Additional suggestions:

You might also want to consider the order of fields in some of your primary keys (i.e. you might want to put sys_id in the first place, depending on typical query patterns).
If you are concerned about physical storage space, some DBMSes (I know of Oracle, others probably too) can fairly efficiently compress composite indexes that contain a lot of repetitions (such as, presumably, sys_id in your case). Some can even compress the table data.

回复收藏 0 原文