我正在使用 Kotlin 和 Jetbrain 的公开 SQL 库为我正在开发的项目设置数据库。我正在尝试在两个表cw_cache(父表)和cw_requests(子表)之间建立外键关系。
此设置适用于 Sqlite 数据库,但当我尝试在 MySQL 服务器数据库中创建表时不起作用。我收到错误“java.sql.SQLException:无法添加外键约束”。
我已经在这里查看了类似的问题,并确保父表和子表中的列具有相同的数据类型,父表中的列实际上是键,并且 cw_cache 表是在 cw_requests 表之前创建的。
当我跑步时SHOW ENGINE INNODB STATUS;
要查看外键错误,我看到以下内容:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-04-05 18:29:17 2e94 Error in foreign key constraint of table coursewatcher/cw_requests:
FOREIGN KEY (term) REFERENCES cw_cache(term) ON DELETE RESTRICT ON UPDATE RESTRICT):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
来自Sqlite数据库的图表:https://gyazo.com/220dd4b1a3d301419e0b8b73bfc80a68 https://gyazo.com/220dd4b1a3d301419e0b8b73bfc80a68
相关代码:
cw_cache表:
object Cache : Table("cw_cache") {
val crn = varchar("crn", 5).primaryKey()
val term = varchar("term", 6).primaryKey()
// other column initializers
}
cw_请求表:
object Requests : Table("cw_requests") {
val id = long("id").primaryKey()
val orderId = long("order_id") references Orders.id
val crn = varchar("crn", 5) references Cache.crn
val term = varchar("term", 6) references Cache.term
// other column initializers
}