Skip to content
Advertisement

id value needs a default value according to error 1364?

I want to create two tables. practice has a AUTO_INCREMENT attachment and is a PRIMARY KEY. continued has the id entity continued_id which exists as a FOREIGN KEY that references practice(user_id). Mysql executes the code below fine until line 19, where I receive the 1364 error, stating that continued_id has no default value.

I am confused. I thought that user_id, which auto_increments, and it being the PK, would have a defining value of 1,2,3… I thought that continued_id is equivalent to user_id, and therefore its default value is 1? Perhaps I am misunderstanding how PK’s and FK’s actually work in sql?

Error:

20:03:02    INSERT INTO continued(hobby) VALUES("Tennis")   Error Code: 1364. Field 'continued_id' doesn't have a default value 0.000 sec
CREATE TABLE practice(
    user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    user_name VARCHAR(60) NOT NULL,
    user_real_name VARCHAR(60) NOT NULL
);
CREATE TABLE continued(
    continued_id INT NOT NULL,
    FOREIGN KEY(continued_id)REFERENCES practice(user_id),
    hobby VARCHAR(25) NOT NULL
);
INSERT INTO practice(user_name,user_real_name)
VALUES("KittenKing","Henry");
INSERT INTO practice(user_name,user_real_name)
VALUES("DogDictator","Mary");
INSERT INTO practice(user_name,user_real_name)
VALUES("HamsterHam","Denver");

INSERT INTO continued(hobby)
VALUES("Tennis");
INSERT INTO continued(hobby)
VALUES("Hockey");
INSERT INTO continued(hobby)
VALUES("Spear Hunting");

SELECT * FROM practice,continued;

Advertisement

Answer

Your inserts into continued need to be linked to an entry in practice. You can either do that by immediately following the insert into practice with an insert into continued using LAST_INSERT_ID() for continued_id:

INSERT INTO practice (user_name,user_real_name)
VALUES("KittenKing","Henry");
INSERT INTO continued (continued_id, hobby)
VALUES(LAST_INSERT_ID(), 'Tennis')

or by referring to the appropriate entry in practice using an INSERT ... SELECT query:

INSERT INTO continued (continued_id, hobby)
SELECT user_id, 'Hockey'
FROM practice 
WHERE user_real_name = 'Mary'

or

INSERT INTO continued (continued_id, hobby)
SELECT user_id, 'Spear Hunting'
FROM practice 
WHERE user_name = 'HamsterHam'

Demo on dbfiddle

Note that you do not need to declare continued_id as AUTO_INCREMENT.

9 People found this is helpful
Advertisement