SQLAlchemy外键约束不兼容错误排查求助

操作步骤 #

• 执行SQL导入命令：$: cat 7-dump.sql | mysql -uroot -p
• 运行控制台验证表创建：echo "quit" | HBNB_MYSQL_USER=hbnb_dev HBNB_MYSQL_PWD=hbnb_dev_pwd HBNB_MYSQL_HOST=localhost HBNB_MYSQL_DB=hbnb_dev_db HBNB_TYPE_STORAGE=db ./console.py

报错信息 #

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (3780, "Referencing column 'city_id' and referenced column 'id' in foreign key constraint 'places_ibfk_1' are incompatible.")

对应的SQL语句：

CREATE TABLE places (
    city_id VARCHAR(60) NOT NULL, 
    user_id VARCHAR(60) NOT NULL, 
    name VARCHAR(128) NOT NULL, 
    description VARCHAR(1024), 
    number_rooms INTEGER NOT NULL, 
    number_bathrooms INTEGER NOT NULL, 
    max_guest INTEGER NOT NULL, 
    price_by_night INTEGER NOT NULL, 
    latitude FLOAT, 
    longitude FLOAT, 
    id VARCHAR(60) NOT NULL, 
    created_at DATETIME NOT NULL, 
    updated_at DATETIME NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(city_id) REFERENCES cities (id), 
    FOREIGN KEY(user_id) REFERENCES users (id), 
    UNIQUE (id)
)

已完成的检查 #

1. 代码字段验证：检查cities.py和places.py的字段定义，类型匹配：
   • cities.py中id字段定义：

     #!/usr/bin/python3
     """city class"""
     from sqlalchemy import ForeignKey
     from sqlalchemy.orm import relationship
     from sqlalchemy import Column, Integer, String
     from models.base_model import BaseModel, Base
     from models.place import Place
     
     
     class City(BaseModel, Base):
         """This is the class for City
         Attributes: state_id: The state id
             name: input name
         """
         __tablename__ = "cities"
         id = Column(String(60), primary_key=True, nullable=False)
         name = Column(String(128), nullable=False)
         state_id = Column(String(60), ForeignKey('states.id'), nullable=False)
         places = relationship("Place", cascade='all, delete, delete-orphan',
                               backref="cities")
     

   • places.py中city_id字段定义：

     #!/usr/bin/python3
     """place class"""
     from os import getenv
     import models
     import shlex
     from models.base_model import BaseModel, Base
     from sqlalchemy import Column, Table, String, Integer, Float, ForeignKey
     from sqlalchemy.orm import relationship
     
     
     place_amenity = Table("place_amenity", Base.metadata,
                           Column("place_id", String(60),
                                  ForeignKey("places.id"),
                                  primary_key=True,
                                  nullable=False),
                           Column("amenity_id", String(60),
                                  ForeignKey("amenities.id"),
                                  primary_key=True,
                                  nullable=False))
     
     
     class Place(BaseModel, Base):
         """This is the class for Place"""
         __tablename__ = "places"
         city_id = Column(String(60), ForeignKey("cities.id"), nullable=False)
         user_id = Column(String(60), ForeignKey("users.id"), nullable=False)
         name = Column(String(128), nullable=False)
         description = Column(String(1024))
         number_rooms = Column(Integer, nullable=False, default=0)
         number_bathrooms = Column(Integer, nullable=False, default=0)
         max_guest = Column(Integer, nullable=False, default=0)
         price_by_night = Column(Integer, nullable=False, default=0)
         latitude = Column(Float)
         longitude = Column(Float)
         amenity_ids = []
     
         if getenv("HBNB_TYPE_STORAGE") == "db":
             reviews = relationship("Review", cascade='all, delete, delete-orphan',
                                    backref="place")
     
             amenities = relationship("Amenity", secondary=place_amenity,
                                      viewonly=False,
                                      back_populates="place_amenities")
         else:
             @property
             def reviews(self):
                 """ Returns list of reviews.id """
                 var = models.storage.all()
                 lista = []
                 result = []
                 for key in var:
                     review = key.replace('.', ' ')
                     review = shlex.split(review)
                     if (review[0] == 'Review'):
                         lista.append(var[key])
                 for elem in lista:
                     if (elem.place_id == self.id):
                         result.append(elem)
                 return (result)
     
             @property
             def amenities(self):
                 """ Returns list of amenity ids """
                 return self.amenity_ids
     
             @amenities.setter
             def amenities(self, obj=None):
                 """ Appends amenity ids to the attribute """
                 if type(obj) is models.Amenity and obj.id not in self.amenity_ids:
                     self.amenity_ids.append(obj.id)
     

2. 数据库结构验证：手动查询cities表，id字段类型为varchar(60)，与代码定义一致；当前数据库中不存在places表。

恳请帮忙排查导致外键约束不兼容的具体原因。

内容的提问来源于stack exchange，提问作者Ivan Osei Kuffour