MySQL 外键不允许插入

Question

我正在使用的 MySQL 数据库遇到问题。我有一个表 DAY_ITEM，其中有两个可为 null 的列:FOOD_ID 和 MEAL_ID，它们是 ID 列的外键分别位于 FOOD_ITEM 和 MEAL_ITEM 表中。

当我尝试将新记录插入 FOOD_ITEM 表时，出现此错误:

[HY000][1364] Field 'FOOD_ID' doesn't have a default value

但是该列不在 FOOD_ITEM 表中，FOOD_ITEM 表只有 ID 列，它是DAY_ITEM 表中的 FOOD_ID 列。

下面是我制作表格的 SQL 脚本，我在这些脚本中做错了什么？

DAY_ITEM 脚本

CREATE TABLE DAY_ITEM
(
  ID        BIGINT       NOT NULL,
  EMAIL     VARCHAR(50)  NOT NULL,
  NAME      VARCHAR(100) NULL     DEFAULT NULL,
  MOD_ID    SMALLINT     NOT NULL, #MOD = Meal Of Day
  MOD_NAME  VARCHAR(50)  NULL     DEFAULT NULL,
  DAYS_DATE DATE         NOT NULL,
  FOOD_ID   BIGINT       NULL     DEFAULT NULL,
  MEAL_ID   BIGINT       NULL     DEFAULT NULL
);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_PK_ID
PRIMARY KEY (ID);

ALTER TABLE DAY_ITEM
MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT;

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_EMAIL
FOREIGN KEY (EMAIL) REFERENCES USER_ACCOUNT (EMAIL);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_FOOD_ID
FOREIGN KEY (FOOD_ID) REFERENCES FOOD_ITEM (ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_MEAL_ID
FOREIGN KEY (MEAL_ID) REFERENCES MEAL_ITEM (ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT MEAL_ITEM_UK_EMAIL_DAYSDATE_FOODID
UNIQUE (EMAIL, DAYS_DATE, MOD_ID, FOOD_ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT MEAL_ITEM_UK_EMAIL_DAYSDATE_MEALID
UNIQUE (EMAIL, DAYS_DATE, MOD_ID, MEAL_ID);

FOOD_ITEM 脚本

CREATE TABLE FOOD_ITEM
(
  ID             BIGINT        NOT NULL,
  EMAIL          VARCHAR(50)   NOT NULL,
  NAME           VARCHAR(100)  NULL     DEFAULT NULL,
  SERVING_AMOUNT DECIMAL(6, 2) NULL     DEFAULT NULL,
  SERVING_SIZE   VARCHAR(50)   NULL     DEFAULT NULL,
  SERVING_ID     SMALLINT      NOT NULL,
  CALORIES       SMALLINT      NULL     DEFAULT NULL,
  PROTEIN        SMALLINT      NULL     DEFAULT NULL,
  CARBS          SMALLINT      NULL     DEFAULT NULL,
  SUGAR          SMALLINT      NULL     DEFAULT NULL,
  FIBER          SMALLINT      NULL     DEFAULT NULL,
  FAT            SMALLINT      NULL     DEFAULT NULL,
  SAT_FAT        SMALLINT      NULL     DEFAULT NULL,
  MONO_FAT       SMALLINT      NULL     DEFAULT NULL,
  POLY_FAT       SMALLINT      NULL     DEFAULT NULL,
  TRANS_FAT      SMALLINT      NULL     DEFAULT NULL,
  SODIUM         BIGINT        NULL     DEFAULT NULL,
  CHOLESTEROL    BIGINT        NULL     DEFAULT NULL
);

ALTER TABLE FOOD_ITEM
ADD CONSTRAINT FOOD_ITEM_PK_ID
PRIMARY KEY (ID);

ALTER TABLE FOOD_ITEM
MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT;

ALTER TABLE FOOD_ITEM
ADD CONSTRAINT FOOD_ITEM_FK_EMAIL
FOREIGN KEY (EMAIL) REFERENCES USER_ACCOUNT (EMAIL);

ALTER TABLE FOOD_ITEM
ADD CONSTRAINT FOOD_ITEM_UK_EMAIL_NAME_SERVING
UNIQUE (EMAIL, NAME, SERVING_AMOUNT, SERVING_SIZE, SERVING_ID);

编辑

这是我正在使用的引发错误的插入语句:

INSERT INTO FOOD_ITEM (EMAIL, NAME, SERVING_AMOUNT, SERVING_SIZE, SERVING_ID, CALORIES, PROTEIN, CARBS, FAT)
VALUES ('userOne@gravytrack.com', 'Caviar 2', 1.00, 'serving', 0, 250, 12, 13, 14);

我根本不明白为什么它要查看 DAY_ITEM 中的 FOOD_ID。我没有插入 DAY_ITEM，而是插入 FOOD_ITEM。即使我没有在此处包含 ID，它也应该自动递增。在添加 DAY_ITEM 表之前，这就是过去的工作方式。

Answer 1

尝试删除默认 NULL 值并在插入记录时传递NULL。

你的脚本显示如下...

CREATE TABLE DAY_ITEM
(
  ID        BIGINT       NOT NULL,
  EMAIL     VARCHAR(50)  NOT NULL,
  NAME      VARCHAR(100) NULL     DEFAULT NULL,
  MOD_ID    SMALLINT     NOT NULL, #MOD = Meal Of Day
  MOD_NAME  VARCHAR(50)  NULL     DEFAULT NULL,
  DAYS_DATE DATE         NOT NULL,
  FOOD_ID   BIGINT       NULL,
  MEAL_ID   BIGINT       NULL
);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_PK_ID
PRIMARY KEY (ID);

ALTER TABLE DAY_ITEM
MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT;

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_EMAIL
FOREIGN KEY (EMAIL) REFERENCES USER_ACCOUNT (EMAIL);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_FOOD_ID
FOREIGN KEY (FOOD_ID) REFERENCES FOOD_ITEM (ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT DAY_ITEM_FK_MEAL_ID
FOREIGN KEY (MEAL_ID) REFERENCES MEAL_ITEM (ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT MEAL_ITEM_UK_EMAIL_DAYSDATE_FOODID
UNIQUE (EMAIL, DAYS_DATE, MOD_ID, FOOD_ID);

ALTER TABLE DAY_ITEM
ADD CONSTRAINT MEAL_ITEM_UK_EMAIL_DAYSDATE_MEALID
UNIQUE (EMAIL, DAYS_DATE, MOD_ID, MEAL_ID);

无需更改第二个表。