CODEWITHSUNDEEP
mysqlsqlforeign-keysprimary-keymysql-error-1364
Sebastian Przybylski
Sebastian Przybylski

Reputation: 153

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;

Upvotes: 0

Views: 87

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The id for continued needs to be the last inserted id for practice. Assuming you want one hobby per practice, I would recommend:

INSERT INTO practice (user_name, user_real_name)
    VALUES('KittenKing', 'Henry');

INSERT INTO continued (continued_id, hobby)
    VALUES(LAST_INSERT_ID(), 'Tennis');

INSERT INTO practice(user_name, user_real_name)
    VALUES('DogDictator', 'Mary');

INSERT INTO continued (hobby)
    VALUES (LAST_INSERT_ID(), 'Hockey');

INSERT INTO practice(user_name ,user_real_name)
    VALUES('HamsterHam', 'Denver');

INSERT INTO continued (continued_id, hobby)
    VALUES (LAST_INSERT_ID(), 'Spear Hunting');

Upvotes: 0

Nick
Nick

Reputation: 147146

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.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

your table continued has foreign key continued_id in table practice, so this field is mandatory. 

INSERT INTO continued(continued_id, hobby)
VALUES(1, "Tennis");
INSERT INTO continued(continued_id, hobby)
VALUES(2, "Hockey");
INSERT INTO continued(continued_id, hobby)
VALUES(3, "Spear Hunting");

Upvotes: 1

Related Questions