mysql - id值是否需要根据错误1364的默认值？

我想创建两个表。 practice具有AUTO_INCREMENT附件，并且是PRIMARY KEY。 continued具有ID实体continued_id，该实体作为引用FOREIGN KEY的practice(user_id)存在。 Mysql将执行下面的代码，直到第19行，我收到1364错误，并指出continued_id没有默认值。

我很困惑。我认为自动递增的user_id（即PK）的定义值为1,2,3 ...我认为continued_id等效于user_id，因此其默认值为1 ？也许我误会了PK和FK在sql中实际上如何工作？

错误：

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;

最佳答案

您在continued中的插入内容需要链接到practice中的条目。您可以通过将practice插入到continued中，然后将LAST_INSERT_ID()用于continued_id插入到practice中来执行此操作：

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

或通过使用INSERT ... SELECT查询引用continued_id中的适当条目：

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

要么

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

Demo on dbfiddle

请注意，您无需将AUTO_INCREMENT声明为。

Practice

mysql - id值是否需要根据错误1364的默认值？