MySQL Forums
Forum List  »  General

Re: How to insert rows with foreign key?
Posted by: Peter Brawley
Date: December 12, 2017 12:56PM

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('user01@gmail.com', '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.

Options: ReplyQuote


Subject
Written By
Posted
Re: How to insert rows with foreign key?
December 12, 2017 12:56PM


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.