non-redundant row inserts and foreign keys
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