Trouble getting SQL foreign key as it says ERROR 1364 "Field doesn't have default value&qu-CodePudding

Student new to SQL. I understand similar question has been asked multiple times on here but i can not seem to figure out where the issue is. I was making a basic project that logs blood pressure. My thinking was that the following code would initialize the schema of a user table (to track who is logged in) and a readings table (blood pressure readings):

CREATE DATABASE IF NOT EXISTS bloodpressure;

USE bloodpressure;

CREATE TABLE IF NOT EXISTS appusers (
    userid INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    username VARCHAR(25) UNIQUE NOT NULL,
    psword VARCHAR(25) NOT NULL
); 

CREATE TABLE IF NOT EXISTS bpreadings (
    reading INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    user_id INT NOT NULL,
    sys DECIMAL(3 , 0 ) NOT NULL,
    dia DECIMAL(3 , 0 ) NOT NULL,
    pulse DECIMAL(3 , 0 ) NOT NULL,
    FOREIGN KEY (user_id)
        REFERENCES appusers (userid)
);

The issue i'm running into to is the error code:

Error Code: 1364. Field 'user_id' doesnt have a default value

This is after I run:

INSERT INTO appusers (username, psword) VALUES ("user1", "pwd1");

which runs correctly and then try to run:

INSERT INTO bpreadings (sys, dia, pulse) VALUES (128, 72, 88);

my original thinking was to use a FK to fill it from the userid in appusers but it does not carry over. I wouldnt think I would need a default value on readings.user_id. I also checked inserting appusers with data and the primary key userid fills fine and autoincrements correctly. I'm just struggling trying to get it to carry over. Is it something simple i am missing or is it an overall flaw in the design?

Thank you in advance!

CodePudding user response：

It is because you declared user_id as NOT NULL attribute which means it cannot be empty, you need to change your query into something like this:

INSERT INTO bpreadings (user_id, sys, dia, pulse) VALUES (1, 128, 72, 88);

The alternative is to remove your "NOT NULL" condition on table creation which gonna be looks like this:

CREATE TABLE IF NOT EXISTS bpreadings (
reading INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id INT,
sys DECIMAL(3 , 0 ) NOT NULL,
dia DECIMAL(3 , 0 ) NOT NULL,
pulse DECIMAL(3 , 0 ) NOT NULL,
FOREIGN KEY (user_id)
    REFERENCES appusers (userid)

);