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.
