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')
?
Advertisement
Answer
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