cannot drop table because other objects depend on it

Question

I'm trying to develop a postgres database and I'm using SQLAlchemy to map my db. The thing is that I was getting way too many errors about dependencies and started to clean some relations to debug and still I get the same problem

when I do

python manage.py db upgrade

I get the following error:

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' {}

this is my postgres code:

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
);

At first I thought that was the order I was doing the DROP TABLE, it seems fixed and then I added the CASCADE after the DROP TABLE, still not working.

manage.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()

Any idea what is going on?

Regards

Answer 1

You are dropping the tables in the wrong order - you should drop the tables have foreign key references before you drop those which reference them.

In fact (assuming there are no other tables not shown in your question) then dropping them in the reverse order that you create them should work.