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.
-
Add the
CASCADE
parameter to yourDROP TABLE
statement. 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 BINDING
flag when creating a view. For example:CREATE VIEW v_west_teams AS
SELECT id, name, city, conference
FROM public.nba_teams
WHERE conference = 'West'
WITH NO SCHEMA BINDING;Make sure to qualify the tables referenced in the
SELECT
statement with a schema name. In the example above,public
is 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 TABLE
statement) without raising dependency errors. But you will encounter errors if you try to query a view that references a non-existent table.
Comments
0 comments
Please sign in to leave a comment.