Programming errors when creating db tables in Postgresql with SQLAlchemy

Question

I have a problem creating tables with SQLAlchemy in PostgreSQL.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "person". [SQL: '\nCREATE TABLE signer (\n\tid INTEGER NOT NULL, \n\tdecree VARCHAR(120), \n\tjob_title VARCHAR(120), \n\tdate_duty_start TIMESTAMP WITHOUT TIME ZONE, \n\tdate_duty_end TIMESTAMP WITHOUT TIME ZONE, \n\tperson_id INTEGER NOT NULL, \n\tcompany_id INTEGER NOT NULL, \n\tsigner_id INTEGER NOT NULL, \n\tcompany_ids INTEGER, \n\tperson_ids INTEGER, \n\tPRIMARY KEY (id, signer_id), \n\tFOREIGN KEY(person_id) REFERENCES person (id), \n\tFOREIGN KEY(company_id) REFERENCES company (id), \n\tFOREIGN KEY(company_ids) REFERENCES company (company_id), \n\tFOREIGN KEY(person_ids) REFERENCES person (person_id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)

I'm trying to create a new database, so deleting all the tables does not solve the problem. Also, I cannot understand why there's no problem with creating dependencies between the company and signers tables while there is a problem with person-signers relationship....

My classes look as follows:

class Person(db.Model):
    __table_args__ = {'extend_existing': True} 
    def __init__ (self, *args, **kwargs):
        [[setattr(self, key, dict[key]) for key in dict if any(key == t for t in Person.__dict__)] for dict in args]

    person_id = db.Column(db.Integer, primary_key = True)
    first_name = db.Column(db.String(30), nullable=False)
    middle_name = db.Column(db.String(40), nullable=False)
    last_name = db.Column(db.String(60), nullable=False)
    email = db.Column(db.String(120))
    license = db.Column(db.String(120))
    address = db.Column(db.String(240))
    telephone = db.Column(db.String(30))

    #o2o
    user_id = db.Column(db.Integer, db.ForeignKey('usersd.user_id'))

    #o2m
    signers = db.relationship('Signer', backref='person_data', lazy='jioned')

    def __repr__(self):
        return f"{self.last_name.Capitalize} {self.first_name[0].Upper}. {self.middle_name[0].Upper}."

class Signer(db.Model):
    __table_args__ = {'extend_existing': True} 

    def __init__ (self, *args, **kwargs):
        [[setattr(self, key, dict[key]) for key in dict if any(key == t for t in Signer.__dict__)] for dict in args]

    signer_id = db.Column(db.Integer, primary_key = True)
    decree = db.Column(db.String(120))
    job_title = db.Column(db.String(120))
    date_duty_start = db.Column(db.DateTime)
    date_duty_end = db.Column(db.DateTime)
    #o2m

    company_ids = db.Column(db.Integer, db.ForeignKey('company.company_id'))
    person_ids = db.Column(db.Integer, db.ForeignKey('person.person_id'))
    #m2o

    def __repr__(self):
        return f"{self.job_title} at {self.company} according to {self.decree}."

class Company(db.Model):
    __table_args__ = {'extend_existing': True} 

    def __init__ (self, *args, **kwargs):
        [[setattr(self, key, dict[key]) for key in dict if any(key == t for t in Company.__dict__)] for dict in args]

    company_id = db.Column(db.Integer, primary_key = True)
    company_name = db.Column(db.String(60))
    full_title = db.Column(db.String(240))
    tin = db.Column(db.BigInteger)
    kpp = db.Column(db.Integer)
    ogrn = db.Column(db.BigInteger)
    email = db.Column(db.String(120))
    address = db.Column(db.String(240))
    telephone = db.Column(db.String(60))

    license_number = db.Column(db.String(40))
    license_date_issued = db.Column(db.DateTime) 
    license_category = db.Column(db.String(120))
    license_issued_by = db.Column(db.String(120))
    license_issued_by_tin = db.Column(db.BigInteger)
    license_issued_by_kpp = db.Column(db.Integer)
    license_issued_by_ogrn = db.Column(db.BigInteger)

    #o2m
    signers = db.relationship('Signer', backref='company', lazy='joined')


    def __repr__(self):
        return f"{self.company_name}"

----------------------
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import LoginManager
import sys
import locale
import datetime





app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:XXXXXXXXXXXXX@localhost/aosr_flask_0_1'
db = SQLAlchemy(app)
bcrypt = Bcrypt(app)
login_manager = LoginManager(app)
login_manager.login_view = 'login'
login_manager.login_message_category = 'info'


------
from test_models import *
db.create_all()

I think the problem came up because I was trying to avoid columns named "id" for each class. Although I defined primary keys in every class as person_id, signer_id, company_id, etc. in my Postgres DB, there are also primary key columns "id" in each table. According to SQL given in Error, SQL Alchemy tries to create two constraints for each case... which makes it not unique.

So my question comes down to how to make SQLAlchemy not to create primary key columns (ID) on its own, when not specified.

I am trying to avoid columns named ID in the database to have fewer problems with using these objects in HTML later.

Answer 1

As far as I see, your problem tells you didn't define a unique option for your primary key field. Your person_id should have unique=True. It will guarantee that this field willn't have repeated Ids.

person_id = db.Column(db.Integer, primary_key = True, unique=True)