This is completely incorrect for any version of MySQL 4+. 3.23 had this problem. It's been fixed.

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

From the docs for 5.0:

The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored by InnoDB. InnoDB only accepts REFERENCES clauses when specified as part of a separate FOREIGN KEY specification.

my %property_id;
sub property_id {
  my ($db, $property) = @_;
  my $pid;
  unless (defined($pid = $property_id{$property}) {
    ($pid) = $db->query(..., $property)->list;
    unless (defined($pid)) {
      $db->query("INSERT ...", $property);
      $pid = $db->last_insert_id();
    }
    $property_id{$property} = $pid;
  }
  return $pid;
}
[download]

When inserting a record that has a property FK, just call the subroutine for its value, and it will get created if necessary.

I do this sort of thing all the time, primarily for bulk-loading data. The method I usually use is to first load the data into a holding table (no keys, so loading is fast & easy):

create table #HOLD (
   ...
   property      varchar(50),
   ...
   record_state  char(1)
)
[download]

I next check constrained columns and do some combination of:

• (a) report new values found in the table,
• (b) mark the records in the holding table that contain unknown values,
• (c) generate new entries in the dictionary table, or
• (d) delete the records containing the unknown values.

-- (a) Report new values
select distinct property 
from #HOLD
where property not in (select property from table2)

-- (b) Mark records containing those new values
update #HOLD set record_state='N'
where property not in (select property from table2)

-- (c) Generate new dictionary items (assumes auto-incrementing ID col
+umn)
insert table2 (property)
select distinct property
from #HOLD
where property not in (select property from table2)

-- (d) Delete records containing an unknown value (assumes you didn't
--     add new dictionary items)
delete #HOLD 
where property not in (select property from table2)
[download]

I can then update the main table(s) without worry:

-- Alter any records needing update
update table1 set
   ....
   property_id = T2.id
   ....
from table1 T1
join #HOLD H on H.PrimaryKeyColumn=T1.PrimaryKeyColumn
join table2 T2 on T2.property = H.property
where T1.property_id is null or T1.property_id != T2.id

-- Insert new records
insert table1 (..., property_id, ...)
select ..., T2.id as property_id, ...
from #HOLD H
where H.PrimaryKeyColumn not in (
   select PrimaryKeyColumn
   from table1
)
[download]

Finally, I can get rid of my holding table.

I don't use MySQL (I'm a Pg fan) so there could be some way but, if so, it is not standard SQL

I think your approach is right (i.e. I don/t know a better way to do it) but I'm wondering about the correctness of the idea behind it: if your table2 has only the column property and it is automatically populated when you insert into table1, what is the need for a separate table?

psini, thank you very much for your reply.. this is actually an example, so there are probably more fields in table2.:)

In fact, even with just two fields, using a separate table sometimes save me lot of disk spaces and time, i.e. if 'property' varchar(20) becomes varchar(250) or text. In fact, I just refactored one of my big table from 4GB to 2.2GB yesterday by such process(normalization). the new table took 4 hours instead of 2 days to build a huge Unique key constraint. :-)

Ah, OK. If data grouped in table2 is significantly complex I'd expect a little performance gain. But with an efficient DBMS I'd believe that the overhead added by joining the tables should lose the gain from memory saving.

BTW, 4 days to build a unique index seems to me really too much! I have a table with about 10 million rows (but only 6 columns, I've to admit) that build its primary key (on two columns) in about an hour. And last time I had to restore it from a backup I grumbled for it was too slow... :)

Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."