How to insert rows with foreign key?
Hi! I'm newbie in SQL and for porpouse of leanging I want to create a system to register users.
I made these tables:
account(id, email, password)
address(id, city, street, number, phone)
user(id, name, lastName, c_id, d_id)
c_id = foreign key for account
d_id = foreign key for address
So, if I want to register a new user I run three commands of insert.
INSERT INTO account(email, password) VALUES('email@example.com', '12345')
INSERT INTO address(city, street) VALUES('city01', 'street01')
And here is the problem. For c_id I know how to get the id.
INSERT INTO user(name, lastName, c_id, d_id) VALUES('user01', 'lastname01', SELECT id FROM account WHERE(firstname.lastname@example.org' AND password='12345'), ?)
But how can I get the id from address considering the user doesn't type all fields only the mandatory(city, and street)?
Maybe my table scheme is not right. Could you make it better?