One-To-Many Relationship (adding data)

Expand Post »

I have the following table:

my_contacts
which contains the following columns:

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)

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:

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

Similar Threads

Master:Detail Views? in C#
POpulate DataGrid in VB.NET

GFXtm

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jul 2008

Permalink

Jul 13th, 2008

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

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:

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: IF THEN statement would not work

Next Thread in MySQL Forum Timeline: SHA1 encription

DaniWeb Message

Cancel Changes

User Name
Password