我对 PostgreSQL 和 SQL 总体来说是新手,并且在理解 PK、FK、序列 id 以及何时使用它们方面遇到了一些问题。我将尝试添加到目前为止我已经尝试过的信息,但我无法回忆起所有内容,因为我已经做了很多尝试和错误。
https://dbdiagram.io/d/biodeg-65f43429ae072629ce19c2b8
这是我的表格的简化版本。我通过 python 和 sqlalchemy 从 csv 文件填充数据库解析和格式化数据。Atm 数据库应该是
每次导出/导入表都会得到:
- “设置元数据”表中的一条新记录。(“Set”是这里的唯一值)
- “测量元数据”表中最多有 6 个新记录(所有这些记录都具有与 1 中添加的记录相同的“Set”值,仅作为“Set”和“Kopf”的组合是唯一的, “ID”或“SN 号”
- “测量数据”表中多达 360 条新记录的六倍(同样,“Set”和“Kopf”(或“ID”/“SN Nr”)的组合应该充当与来自“测量元数据”表的记录。
所以我的问题是:
- 我应该向这些表添加自动递增序列 ID 吗?直观上来说,这对于表 1 和表 2 来说是有意义的,但对于表 3 来说则没有意义,因为来自单个测试的 360 个数据点实际上并不需要递增的 id?
- 假设我没有添加任何 id:我设置 PK、FK 的想法之一如下所示:
。
- PK“设置元数据”表中的“设置”。
- “测量元数据”表中“Set”和“Kopf”的综合 PK
- FK 表示“测量元数据”表中的“集合” 引用“集合元数据”表中的“集合”。
- “测量数据”表中“Set”+“Kopf”的 FK 参考“测量元数据”表中的“Set”和“Kopf”。
- 另外,我认为我必须设置一些独特的约束才能设置这些 FK。
然而,这并没有成功。在 PowerBI 中,这结果是表 1 和表 2 之间的 1:1 关系,这应该是一对多关系(?)。表 2 和表 3 之间也没有任何关系。
那么,我的 3 张桌子应该使用哪种方式 PK 和 FK 呢?
另外,如果建议我向这些表添加 id,则假设表 1 为“set_id”,表 2 为“measurement_id”,这些将成为我的 PK。然后我需要“set_id”作为表 2 中的 FK,但是如何将“set_id”添加到表中,其值与表 1 中引用的“set_id”相同。毕竟,我最多需要 6 个具有相同“set_id”的记录。
非常感谢,我希望这不是要求太多
每个“主”实体(或表)代表一个对象的实例(即记录),在没有任何其他实体(例如人)的情况下,该对象可以以其自身的权限存在。唯一标识每个实例的属性是该表上主键的候选者。主键的想法是,您在第一次创建记录时确定(或分配)其值,并且该值在该记录的整个生命周期中保持不变,直到它最终被永久销毁。
例如,一个人可以通过其社会安全号码进行唯一标识,但可能在非常特殊的情况下甚至必须更改。在这种情况下,任意数字标识符将作为主键(但仍然对 SSN具有唯一约束)。YMMV。
从你的图表中,我想说你的主键和外键应该是:
现在,有些人可能会对这些复合键、自然键、主键感到惊恐地举手,但是,只要您正确选择了它们,它们就不是问题。
我建议让你失望的是你的工具集,而不是你的数据建模。
如果您是 SQL 新手:
(set, kopf, time) 是一个元组。
说 (set, kopf, time) 是唯一的意味着只能有一行具有特定值 (set, kopf, time)。这并不意味着任何列都是唯一的,只是列中特定值的特定组合是唯一的。这也意味着对于 (set, kopf) 的每个特定值,每个 (time) 值只能有一行。
如果(set, kopf, time)是唯一的,那么它可以是主键。由于主键会自动在 (set, kopf, time) 上创建索引,这也为您提供了一种快速的方法
SELECT WHERE set=... AND kopf=... ORDER BY time
。如果数据自然地给你一个主键,你就不需要创建一个id。
但在本例中,有一点疑问,因为“set”是 varchar。如果只是几个字符那就没问题了。但是,如果它是一个大字符串,并且您有很多行,您可能会发现表使用的磁盘空间的很大一部分由该字符串组成。在这种情况下,将 id 添加到表“测量元数据”、从“测量”中删除 set 和 kopf 并使用该 id 可能会很有用。
另一种可能遇到麻烦的情况是,如果键入“set”和“kopf”中使用的字符串值时出现错误,或者出现任何需要更新的情况。要更新这些值,如果它们用作外键,则必须在各处更新它们。这可以通过 ON UPDATE CASCADE 自动完成,但如果有很多行,则可能需要一段时间。此外,它也不会更新数据库之外的内容,例如使用数据生成的绘图的文件名,或者绘图上显示“数据集编号”的图例。
生成的 id 的要点是它们没有什么特别的,除了引用特定行之外,它们没有任何意义,因此它们永远不需要更新,也永远不会重用。因此,如果更新标题、名称或其他文本字段,数据库外部可能使用 id 引用特定行的内容不会破坏引用。我使用绘图作为示例,但您明白了,它可以是任何东西。如果您使用某些内容作为主键,则它永远不需要更新。
对于 set_metadata,我会说是。
对于measurement_metadata,我不确定。您可以:
使用(set_id,kopf)作为PK,则与measurement_data中的FK相同
将自动生成的 id PK 添加到measurement_metadata,然后用特定的(set_id,kopf)标识行,然后在measurement_data 中使用此id。
是的:想必您不会同时对同一个(set,kopf)进行两个测量,因此您不需要额外的 id 来区分它们。
假设“set_metadata”将set_id作为PK。当你的 python 代码执行插入操作时,它会通过“INSERT ... RETURNING”子句获取插入的 id。然后,您可以使用该值将 6 行插入到“measurement_metadata”中。
同样的事情也适用于其他表,每次有 FK 时:插入到父表中,获取生成的 id,用它插入到子表中。
记住将整个事情包装到一个事务中。这确保了它要么工作要么失败(不能插入不完整的数据),并且速度更快,因为它避免了每行 COMMIT 开销。