数据库设计：使用非键作为FK？

Question

假设我有下表：

TABLE: widget
 - widget_id (not null, unique, auto-increment)
 - model_name (PK)
 - model_year (PK)

model_name 和 model_year 组成一个复合键。在另一个表中使用widget_id作为FK有什么问题吗？

Answer 1

键是任意数量的列，可用于唯一标识表中的每一行。

在您显示的示例中，您的小部件表有两个键：

• model_name、model_year
• widget_id

在标准 SQL 中，外键可以引用引用表上的任何声明键（主键或唯一键）。我需要检查 MySQL 的合规性。

---

来自MySQL参考手册中的外键 :

InnoDB 允许外键引用任何索引列或列组。但是，在引用的表中，必须有一个索引，其中引用的列按相同顺序列为第一列。

---

作为替代方案，如果您希望使用引用表中的复合键，则该表中将有两列对应于 model_name 和 model_year，然后将外键约束声明为：

ALTER TABLE OtherTable ADD CONSTRAINT
     FK_OtherTable_Widgets (model_name,model_year)
     references Widgets (model_name,model_Year).

---

Re InnoDB vs MyISAM，在ALTER TABLE 的文档

InnoDB 存储引擎支持 FOREIGN KEY 和 REFERENCES 子句，该引擎实现 ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...)。请参见第 13.6.4.4 节“外键约束”。对于其他存储引擎，这些子句会被解析但被忽略。 CHECK 子句被所有存储引擎解析但忽略。请参见第 12.1.17 节“CREATE TABLE 语法”。接受但忽略语法子句的原因是为了兼容性，以便更轻松地从其他 SQL 服务器移植代码，以及运行使用引用创建表的应用程序。请参见第 1.8.5 节“MySQL 与标准 SQL 的差异”。

Answer 2

我没有特定于 my-sql 的经验，但对于一般的数据库建模来说，

了解主键和辅助键之间的区别非常重要。
即使许多数据库（我确信 Oracle 确实如此）允许指定唯一（简单或复合）键作为 FK 目标，但这也不被认为是最佳实践。改用PK。

在我看来，辅助键的 FK 仅应用于与不受您控制的表相关。

在你的具体情况下，我肯定会FK到widget_id：那是因为widget_id应该是你的PK，并且复合材料只是唯一的（当然不是空的）。这会在鬃毛情况下带来更好的性能，因为您在查询中只加入一列，并且通常被认为是最佳实践（谷歌“代理键”以获取更多信息）

Answer 3

如果没有可用的索引，MySQL 将在该列上创建一个索引：

InnoDB 需要外部索引
键和引用的键，以便
外键检查可以很快，但不能
需要进行表扫描。在
引用表，必须有一个
外键列所在的索引
被列为第一列
相同的顺序。这样的索引就创建好了
自动在参考表上
如果它不存在。 （这是在
与一些旧版本相比，
必须创建哪些索引
明确或建立外国
关键约束将失败。）
index_name，如果给定，则用作
前面已经描述过。

http://dev.mysql.com/doc /refman/5.1/en/innodb-foreign-key-constraints.html

Answer 4

假设您有两张桌子。第一个表如下所示：

TABLE: widget
 - model_name (PK)
 - model_year (PK)
 - widget_id (not null, unique, auto-increment)

如果您想创建另一个表来引用第一个表中的唯一记录，它应该如下所示：

TABLE B: sprocket
 - part_number (PK)
 - blah
 - blah
 - model_name_widget (FK to widget)
 - model_year_widget (FK to widget)
 - blah 

使用复合主键，您必须在 FK 引用中包含所有关键字段以确保您唯一指定一条记录。