Re: How to insert rows with foreign key?

[Peter Brawley]

Have a look at the manual page for last_insert_id().

It's problematic to form a PK from email address and/or passwords. They both tend to change. PKs ought not to require edits. So these tables are ideal for surrogate PKs---auto_increment PKs. Then after they're added ...

INSERT INTO account(email, password) VALUES('[e-mail]', '12345');
set @acctid = last_insert_id();

INSERT INTO address(city, street) VALUES('city01', 'street01');
set @addrid = last_insert_id();

... and assuming user.id is the user auto_indrement PK, you can write ...

insert into user set name-'itisI', lastname='Smith', c_id=@acctid, d_id=@addrid);

BTW, if your db us ever ported to Linux, mixed case identifier names may wreak havoc, best stick to lower case in identifier names.