How to add or insert foreign key value in child table from parent table using insert statement

I want to use the primary key of parent table in my child tables. I have created a foreign key to connect QUESTION and ANSWER table with each other. below is code of my database:

user table

CREATE TABLE user (
  user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- unique index
  username VARCHAR(255) NOT NULL, 
  password VARBINARY(255) NOT NULL,                -- password hash, binary
  firstname VARCHAR(255) NOT NULL,
  lastname VARCHAR(255) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  phone BIGINT DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE question (
   question_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique index
   questions TEXT NOT NULL,    
   question_text TEXT NOT NULL,        
   user_id INT NOT NULL,                                 -- id of the user who have asked
   CONSTRAINT user_to_question FOREIGN KEY (user_id) REFERENCES user (user_id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 CREATE TABLE answer (
  answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique index
  answer_text TEXT NOT NULL,
  question_id INT NOT NULL,                           -- what question it answers on
  user_id INT NOT NULL,                               -- id of the user who have answered
  CONSTRAINT user_to_answer FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT question_to_answer FOREIGN KEY (question_id) REFERENCES question (question_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

So, In USER table I have added a new value:

  insert into user values(1,"krish","123456789","Krish","D","[e-mail]","Mumbai MH","98776");

Now in QUESTION table I want to add a new value and I want to use the user_id value from USER table. Since user_id is foreign key in QUESTION table. So, far i have tried this query but its not working.

 insert into question (question_id,questions,question_text)
 SELECT user_id
 FROM user
 where user_id = 1;
 values (1,"What is java","Please Explain in details");

I have referred this question and solution but its not working. inserting data from parent table to child table in postgres

Answer

The query you are trying is something like:

insert into question ( question_id,
                       questions,
                       question_text
                     )
SELECT user_id as question_id,
       "What is java" as questions,
       "Please Explain in details" as question_text
FROM user
where user_id = 1 ;

But as far as I can see you cant insert only this values because on

CREATE TABLE answer (
  answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  answer_text TEXT NOT NULL,
  question_id INT NOT NULL,                           
  user_id INT NOT NULL,  

you have user_id INT NOT NULL so it expects a value.

Something like :

insert into question ( question_id,
                       questions,
                       question_text,
                       user_id
                      )
SELECT user_id as question_id,
       "What is java" as questions,
       "Please Explain in details" as question_text,
       user_id
FROM user
where user_id = 1 ;