MySQL

One-To-Many Relationship (adding data)

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jul 2008

Posts: 2

Reputation: GFXtm is an unknown quantity at this point

Solved Threads: 0

GFXtm

Offline

Newbie Poster

One-To-Many Relationship (adding data)

Jul 10th, 2008

I have the following table:

my_contacts
which contains the following columns:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
frist_name VARCHAR(20),
interest VARCHAR(20)
contact_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
frist_name VARCHAR(20),
interest VARCHAR(20)

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
-----------+-----------+----------
contact_id | fisrt_name | interest +
----------------------------------
1              | smith        | swim     +
2              | mark         | dance   +
----------------------------------
-----------+-----------+----------
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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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)
);
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 ???

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: One-To-Many Relationship (adding data)

Jul 13th, 2008

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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
-- 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 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

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.