One-To-Many Relationship (adding data)

[GFXtm]

I have the following table:

my_contacts
which contains the following columns:

contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
frist_name VARCHAR(20),
interest VARCHAR(20)

-----------+-----------+----------
contact_id | fisrt_name | interest +
----------------------------------
1              | smith        | swim     +
2              | mark         | dance   +
----------------------------------

I need to move the interests column and make it into a CHILD table:

Here's the code to do that:

CREATE TABLE interests 
(
  int_id INT NOT NULL AUTO_INCREMENT PRIMARY_KEY,
  interest VARCHAR(20) NOT NULL,
  contact_id INT NOT NULL,
  CONSTRAINT my_contacts_contact_id_fk
  FOREIGN KEY (contact_id)
  REFERENCES my_contacts (contact_id)
);

Since I can't add information to the child table because of CONSTRAINT how am I goona move the interests from my_contacts to my new table interests so they have a one-to-many relationship ???

[tesuji]

Hi

There is a function LAST_INSERT_ID() what returns the last-created auto-increment value. So first insert parent data what creates a new auto-increment ID. Then insert row in child table where LAST_INSERT_ID() function is put in the values list of the insert statement, for example:

-- first insert parent row
insert into my_contacts (first_name) values ('GFXtm')
-- this generates a new value for auto-increment contactID

-- Now insert child row
insert into interests (interest, contactID) values ('databasing',  LAST_INSERT_ID())
-- this will assign the just generated auto-increment to foreign key contactID

First time, there is no interference between the auto-increment values of both tables: In Mysql all values of the values list will be inserted first. Finally the new auto-increment of table interests will be generated which then become last-inserted ID. That also means that you cannot use above insert statement, if you want to insert multiple children to same parent. For this case you must store the LAST_INSERT_ID() value from parent table into a variable.

krs,
tesu

[Asish_2]

this is not work....if you add auto increment in primary key.
#1452 - Cannot add or update a child row: a foreign key constraint fails .