0

I have a table A which has an Auto incrementing Primary key.

I have a table B with a field as foreign key referencing to those Primary key values.

I want to add a record in Table A and Table B simultaneously,(note I'm not defining these auto incrementing fields.When I execute my codes the records in table A are created but table B fails to do so. I understand that I cannot reference a foreign key as a auto incrementing null value but is there any alternative to what I'm trying to achieve???

My code for creating tables are:

Table A

Create TABLE IF NOT EXISTS Friends(
     friend_id Int PRIMARY KEY AUTO_INCREMENT, 
     friend_email Varchar(50) NOT NULL UNIQUE, 
     Password Varchar(20) NOT NULL,
     Profile_Name Varchar(30) NOT NULL,
     Date_Started  DATE NOT NULL,
 Num_Of_Friends INT UNSIGNED )

Table B

Create TABLE IF NOT EXISTS myFriends(
 friend_id1 Int AUTO_INCREMENT , 
 friend_id2 Int NOT NULL,
    FOREIGN KEY(friend_id1) REFERENCES Friends(friend_id))

The queries for adding into these tables

For adding into Table A

INSERT INTO `Friends` (`friend_id`, `friend_email`, `Password`, `Profile_Name`, `Date_Started`, `Num_Of_Friends`) VALUES (NULL, '$Email', '$password1', '$Profile_Name', CURRENT_DATE(), '0')";

For adding into Table B

INSERT INTO `myFriends` (`friend_id1`,`friend_id2`) VALUES (NULL,0)";
Binay Dhawa
  • 71
  • 9
  • 4
    Check out https://stackoverflow.com/questions/3837990/last-insert-id-mysql – Sam M Oct 19 '18 at 05:33
  • 1
    Why do you insert a record that doesn't contain data in table B? – Maksym Fedorov Oct 19 '18 at 05:34
  • Approach is not good, can be done better. If you tell whats the purpose of these two tables. – somsgod Oct 19 '18 at 05:51
  • It's a question for an assignment the question specifies the details so not much I can do with the approach. The first table is to store a list of users who sign up ,the second one stores the number of friends a user has. The I'd of the users is assigned by an auto increasing value. The purpose of second table is for storing number of friends for the users connected through the auto increasing is of table one – Binay Dhawa Oct 19 '18 at 07:05

0 Answers0