Generating an ID from primary key sequence without adding a row

All we need is an easy explanation of the problem, so here it is.

I have the following two tables where I store users and their addresses. I have a problem relating to the order in which I receive and can save data from the user. At first the user will supply only the address and then get directed to an external service. To open this service I need to supply my internal user ID. The service verifies their email address and sends me the response.

CREATE TABLE IF NOT EXISTS public."user"
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
    CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
)

CREATE TABLE IF NOT EXISTS public.adresses
(
    id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
    address character varying COLLATE pg_catalog."default" NOT NULL,
    "userId" integer,
    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
  • Can I generate the user ID from the sequence in some other way than entering an email in to the table and in the end enter both the ID and the email at the same time?
  • Can I save the address in the addresses table referencing a foreign key which has not yet been added to users?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

To answer this question, I did the following (all of the code below is available on the fiddle here):

CREATE SEQUENCE user_id_seq;
CREATE SEQUENCE address_id_seq;

and the tables:

CREATE TABLE user_
(
    id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    email character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
    CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
);

and

CREATE TABLE address
(
    id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
    address character varying COLLATE pg_catalog."default" NOT NULL,
    user_id integer,
    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY (user_id)
        REFERENCES user_ (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

We use PostgreSQL’s RETURNING clause with a Common Table Expression (CTE – aka the WTIH clause).

A simple example:

WITH insert_user AS
(
  INSERT INTO user_ (email) VALUES ('[email protected]')
  RETURNING id
)
SELECT *  FROM insert_user;

Result:

id  email
1   [email protected]

So, we can see that we can obtain the id from the INSERT statement withing the CTE.

Now, we take this a step further as follows:

BEGIN TRANSACTION;
WITH insert_user AS
(
  INSERT INTO user_ (email) VALUES ('[email protected]')
  RETURNING id
)
INSERT INTO address (address, user_id)
SELECT '1, Long Street, Somewhere', (SELECT id FROM insert_user)
COMMIT;

We use a transaction – just in case there’s a problem in the step from the first INSERT to the second.

SELECT * FROM user_;
and
SELECT * FROM address;

Results:

id  email
2   [email protected]
and
id         address            user_id
1   1, Long Street, Somewhere       2

We see that the user_id of 2 is in the address table – having come from the INSERT into the user_ table in the CTE.

You have to INSERT a value for the email of the user because of the NOT NULL constraint in the user_ table definition! You can remove the constraint, but I see little point in this.

A few points to note:

  • you use CHARACTER VARYING – PostgreSQL’s TEXT data type is better suited for this.

  • COLLATE pg_catalog."default" is redundant – if not specified, the collation will be the default.

  • you use quoted identifiers, i.e. "userId" – far better to use PostgreSQL’s recommended naming convention and turn this into user_id – i.e. snake_case!

  • you have the most bizarre names for constraints that I have ever seen!

    CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),

    and

    CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY ("userId")... REFERENCES...

    The only rationale for this is that it’s taken from some other system that gives these horrible names. Far better to have address_pk_id or something similar (i.e. meaningful). An error message stating that constraint "PK_bec464dd8d54c39c54fd32e2334" has been violated isn’t going to tell anyone much! Plus, transmitting that over a phone call is going to be difficult – much better to use a simple name to which users can relate.

To answer the questions:

1st question:

  • Can I generate the user ID from the sequence in some other way than entering an email in to the table and in the end enter both the ID and the email at the same time?

You could do the following:

SELECT nextval('user_id_seq');

Result:

nextval
      3

and then INSERT that into the address table along with an address. But, what’s the point? You have to have some way of relating a given user to a given address – and you do this by wrapping your SEQUENCE value within a transaction – first from the INSERT into the user_ table and then using that value from the RETURNING clause to create the address.

2nd question:

  • Can I save the address in the addresses table referencing a foreign key which has not yet been added to users?

No! The whole point of FOREIGN KEYs is so that you can’t insert anything into the address table that doens’t have a valid user_id back in the id field of the user_ table.

You could drop the constraints and have some ghastly procedural mess to keep track of what had and hadn’t been INSERTed where and when – but again, why? You database will do all the work of keeping track of all this if you let it!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply