SQLTeam.com | Weblogs | Forums

Inserting 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

I'm doing this way because if I want to register company for instance, which has account and address I could create a table for "company" and use the other ones.

So, if I want to register a new user I run three commands of insert.
INSERT INTO account(email, password) VALUES('user01@gmail.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(email='user01@gmail.com' 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?

Use scope_identity() ?

INSERT INTO account(email, password) VALUES('user01@gmail.com', '12345')
SELECT scope_identity() -- Get the ID of the just-inserted record

Or I could change a little bit the tables. I could create a foreign key in address for account id and remove d_id from user table. Then i could make this insertion like this:

  • INSERT INTO account(email, password) VALUES('user01@gmail.com', '12345')

  • INSERT INTO address(city, street, c_id) VALUES('city01', 'street01', SELECT id FROM account WHERE (email='user01@gmail.com' AND password='12345'))

  • INSERT INTO user(name, lastName, c_id) VALUES('username01', 'lastname01', SELECT id FROM account WHERE (email='user01@gmail.com' AND password='12345'))

What do you think? Which one is better?

But the problem is if I want to select a user that lives in a x city. How do I do that?

I don;t think using the email in the WHERE clause is a good idea. Better to use a well-defined single value not a text string.

e.g. if the account table has an id field:

Insert into account...
DECLARE @id int = scope_identity()
insert into address ... values (city, street, @id)
insert into user... -- same idea