1

I am trying to create a record for a user.

Here are the two tables:

CREATE TABLE users
(
  id bigserial NOT NULL,
  email character varying(254) NOT NULL,
  passhash character varying(255) NOT NULL,
  email_verified boolean,
  time_created integer,
  CONSTRAINT users_pkey PRIMARY KEY (id)
)

and

CREATE TABLE user_info
(
  id bigserial NOT NULL,
  user_id integer NOT NULL,
  first_name character varying(255),
  last_name character varying(255),
  CONSTRAINT user_info_pkey PRIMARY KEY (id)
)

Basically when a new user signs up, I need add a record to users and user_info.

Normally I would run all of the queries seperate, something like this:

First insert into users: INSERT INTO users (email, passhash, email_verified, time_created) VALUES (...); Then find the id of the last insert: SELECT id FROM users WHERE email = '$email' AND time_created = '$tm'; And then insert into users using the id I retrieved in the last query: INSERT INTO user_info ...;'

I was wondering if this is an acceptable way to do this, I know there must be a better way.

Bobby W
  • 836
  • 8
  • 22
  • Why two separate tables? If two tables, where is the foreign key? – jarlh Apr 11 '16 at 12:25
  • Two seperate tables because there will be much more information in the future, figured it would be better to have them seperate? Is foreign keys something I should use? Not familiar with them, nor an sql expert. – Bobby W Apr 11 '16 at 12:26
  • A two table design is the best practice if you're expandning. Have you thought about creating a trigger? http://www.techonthenet.com/mysql/triggers/after_insert.php – Petter Pettersson Apr 11 '16 at 12:40

1 Answers1

1

You can use the returning clause:

WITH u as (
      INSERT INTO users(email, passhash, email_verified, time_created)
          VALUES (...)
      RETURNING *
     )
INSERT INTO user_info(userId, . . . )
    SELECT u.id, . . .
    FROM u;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786