ID primary key of ONE to MANY related tables jumping numbers with POSTGRESQL

Question

I am having an issue where the id column of tables users and posts are jumping in increments

users and have many notes ==> one to many relationship

table users

id | email | first_name | last_name | password
====================================
1  |1@a.com| john       |       den | 23ojonknen4
2  |2@a.com| jenn       |       dub | rfknkn4j4r4
5  |3@a.com| jai        |       dan | 9jikddjk4nj

table notes

id | note_name | note_content | user_id
=======================================
3  | name one  | this is yes  | 1
4  | name two  | this is no   | 5

what is happening is the id column of the two tables jump...if there is 3 in id of notes, then next if for users will be 4

how can i fix this issue? or is this normal?

here is the alembic file used to create the schema

from alembic import op
import sqlalchemy as sa


revision = 'master'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'users',
        
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("email", sa.String(254), nullable=False, index=True, unique=True),
        sa.Column("first_name", sa.String(150), nullable=False),
        sa.Column("last_name", sa.String(150), nullable=False),
        sa.Column("password", sa.String(128), nullable=False),
    )

    op.create_table(
        'notes',
        
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("note_name", sa.String(254)),
        sa.Column("note_content", sa.String(254)),
        sa.Column("user_id", sa.Integer, sa.ForeignKey("users.id")),
    )

def downgrade():
    op.drop_table('users')
    op.drop_table('notes')

and here is what i have in my models.py file for python to interact with the database

from sqlalchemy import Table, Column, Integer, String, Float, Boolean, DateTime, MetaData, Sequence, ForeignKey
from sqlalchemy.orm import relationship

metadata = MetaData()

users = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("email", String(254), nullable=False, index=True, unique=True),
    Column("first_name", String(150), nullable=False),
    Column("last_name", String(150), nullable=False),
    Column("password", String(128), nullable=False),
)


notes = Table(
    "notes", metadata,
    Column("id", Integer, primary_key=True),
    Column("note_name", String(254)),
    Column("note_content", String(254)),
    Column("user_id", Integer, ForeignKey("users.id")),
)

so what did i do wrong and how do i fix the issue?

UPDATE

-# \d

                     List of relations
 Schema |          Name          |   Type   |    Owner     
--------+------------------------+----------+--------------
 public | alembic_version        | table    | db_user
 public | pg_stat_statements     | view     | db_user
 public | notes                  | table    | db_user
 public | notes_id_seq           | sequence | db_user
 public | users                  | table    | db_user
 public | users_id_seq           | sequence | db_user
(6 rows)





-# \d users
                                         Table "public.users"
    Column    |            Type             | Collation | Nullable |               Default                
--------------+-----------------------------+-----------+----------+--------------------------------------
 id           | integer                     |           | not null | nextval('users_id_seq'::regclass)
 email        | character varying(254)      |           | not null | 
 first_name   | character varying(150)      |           | not null | 
 last_name    | character varying(150)      |           | not null | 
 password     | character varying(128)      |           | not null |  
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)             
    "ix_users_email" UNIQUE, btree (email)
Referenced by:
    TABLE "notes" CONSTRAINT "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)




-# \d notes
                                             Table "public.notes"
       Column        |            Type             | Collation | Nullable |                   Default                   
---------------------+-----------------------------+-----------+----------+---------------------------------------------
 id                  | integer                     |           | not null | nextval('notes_id_seq'::regclass)
 note_name           | character varying(254)      |           |          | 
 note_content        | character varying(254)      |           |                   | 
 user_id             | integer                     |           |          | 
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

here is an example query to insert rows into users table

    query = "INSERT INTO users VALUES (nextval('users_id_seq'), :email, :password)"
    return database.execute(query, values={"email": user.email, "password": user.password})

here is an example query to insert rows into notes table

    query = "INSERT INTO notes VALUES (nextval('users_id_seq'), :note_name, :note_content, :user_id)"
    return database.execute(query, values={"note_name": note.name, "note_content": note.content, "user_id": user.id})

could this the issue?

query = "INSERT INTO notes VALUES (nextval('users_id_seq'),
:note_name, :note_content, :user_id)"

change nextval('users_id_seq') to nextval('notes_id_seq')?

Each table has its own sequence generator, so the ids will be generated independently, unless you are setting the ids in your code. How are you creating the rows? A [mre] would be helpful here. (Also, in your code both tables are assigned to the name `users`, but I assume that's a typo?) — snakecharmerb, Aug 09 '21 at 08:04
that was typo...i will post the query to insert user in question now — uberrebu, Aug 09 '21 at 08:10
Why are you using `nextval()` "manually?" Use the type `serial` instead, and Pg will make those `nextval()` calls for you... — Usagi Miyamoto, Aug 09 '21 at 08:23
Also, a `sequence` is not guarantied to have consecutive numbers, there might be holes... (To create a hole, just specify an invalid value for one of the columns, EG NULL for a not null column...) — Usagi Miyamoto, Aug 09 '21 at 08:24
Yes, using the users sequence to generate notes ids is the issue. Postgres will generate the ids automatically, but you need to specify the non-id column names in the insert. Or just use SQLAlchemy's `insert` function instead of hand-crafting your queries. — snakecharmerb, Aug 09 '21 at 08:35

score 0 · Answer 1 · answered Aug 09 '21 at 14:09

0

what is happening is the id column of the two tables jump...if there is 3 in id of notes, then next if for users will be 4

how can i fix this issue? or is this normal?

Yes, it's totally normal. Auto-generated numbers are not strictly consecutive; the engine makes a best effort to make them consecutive, but under conditions of buffering, roll backs, multi-threading, etc. that won't happen in practice.

The key feature you actually need is that those numbers should always be unique. As you can see that conditions is satisfied, and that's all you need to maintain data integrity.

answered Aug 09 '21 at 14:09

The Impaler

45,731
9
39
76

are you saying having the `id` columns of both tables have unique values and jumping based on value in other table is good design? so what happens if i have multiple tables related? have all of them fighting for their turn to count in the `id` column? – uberrebu Aug 09 '21 at 16:09
@uberrebu The `INSERT` on the first table provides the the new id as a "returned value" of the SQL call. Then, you use this value to insert in the second table. This way the foreign key relationship is maintained. – The Impaler Aug 10 '21 at 01:55

score 0 · Accepted Answer · answered Aug 10 '21 at 01:19

Fix was to fix the INSERT query that populates the notes table

FROM

query = "INSERT INTO notes VALUES (nextval('users_id_seq'), :note_name, :note_content, :user_id)"
return database.execute(query, values={"note_name": note.name, "note_content": note.content, "user_id": user.id})

TO

query = "INSERT INTO notes VALUES (nextval('notes_id_seq'), :note_name, :note_content, :user_id)"
return database.execute(query, values={"note_name": note.name, "note_content": note.content, "user_id": user.id})

this solved my problem

ID primary key of ONE to MANY related tables jumping numbers with POSTGRESQL

2 Answers2