In Panoply, it is prohibited to drop a table that has dependencies on it using the
DROP TABLE SQL statement.
A database view is a saved SQL query. When you create a database view, you query or reference data from one or more tables. Hence, creating the dependency of the view to one or more tables.
This is why you can't drop tables because there are database views that depend on them. Panoply lets you know about that by displaying an error message.
To identify the dependencies of the table you're trying to drop, you can manually run a query. This way, you can decide it's importance, whether to drop it or not.
If the table-view relationship is of no importance and you really want to drop a table, you can do a "force drop" using any of these options:
- Drop/Delete the table on Panoply's Tables page. This will prompt you to remove the table and all of its dependencies.
CASCADEparameter to your
DROP TABLEstatement. For example:
DROP TABLE <table_name> CASCADE;
Executing this statement will drop the table and all of its dependencies with no errors being raised.
- Use the
WITH NO SCHEMA BINDINGflag when creating a view. For example:
CREATE VIEW v_west_teams AS
SELECT id, name, city, conference
WHERE conference = 'West'
WITH NO SCHEMA BINDING;
Make sure to qualify the tables referenced in the
SELECTstatement with a schema name. In the example above,
publicis the schema name.
Between the three options:
- Option #1 is the most recommended because of the UI feedback that informs you about the table dependencies.
- Option #2 doesn't provide any feedback at all and assumes you have knowledge of the table dependencies.
- Option #3 doesn't create the dependency between a table and a view. This is fine if you want to drop tables (using only the simple
DROP TABLEstatement) without raising dependency errors. But you will encounter errors if you try to query a view that references a non-existent table.