Идентификатор первичного ключа от ОДНОЙ до МНОГИХ связанных таблиц, перескакивающих номера с помощью POSTGRESQL

avatar
uberrebu
9 августа 2021 в 02:49
161
2
0

У меня возникла проблема, когда столбец id таблиц users и posts прыгает с шагом

users и имеют много notes ==> отношение один ко многим

стол 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

стол notes

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

происходит скачок столбца id двух таблиц...если в id ноты 3, то дальше если для пользователей будет 4

как решить эту проблему? или это нормально?

вот файл перегонного куба, используемый для создания схемы

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')

и вот что у меня есть в моем файле models.py для взаимодействия Python с базой данных

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")),
)

итак, что я сделал не так и как мне решить проблему?

ОБНОВЛЕНИЕ

-# \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)



вот пример запроса для вставки строк в users таблицу

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

вот пример запроса для вставки строк в таблицу notes

    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})

Может ли это быть проблемой?

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

изменить nextval('users_id_seq') на nextval('notes_id_seq')?

Источник
snakecharmerb
9 августа 2021 в 08:04
1

Каждая таблица имеет свой собственный генератор последовательности, поэтому идентификаторы будут генерироваться независимо, если вы не устанавливаете идентификаторы в своем коде. Как вы создаете ряды? Здесь был бы полезен минимальный воспроизводимый пример. (Кроме того, в вашем коде обеим таблицам присвоено имя users, но я предполагаю, что это опечатка?)

uberrebu
9 августа 2021 в 08:10
1

это была опечатка... сейчас я опубликую запрос, чтобы вставить пользователя, о котором идет речь

Usagi Miyamoto
9 августа 2021 в 08:23
2

Почему вы используете nextval() "вручную"? Вместо этого используйте тип serial, и Pg сделает за вас вызовы nextval()...

Usagi Miyamoto
9 августа 2021 в 08:24
1

Кроме того, sequence не гарантирует наличие последовательных номеров, могут быть дыры... (Чтобы создать дыру, просто укажите недопустимое значение для одного из столбцов, например, NULL для ненулевого столбца...)

snakecharmerb
9 августа 2021 в 08:35
1

Да, проблема заключается в использовании последовательности пользователей для создания идентификаторов заметок. Postgres автоматически сгенерирует идентификаторы, но вам нужно указать имена столбцов без идентификаторов во вставке. Или просто используйте функцию SQLAlchemy insert вместо создания запросов вручную.

Ответы (2)

avatar
uberrebu
10 августа 2021 в 01:19
0

Исправление заключалось в исправлении запроса INSERT, заполняющего таблицу заметок

.

ОТ

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})

ТО

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})

это решило мою проблему

avatar
The Impaler
9 августа 2021 в 14:09
0

происходит скачок столбца id двух таблиц...если в id заметок 3, то дальше если для пользователей будет 4

как решить эту проблему? или это нормально?

Да, это совершенно нормально. Автоматически сгенерированные числа не являются строго последовательными; движок прилагает максимальные усилия, чтобы сделать их последовательными, но в условиях буферизации, откатов, многопоточности и т. д. на практике этого не происходит.

Ключевой функцией, которая вам действительно нужна, является то, что эти номера всегда должны быть уникальными. Как видите, условия выполнены, и это все, что вам нужно для поддержания целостности данных.

uberrebu
9 августа 2021 в 16:09
0

Вы говорите, что столбцы id обеих таблиц имеют уникальные значения, а переход на основе значения в другой таблице - хороший дизайн? так что произойдет, если у меня есть несколько связанных таблиц? все ли они борются за свою очередь засчитываться в столбце id?

The Impaler
10 августа 2021 в 01:55
0

@uberrebu INSERT в первой таблице предоставляет новый идентификатор как «возвращаемое значение» вызова SQL. Затем вы используете это значение для вставки во вторую таблицу. Таким образом, связь внешнего ключа сохраняется.