so I'm getting an error that no unique constraint matches given keys for one of my tables. I have two tables mainly: Weather and Report, of which we have one-to-one relationship(one report for one weather event). I am using FastAPI and Async sqlalchemy 1.4 and Postgresql(asyncpg).

models/report.py

import uuid
import datetime

from sqlalchemy import (
    Column,
    Float,
    ForeignKeyConstraint,
    Integer,
    String,
    DateTime,
    ForeignKey,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship

from db.base_class import Base


class Report(Base):
    id = Column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,
    )
    description = Column(String, nullable=False)
    location = Column(String, ForeignKey("weather.city"))
    temp = Column(Float, ForeignKey("weather.temperature"))
    created_date = Column(DateTime, default=datetime.datetime.utcnow)
    # weather = relationship("Weather", back_populates="report")
    # __table_args__ = (
    #     ForeignKeyConstraint(["location", "temp"], ["weather.city", "weather.temperature"]),
    #     {},
    # )

models/weather.py

import uuid
import datetime

from sqlalchemy import Column, Float, String, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship

from db.base_class import Base


class Weather(Base):
    id = Column(
        UUID(as_uuid=True),
        primary_key=True,
        default=uuid.uuid4,
    )
    city = Column(String, nullable=False)
    state = Column(String, nullable=True)
    country = Column(String, nullable=False)
    units = Column(String, nullable=True, default="metric")
    temperature = Column(Float, nullable=False)
    created_date = Column(DateTime, default=datetime.datetime.utcnow)
    report = relationship("Report", back_populates="weather")

To create the tables, I have this async session in my 'main.py' to create tables:

@api.on_event("startup")
async def start_app():
    # create db tables on initializing
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

The error is: no unique constraint matching given keys for referenced table "weather"

I am not quite sure where I'm doing wrong since I have a foreign key in Report Model that matches with the primary key in Weather Model.

Any suggestions/tips are welcome!

Thanks.