python - 不能删除表，因为其他对象依赖于它

Question

我正在尝试开发一个 postgres 数据库，并且我正在使用 SQLAlchemy 来映射我的数据库。问题是我收到了太多关于依赖项的错误，并开始清理一些关系以进行调试，但我仍然遇到同样的问题

当我这样做的时候

python manage.py db upgrade

我收到以下错误:

sqlalchemy.exc.InternalError: (InternalError) cannot drop table proposalstate because other objects depend on it DETAIL: constraint proposals_idproposalstate_fkey on table proposals depends on table proposalstate HINT: Use DROP ... CASCADE to drop the dependent objects too. '\nDROP TABLE proposalstate' {}

这是我的postgres代码:

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS position CASCADE;
DROP TABLE IF EXISTS proposalstate CASCADE;
DROP TABLE IF EXISTS proposals CASCADE;
DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS politics CASCADE;
DROP TABLE IF EXISTS role CASCADE;
DROP TABLE IF EXISTS domain CASCADE;
DROP TABLE IF EXISTS organizations CASCADE;


CREATE TABLE users (
    uid SERIAL PRIMARY KEY,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    email VARCHAR(120) NOT NULL,
    pwdhash VARCHAR(150) NOT NULL,
    phoneNumber INTEGER
);

CREATE TABLE organizations (
    idOrganization SERIAL PRIMARY KEY,
    publicName TEXT NOT NULL,
    completeName TEXT NOT NULL,
    startDate NUMERIC CHECK (startDate <= EXTRACT(ISOYEAR FROM CURRENT_TIMESTAMP)),
    endDate NUMERIC CHECK (startDate < endDate),
    publicBioLink TEXT
);

CREATE TABLE category (
    idCategory SERIAL PRIMARY KEY,
    category TEXT UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE proposalstate (
    idProposalState SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    stateDate TIMESTAMP,
    stateLink TEXT
);

SELECT EXTRACT(ISOYEAR FROM CURRENT_TIMESTAMP);

CREATE TABLE proposals (
    idProposal SERIAL PRIMARY KEY,
   -- nameProposal VARCHAR(120) NOT NULL,
    description VARCHAR(10000) NOT NULL,
    "dateProposal" TIMESTAMP,    
    linkProposal VARCHAR(1000),
    idCategory INTEGER REFERENCES category(idCategory) ON DELETE CASCADE,
    idProposalState INTEGER REFERENCES proposalstate(idProposalState) ON DELETE CASCADE
);



CREATE TABLE politics (
    idPolitician SERIAL PRIMARY KEY,
    publicName VARCHAR(150) NOT NULL,
    completeName VARCHAR(300) NOT NULL,
    publicBioLink TEXT,
    "startDate" TIMESTAMP,
    "endDate" TIMESTAMP CHECK ("startDate" < "endDate")
);



CREATE TABLE domain (
    idDomain SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    officialName TEXT NOT NULL,
    publicBioLink TEXT
);

CREATE TABLE role (
    idRole SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE position (
    idPosition SERIAL PRIMARY KEY ,
    name TEXT NOT NULL,
    "dateStart" TIMESTAMP,
    "dateEnd" TIMESTAMP CHECK ("dateStart" < "dateEnd"),
    link TEXT,
    idPolitician INTEGER NOT NULL REFERENCES politics(idPolitician) ON DELETE CASCADE,
    idRole INTEGER NOT NULL REFERENCES role(idRole) ON DELETE CASCADE,
    idDomain INTEGER REFERENCES domain(idDomain),
    idOrganization INTEGER NOT NULL REFERENCES organizations(idOrganization) ON DELETE CASCADE
);

起初我以为这是我执行 DROP TABLE 的顺序，它似乎已修复，然后我添加了 CASCADE在DROP TABLE之后，还是不行。

管理.py

import os
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand

from app import app
from models import db


app.config.from_object(os.environ['APP_SETTINGS'])

migrate = Migrate(app, db)
manager = Manager(app)

manager.add_command('db', MigrateCommand)


if __name__ == '__main__':
    manager.run()

知道发生了什么吗？

问候

Answer 1

您以错误的顺序删除表 - 在删除引用它们的表之前，您应该删除具有外键引用的表。

事实上(假设您的问题中没有未显示的其他表)然后以与您创建它们相反的顺序删除它们应该可行。