python 3.x – ‘there is no unique constraint’ error in SQLAlchemy when setting ForeignKeys

I’m trying to make some foreign keys in SQLAlchemy but I’m not sure if I understand how to do it correctly. I want to set p1 in Text_messages to equal p1 in Customer_Information

import sqlalchemy
import psycopg2
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/Database')

conn_string = "host="localhost" dbname="Database" user="postgres" password='postgres'"
conn = psycopg2.connect(conn_string)


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://postgres:postgres@localhost:5432/Database'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False #to supress warnings
db = SQLAlchemy(app)

class Customer_information(db.Model):
    __tablename__ = 'Customer_information'
    account_id = db.Column(db.Integer, primary_key = True)
    p1 = db.Column(db.Integer, primary_key = True)
    text = db.relationship("Text_messages", back_populates = "Customer_information")
    
class Text_messages(db.Model):
    __tablename__ = 'Text_messages'
    phone_number = db.Column(db.Integer, primary_key = True)
    p1 = db.Column(db.Integer, db.ForeignKey('Customer_information.p1'))
    customer = db.relationship("Customer_information", back_populates = "Text_messages")
    
db.create_all()
# db.drop_all()

but I get this error

    ---------------------------------------------------------------------------
InvalidForeignKey                         Traceback (most recent call last)
File ~anaconda3libsite-packagessqlalchemyenginebase.py:1808, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1807     if not evt_handled:
-> 1808         self.dialect.do_execute(
   1809             cursor, statement, parameters, context
   1810         )
   1812 if self._has_events or self.engine._has_events:

File ~anaconda3libsite-packagessqlalchemyenginedefault.py:732, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    731 def do_execute(self, cursor, statement, parameters, context=None):
--> 732     cursor.execute(statement, parameters)

InvalidForeignKey: there is no unique constraint matching given keys for referenced table "Customer_information"


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
Input In [7], in <cell line: 33>()
     30     p1 = db.Column(db.Integer, db.ForeignKey('Customer_information.p1'))
     31     customer = db.relationship("Customer_information", back_populates = "Text_messages")
---> 33 db.create_all()

File ~anaconda3libsite-packagesflask_sqlalchemy__init__.py:1094, in SQLAlchemy.create_all(self, bind, app)
   1088 def create_all(self, bind='__all__', app=None):
   1089     """Creates all tables.
   1090 
   1091     .. versionchanged:: 0.12
   1092        Parameters were added
   1093     """
-> 1094     self._execute_for_all_tables(app, bind, 'create_all')

File ~anaconda3libsite-packagesflask_sqlalchemy__init__.py:1086, in SQLAlchemy._execute_for_all_tables(self, app, bind, operation, skip_tables)
   1084     extra['tables'] = tables
   1085 op = getattr(self.Model.metadata, operation)
-> 1086 op(bind=self.get_engine(app, bind), **extra)

File ~anaconda3libsite-packagessqlalchemysqlschema.py:4864, in MetaData.create_all(self, bind, tables, checkfirst)
   4862 if bind is None:
   4863     bind = _bind_or_error(self)
-> 4864 bind._run_ddl_visitor(
   4865     ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
   4866 )

File ~anaconda3libsite-packagessqlalchemyenginebase.py:3123, in Engine._run_ddl_visitor(self, visitorcallable, element, **kwargs)
   3121 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
   3122     with self.begin() as conn:
-> 3123         conn._run_ddl_visitor(visitorcallable, element, **kwargs)

File ~anaconda3libsite-packagessqlalchemyenginebase.py:2119, in Connection._run_ddl_visitor(self, visitorcallable, element, **kwargs)
   2112 def _run_ddl_visitor(self, visitorcallable, element, **kwargs):
   2113     """run a DDL visitor.
   2114 
   2115     This method is only here so that the MockConnection can change the
   2116     options given to the visitor so that "checkfirst" is skipped.
   2117 
   2118     """
-> 2119     visitorcallable(self.dialect, self, **kwargs).traverse_single(element)

File ~anaconda3libsite-packagessqlalchemysqlvisitors.py:524, in ExternalTraversal.traverse_single(self, obj, **kw)
    522 meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
    523 if meth:
--> 524     return meth(obj, **kw)

File ~anaconda3libsite-packagessqlalchemysqlddl.py:849, in SchemaGenerator.visit_metadata(self, metadata)
    847 for table, fkcs in collection:
    848     if table is not None:
--> 849         self.traverse_single(
    850             table,
    851             create_ok=True,
    852             include_foreign_key_constraints=fkcs,
    853             _is_metadata_operation=True,
    854         )
    855     else:
    856         for fkc in fkcs:

File ~anaconda3libsite-packagessqlalchemysqlvisitors.py:524, in ExternalTraversal.traverse_single(self, obj, **kw)
    522 meth = getattr(v, "visit_%s" % obj.__visit_name__, None)
    523 if meth:
--> 524     return meth(obj, **kw)

File ~anaconda3libsite-packagessqlalchemysqlddl.py:893, in SchemaGenerator.visit_table(self, table, create_ok, include_foreign_key_constraints, _is_metadata_operation)
    889 if not self.dialect.supports_alter:
    890     # e.g., don't omit any foreign key constraints
    891     include_foreign_key_constraints = None
--> 893 self.connection.execute(
    894     # fmt: off
    895     CreateTable(
    896         table,
    897         include_foreign_key_constraints=  # noqa
    898             include_foreign_key_constraints,  # noqa
    899     )
    900     # fmt: on
    901 )
    903 if hasattr(table, "indexes"):
    904     for index in table.indexes:

File ~anaconda3libsite-packagessqlalchemyenginebase.py:1295, in Connection.execute(self, statement, *multiparams, **params)
   1291     util.raise_(
   1292         exc.ObjectNotExecutableError(statement), replace_context=err
   1293     )
   1294 else:
-> 1295     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File ~anaconda3libsite-packagessqlalchemysqlddl.py:80, in DDLElement._execute_on_connection(self, connection, multiparams, params, execution_options)
     77 def _execute_on_connection(
     78     self, connection, multiparams, params, execution_options
     79 ):
---> 80     return connection._execute_ddl(
     81         self, multiparams, params, execution_options
     82     )

File ~anaconda3libsite-packagessqlalchemyenginebase.py:1387, in Connection._execute_ddl(self, ddl, multiparams, params, execution_options)
   1382 dialect = self.dialect
   1384 compiled = ddl.compile(
   1385     dialect=dialect, schema_translate_map=schema_translate_map
   1386 )
-> 1387 ret = self._execute_context(
   1388     dialect,
   1389     dialect.execution_ctx_cls._init_ddl,
   1390     compiled,
   1391     None,
   1392     execution_options,
   1393     compiled,
   1394 )
   1395 if self._has_events or self.engine._has_events:
   1396     self.dispatch.after_execute(
   1397         self,
   1398         ddl,
   (...)
   1402         ret,
   1403     )

File ~anaconda3libsite-packagessqlalchemyenginebase.py:1851, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1848             branched.close()
   1850 except BaseException as e:
-> 1851     self._handle_dbapi_exception(
   1852         e, statement, parameters, cursor, context
   1853     )
   1855 return result

File ~anaconda3libsite-packagessqlalchemyenginebase.py:2032, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2030     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2031 elif should_wrap:
-> 2032     util.raise_(
   2033         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2034     )
   2035 else:
   2036     util.raise_(exc_info[1], with_traceback=exc_info[2])

File ~anaconda3libsite-packagessqlalchemyutilcompat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~anaconda3libsite-packagessqlalchemyenginebase.py:1808, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1806                 break
   1807     if not evt_handled:
-> 1808         self.dialect.do_execute(
   1809             cursor, statement, parameters, context
   1810         )
   1812 if self._has_events or self.engine._has_events:
   1813     self.dispatch.after_cursor_execute(
   1814         self,
   1815         cursor,
   (...)
   1819         context.executemany,
   1820     )

File ~anaconda3libsite-packagessqlalchemyenginedefault.py:732, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    731 def do_execute(self, cursor, statement, parameters, context=None):
--> 732     cursor.execute(statement, parameters)

ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "Customer_information"

[SQL: 
CREATE TABLE "Text_messages" (
    phone_number SERIAL NOT NULL, 
    p1 INTEGER, 
    PRIMARY KEY (phone_number), 
    FOREIGN KEY(p1) REFERENCES "Customer_information" (p1)
)

]
(Background on this error at: https://sqlalche.me/e/14/f405)

It seems to imply that Customer_information.p1 is not unique but it is a primary key so it is. I’m not sure what I’m missing.

Leave a Comment