主页 / dba / 问题 / 321134
Accepted
DevelJoe
Asked: 2022-12-19 05:13:03 +0800 CST

设置列的默认值等于插入记录的PK，并将其用作外键引用

  • 772

我正在使用 MariaDB 10.3，而我想要做的是建立一对多的关系，例如在“一对多关系”部分中指定的示例。我正在尝试通过以下方式这样做：

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL DEFAULT ID
);

CREATE TABLE related_table (
  ID INT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
)

SQL Fiddle Editor 等在执行时失败，错误：

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of 'ID'

所以我想你不能将一列的默认值设置为等于另一列，如果另一列的值是由函数或表达式定义的？

如果我然后省略AUTO_INCREMENT，我会得到：

Can't create table "fiddle"."related_table" (errno: 150 "Foreign key constraint is incorrectly formed")

所以我假设您不能基于具有默认值的引用列创建 FOREIGN KEY 约束？

您是否看到另一种可能性来做我需要的事情，那就是：

A）当我插入一条新记录test_table但不提供test_value时，它的值应该等于插入记录的主键，这应该是自动生成的。

B) related_table'sID应该将test_table'test_value称为外键。

我目前看到的唯一解决方法是：

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL
);

CREATE TABLE related_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
);

然后，如果我将例如 3 条记录插入test_table：

  1. 将第一条新记录插入test_table，然后获取LAST_INSERT_ID.
  2. test_value通过将 的值设置为等于返回的值来更新刚刚创建的第一条记录LAST_INSERT_ID。
  3. 将剩下的两条记录插入到test_table中，这次直接将LAST_INSERT_ID之前返回的作为 的值test_value。
  4. related_table将返回的值LAST_INSERT_ID作为其主键ID字段插入新记录。

但这似乎过于复杂。这个用例没有更好的解决方案吗？

mariadb

1 个回答

  1. Best Answer

    对于您的 3 行示例，重新设计如下：

    • 有2张桌子

    • 一张表每块一行，并提供 auto_increment

    • 另一个表有 3 行

    • 然后构建一个事务来完成所有步骤：

      BEGIN;
INSERT INTO table1 ...;
get $id from the LAST_INSERT_ID()
foreach ...   -- walk through the 3 things
    INSERT INTO table2
        (..., test_value, ...)
        VALUES (..., $id, ...);
COMMIT;

    思考哪些列“属于”一个表和另一个表。您可能会发现最终结果比您假设的设计更清晰。

    • 1

相关问题