MySQL Forums
Forum List  »  Newbie

non-redundant row inserts and foreign keys
Posted by: Nathan Edwards
Date: June 29, 2005 08:25AM

Given the following tables:

create table A (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
data int(10),
PRIMARY KEY (id)
)

create table B (
Bid int(10) unsigned NOT NULL,
Aid int(10) unsigned NOT NULL
)

where A stores expensive to compute and store items, and B defines sets of these items.

I'm creating sets of the data items dynamically, so something like:

insert into A values (NULL,1)
insert into B values (1,LAST_INSERT_ID())
insert into A values (NULL,2)
insert into B values (1,LAST_INSERT_ID())
insert into A values (NULL,3)
insert into B values (1,LAST_INSERT_ID())

and then, at some later time,

insert into A values (NULL,1)
insert into B values (2,LAST_INSERT_ID())
insert into A values (NULL,3)
insert into B values (2,LAST_INSERT_ID())
insert into A values (NULL,5)
insert into B values (2,LAST_INSERT_ID())

...representing the sets {1,2,3} and {1,3,5}.

Unfortunately, A now has 6 rows:

A:
1, 1
2, 2
3, 3
4, 1
5, 3
6, 5

I want some way to manage A so that I don't have to do multiple statements to discover whether data is already present or not, and to get its id, so that the tables look like this:

A:
1, 1
2, 2
3, 3
4, 5

B:
1, 1
1, 2
1, 3
2, 1
2, 3
2, 4

I'm using MySQL 4.1.

This can't be that hard! What am I missing?

Thanks,

nathan

Options: ReplyQuote


Subject
Written By
Posted
non-redundant row inserts and foreign keys
June 29, 2005 08:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.