Re: SQL question: Insertion with FK constraint

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?

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

Comment on Re: SQL question: Insertion with FK constraint

Replies are listed 'Best First'.
Re^2: SQL question: Insertion with FK constraint by lihao (Monk) on Jun 26, 2008 at 15:14 UTC
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. :-)	[reply]
Re^3: SQL question: Insertion with FK constraint by psini (Deacon) on Jun 26, 2008 at 15:38 UTC
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."	[reply]