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

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","[email protected]","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

CodePudding user response：

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 ;

CodePudding user response：

If you know the user_id that you're selecting from the user table, there's no need for the SELECT at all, just put it in the VALUES list.

INSERT INTO question (question_id,questions,question_text, user_id)
VALUES (1,"What is java","Please Explain in details", 1);

You would use a SELECT if you need to look up the user_id using other columns. In that case, you add the fixed values to the SELECT list.

INSERT INTO question (question_id,questions,question_text, user_id)
SELECT 1,"What is java","Please Explain in details", user_id
FROM users
WHERE username = 'krish';

If you're doing this immediately after creating the user, you can also use the LAST_INSERT_ID() function to get the user_id that was assigned using auto-increment.

INSERT INTO question (question_id,questions,question_text, user_id)
VALUES (1,"What is java","Please Explain in details", LAST_INSERT_ID());