下面我有三个表,它们之间有多对多的关系,问题是我无法删除数据库中的用户表: “表 imagesmetadata 上的约束 imagesmetadata_user_id_fkey 取决于表用户 表令牌上的约束 tokens_user_id_fkey 取决于表 usercannot 删除表用户,因为其他对象依赖于它”
删除令牌和图像元数据表后删除用户表的唯一方法|| |虽然我已经把这个
放在我的代码中,但它仍然不起作用,有人可以帮忙吗?
cascade="all, delete-orphan"
这是我的模型代码:
用户文件:
ImagesMetadata 文件:
from config.Database import Base
from sqlalchemy import Column, Boolean, String, ForeignKey, DateTime, func, BigInteger
from sqlalchemy.orm import relationship
from model import ImagesMetaData
class User(Base):
__tablename__ = "users"
id = Column(String, primary_key=True, index=True)
email = Column(String, unique=True, index=True, nullable=False)
name = Column(String, nullable=False)
email_verified = Column(Boolean, nullable=False, default=False)
picture = Column(String, default=None)
created_at = Column(DateTime, server_default=func.now())
tokens = relationship("Token", back_populates="user", cascade="all, delete-orphan")
images = relationship("ImagesMetaData", back_populates="owner", cascade="all, delete-orphan")
class Token(Base):
__tablename__ = 'tokens'
id = Column(BigInteger, primary_key=True, autoincrement=True)
access_token = Column(String, nullable=False)
refresh_token = Column(String, nullable=False)
expires_at = Column(DateTime, nullable=False)
user_id = Column(String, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
user = relationship("User", back_populates="tokens")
created_at = Column(DateTime, server_default=func.now())
updated_at = Column(DateTime, default=None)
ImagesMetadata file :
from sqlalchemy import Column, BigInteger, ForeignKey, String, DateTime, func
from sqlalchemy.orm import relationship
from config.Database import Base
from model import User
class ImagesMetaData(Base):
__tablename__ = "imagesmetadata"
id = Column(BigInteger, primary_key=True, autoincrement=True, nullable=False)
name = Column(String(200), nullable=False)
file_type = Column(String(10), nullable=False)
upload_at = Column(DateTime, server_default=func.now())
Bucket_folder = Column(String, nullable=False)
user_id = Column(String, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
# Relationship back to User
owner = relationship("User", back_populates="images")
错误提示表明,在数据库中定义了外键约束,这些约束阻止在相关记录仍然存在于
imagesmetadata
和
tokens
表中的情况下删除
users
表中的记录。 已经通过在模型中包含
cascade="all, delete-orphan"
来解决此问题,但是 SQLAlchemy 中的外键级联选项的实现方式略有不同。
以下是解决此问题并让代码按预期工作的步骤:
-
在关系定义中配置
cascade
选项: 确保在User
模型中定义与Token
和ImagesMetaData
的关系时,正确配置了cascade
选项。cascade="all, delete-orphan"
选项应应用于User
模型中的关系,因为它拥有级联删除操作。
```python class User(Base): # ... other columns ...
tokens = relationship("Token", back_populates="user", cascade="all, delete-orphan")
images = relationship("ImagesMetaData", back_populates="owner", cascade="all, delete-orphan")
```
-
在数据库中配置
ondelete
选项:
虽然在
Token
和
ImagesMetaData
模型中设置了
ondelete='CASCADE'
,但最好在数据库级别也设置它以确保一致性。当其引用的
User
记录被删除时,这将自动级联删除相关记录。
```python class Token(Base): # ... other columns ...
user_id = Column(String, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
class ImagesMetaData(Base): # ... other columns ...
user_id = Column(String, ForeignKey("users.id", ondelete='CASCADE'), nullable=False)
```
-
提交更改并测试:
保存对模型所做的更改,然后使用 SQLAlchemy 的
session.delete()
方法删除User
记录。cascade
选项应确保在删除User
记录时自动删除相关Token
和ImagesMetaData
记录。
以下是在 FastAPI 路由中实现此功能的示例:
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.orm import Session
from database import get_db
from models import User
router = APIRouter()
@router.delete("/users/{user_id}", status_code=204)
def delete_user(user_id: str, db: Session = Depends(get_db)):
user = db.query(User).filter(User.id == user_id).first()
if not user:
raise HTTPException(status_code=404, detail="User not found")
db.delete(user)
db.commit()
通过这些更改,应该能够从数据库中删除
User
记录及其相关
Token
和
ImagesMetaData
记录,而不会遇到任何外键约束错误。