title: sqlalchemy + postgres asyncpg.exceptions.ForeignKeyViolationError on relationship()
body: <p>So far in my project I was prototyping and used sqlite with sqlalchemy.
  I have a couple of tables with oneToMany relationships, all works nicely.
  Now, after switching to postgres, I get the <code>asyncpg.exceptions.ForeignKeyViolationError</code> for the tables where I defined ForeignKeys.</p>
  <p>Here is my sqlalchemy parent and child model:</p>
  <pre><code>from sqlalchemy.orm import relationship
  from .database import Base
  from fastapi_users_db_sqlalchemy import SQLAlchemyBaseUserTable
  
  
  class Company(Base):
      __tablename__ = &quot;company&quot;
  
      id = Column(Integer, primary_key=True, index=True)
      name = Column(String, unique=True)
      type = Column(
          String(length=10),
          server_default=&quot;No type given&quot;,
          nullable=False,
      )
  
      users = relationship(&quot;User&quot;, back_populates=&quot;company&quot;)
  
  class User(Base, SQLAlchemyBaseUserTable):
      first_name = Column(
          String(length=50),
          index=True,
          server_default=&quot;No name given&quot;,
          nullable=False,
      )
  
      company_id = Column(Integer, ForeignKey(&quot;company.id&quot;), nullable=False)
      company = relationship(&quot;Company&quot;, back_populates=&quot;users&quot;)
  </code></pre>
  <p>When trying to register a user, I get:</p>
  <pre><code>INFO:     172.17.0.1:63534 - &quot;POST /auth/register HTTP/1.1&quot; 500 Internal Server Error
  ERROR:    Exception in ASGI application
  Traceback (most recent call last):
    File &quot;/usr/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py&quot;, line 373, in run_asgi
      result = await app(self.scope, self.receive, self.send)
    File &quot;/usr/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py&quot;, line 75, in __call__
      return await self.app(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/fastapi/applications.py&quot;, line 208, in __call__
      await super().__call__(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/applications.py&quot;, line 112, in __call__
      await self.middleware_stack(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/middleware/errors.py&quot;, line 181, in __call__
      raise exc
    File &quot;/usr/lib/python3.9/site-packages/starlette/middleware/errors.py&quot;, line 159, in __call__
      await self.app(scope, receive, _send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/middleware/cors.py&quot;, line 92, in __call__
      await self.simple_response(scope, receive, send, request_headers=headers)
    File &quot;/usr/lib/python3.9/site-packages/starlette/middleware/cors.py&quot;, line 147, in simple_response
      await self.app(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/exceptions.py&quot;, line 82, in __call__
      raise exc
    File &quot;/usr/lib/python3.9/site-packages/starlette/exceptions.py&quot;, line 71, in __call__
      await self.app(scope, receive, sender)
    File &quot;/usr/lib/python3.9/site-packages/starlette/routing.py&quot;, line 656, in __call__
      await route.handle(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/routing.py&quot;, line 259, in handle
      await self.app(scope, receive, send)
    File &quot;/usr/lib/python3.9/site-packages/starlette/routing.py&quot;, line 61, in app
      response = await func(request)
    File &quot;/usr/lib/python3.9/site-packages/fastapi/routing.py&quot;, line 226, in app
      raw_response = await run_endpoint_function(
    File &quot;/usr/lib/python3.9/site-packages/fastapi/routing.py&quot;, line 159, in run_endpoint_function
      return await dependant.call(**values)
    File &quot;/usr/lib/python3.9/site-packages/fastapi_users/router/register.py&quot;, line 32, in register
      created_user = await user_manager.create(user, safe=True, request=request)
    File &quot;/usr/lib/python3.9/site-packages/fastapi_users/manager.py&quot;, line 153, in create
      created_user = await self.user_db.create(db_user)
    File &quot;/usr/lib/python3.9/site-packages/fastapi_users_db_sqlalchemy/__init__.py&quot;, line 159, in create
      await self.database.execute(query, user_dict)
    File &quot;/usr/lib/python3.9/site-packages/databases/core.py&quot;, line 169, in execute
      return await connection.execute(query, values)
    File &quot;/usr/lib/python3.9/site-packages/databases/core.py&quot;, line 295, in execute
      return await self._connection.execute(built_query)
    File &quot;/usr/lib/python3.9/site-packages/databases/backends/postgres.py&quot;, line 210, in execute
      return await self._connection.fetchval(query_str, *args)
    File &quot;/usr/lib/python3.9/site-packages/asyncpg/connection.py&quot;, line 645, in fetchval
      data = await self._execute(query, args, 1, timeout)
    File &quot;/usr/lib/python3.9/site-packages/asyncpg/connection.py&quot;, line 1659, in _execute
      result, _ = await self.__execute(
    File &quot;/usr/lib/python3.9/site-packages/asyncpg/connection.py&quot;, line 1684, in __execute
      return await self._do_execute(
    File &quot;/usr/lib/python3.9/site-packages/asyncpg/connection.py&quot;, line 1731, in _do_execute
      result = await executor(stmt, None)
    File &quot;asyncpg/protocol/protocol.pyx&quot;, line 201, in bind_execute
  asyncpg.exceptions.ForeignKeyViolationError: insert or update on table &quot;user&quot; violates foreign key constraint &quot;user_company_id_fkey&quot;
  DETAIL:  Key (company_id)=(1) is not present in table &quot;company&quot;.
  </code></pre>
  <p>The Error is right, there is no column <code>company_id</code> in table <code>company</code> - only <code>id</code>.
  I defined to use the value <code>company.id</code> for column <code>company_id</code> in the <code>user</code>-table.
  Why is the program checking on <code>company_id</code>?</p>
  <p>I followed this sqlalchemy documentation to define the relationships: <a href="https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html" rel="nofollow noreferrer">https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html</a>
  It works fine like this with sqlite.</p>
  <p>I can see in the traceback, that it connects to the right backend (<code>postgres</code>), so does that mean, sqlalchemy is not translating the relationship in the right way for postgres?</p>
  <p>Any ideas how to solve this?</p>