MySQL Foreign key not allowing insert

Question

I am having an issue with a MySQL database I'm working on. I have a table DAY_ITEM with two nullable columns, FOOD_ID and MEAL_ID, that are foreign keys to the ID column in FOOD_ITEM and MEAL_ITEM tables respectively.

When I try to insert a new record into the FOOD_ITEM table I get this error:

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

But that column isn't in the FOOD_ITEM table, the FOOD_ITEM table only has the ID column which is the foreign key of the FOOD_ID column in the DAY_ITEM table.

Below are my SQL scripts that make the tables, what I am doing wrong in these scripts?

DAY_ITEM Script

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 Script

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);

EDIT

Here is the insert statement I'm using that throws the error:

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);

I don't see why it's looking at the FOOD_ID in DAY_ITEM at all. I'm not inserting into DAY_ITEM I'm inserting into FOOD_ITEM. Even though I don't include an ID here it should just auto-increment. That's the way it worked in the past before I added the DAY_ITEM table.

Possible duplicate of [Field 'id' doesn't have a default value?](http://stackoverflow.com/questions/25865104/field-id-doesnt-have-a-default-value) — MusicLovingIndianGirl, Mar 28 '16 at 06:24
@MusicLovingIndianGirl Nope, wasn't the same problem at all. Did you read my actual question or just the title? — Graham, Mar 29 '16 at 05:51

Zafar Malik · Answer 1 · 2016-03-29T04:18:24.693

0

insert a row with value NULL in id column in your parent table, then you can insert NULL value in child table.

Better option is you should avoid NULL values...

Update:

As you can see in your insert statement that food_id and meal_id fields are not included in insert statement means you are inserting default NULL value in these fields while in your master tables you have set these fields as not null means you are trying to insert a value (NULL) which does not exist in parent table.

edited Mar 29 '16 at 04:18

answered Mar 28 '16 at 06:25

Zafar Malik

6,734
2
19
30

To avoid null values would I need to use a linking table? – Graham Mar 28 '16 at 06:29
just set column property as not null or you can assign a default value. – Zafar Malik Mar 28 '16 at 06:30
I edited my question with the insert statement I'm using. – Graham Mar 28 '16 at 14:24
As you can see in your insert statement that food_id and meal_id fields are not included in insert statement means you are inserting default NULL value in these fields while in your master tables you have set these fields as not null means you are trying to insert a value (NULL) which does not exist in parent table. – Zafar Malik Mar 29 '16 at 04:17
Turns out the above scripts are just fine, and so is the insert statement. When I was setting up the hibernate entity classes with JPA annotations and adding the foreign key relationship I accidentally made hibernate add the `FOOD_ID` column to the `FOOD_ITEM` table. So there actually was a `FOOD_ID` column in the table without a default value. I deleted the column and everything works now. Just an FYI your solution would not have worked, just for future reference if you run into a similar problem. – Graham Mar 29 '16 at 05:48
Its good that your problem has been sorted out....but this error can occur only due to 2 reasons - either if trying to insert a row in child table which does not exist in master table OR strict mode is enable & column property is set to not null and we are not skipping that field in our insert statement.... – Zafar Malik Mar 29 '16 at 05:56

score 0 · Answer 2 · answered Mar 28 '16 at 08:05

Try with removing Default NULL value and pass NULL while insert record.

your script shows like...

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);

No need to change in second table.

MySQL Foreign key not allowing insert

2 Answers2