One-To-Many Relationship (adding data)

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 403,514 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,945 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 597 | Replies: 1 | Solved

•

Join Date: Jul 2008

Posts: 2

Reputation: GFXtm is an unknown quantity at this point

Rep Power: 0

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

(Toggle Plain Text)

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

Help with Code Tags

(Toggle Plain Text)

-----------+-----------+----------
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

(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)
);

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: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

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

(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 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.