Dropping Tables¶
Let us go through the details related to dropping tables.
We can drop table using
DROP TABLE
.All the direct dependent objects such as indexes, primary key constraints, unique constraints, not null constraints will automatically be dropped.
Sequences will be dropped only if the sequence is owned by the column.
If there are child tables for the table being dropped, then we need to specify
CASCADE
.Using
CASCADE
will drop the constraints from the child table, but not the child tables themselves.We can also drop the foreign key constraints before dropping the parent table instead of using
CASCADE
.
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%sql DROP TABLE IF EXISTS user_logins
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP TABLE IF EXISTS users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_first_name VARCHAR(30) NOT NULL,
user_last_name VARCHAR(30) NOT NULL,
user_email_id VARCHAR(50) NOT NULL,
user_email_validated BOOLEAN,
user_password VARCHAR(200),
user_role VARCHAR(1),
is_active BOOLEAN,
created_dt DATE DEFAULT CURRENT_DATE
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE user_logins (
user_login_id SERIAL PRIMARY KEY,
user_id INT,
user_login_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
user_ip_addr VARCHAR(20)
)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
ALTER TABLE user_logins
ADD FOREIGN KEY (user_id)
REFERENCES users(user_id)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
---|---|---|---|---|---|---|---|---|---|---|---|
itversity_retail_db | public | users | BASE TABLE | None | None | None | None | None | YES | NO | None |
itversity_retail_db | public | user_logins | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
SELECT * FROM information_schema.sequences
WHERE sequence_name = 'users_user_id_seq'
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
---|---|---|---|---|---|---|---|---|---|---|---|
itversity_retail_db | public | users_user_id_seq | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
%%sql
SELECT * FROM information_schema.sequences
WHERE sequence_name = 'user_logins_user_login_id_seq'
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
---|---|---|---|---|---|---|---|---|---|---|---|
itversity_retail_db | public | user_logins_user_login_id_seq | integer | 32 | 2 | 0 | 1 | 1 | 2147483647 | 1 | NO |
Error
We will not be able to drop the parent tables with out dropping the child tables or specifying CASCADE
. Using CASCADE
will not drop child tables, it only drops the foreign key constraints.
%sql DROP TABLE users
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
---------------------------------------------------------------------------
DependentObjectsStillExist Traceback (most recent call last)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1276 self.dialect.do_execute(
-> 1277 cursor, statement, parameters, context
1278 )
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
592 def do_execute(self, cursor, statement, parameters, context=None):
--> 593 cursor.execute(statement, parameters)
594
DependentObjectsStillExist: cannot drop table users because other objects depend on it
DETAIL: constraint user_logins_user_id_fkey on table user_logins depends on table users
HINT: Use DROP ... CASCADE to drop the dependent objects too.
The above exception was the direct cause of the following exception:
InternalError Traceback (most recent call last)
<ipython-input-175-46beae3783fd> in <module>
----> 1 get_ipython().run_line_magic('sql', 'DROP TABLE users')
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/interactiveshell.py in run_line_magic(self, magic_name, line, _stack_depth)
2324 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals
2325 with self.builtin_trap:
-> 2326 result = fn(*args, **kwargs)
2327 return result
2328
<decorator-gen-135> in execute(self, line, cell, local_ns)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
185 # but it's overkill for just that one bit of state.
186 def magic_deco(arg):
--> 187 call = lambda f, *a, **k: f(*a, **k)
188
189 if callable(arg):
<decorator-gen-134> in execute(self, line, cell, local_ns)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in <lambda>(f, *a, **k)
185 # but it's overkill for just that one bit of state.
186 def magic_deco(arg):
--> 187 call = lambda f, *a, **k: f(*a, **k)
188
189 if callable(arg):
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sql/magic.py in execute(self, line, cell, local_ns)
215
216 try:
--> 217 result = sql.run.run(conn, parsed["sql"], self, user_ns)
218
219 if (
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sql/run.py in run(conn, sql, config, user_namespace)
365 else:
366 txt = sqlalchemy.sql.text(statement)
--> 367 result = conn.session.execute(txt, user_namespace)
368 _commit(conn=conn, config=config)
369 if result and config.feedback:
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
1009 )
1010 else:
-> 1011 return meth(self, multiparams, params)
1012
1013 def _execute_function(self, func, multiparams, params):
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
296 def _execute_on_connection(self, connection, multiparams, params):
297 if self.supports_execution:
--> 298 return connection._execute_clauseelement(self, multiparams, params)
299 else:
300 raise exc.ObjectNotExecutableError(self)
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
1128 distilled_params,
1129 compiled_sql,
-> 1130 distilled_params,
1131 )
1132 if self._has_events or self.engine._has_events:
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1315 except BaseException as e:
1316 self._handle_dbapi_exception(
-> 1317 e, statement, parameters, cursor, context
1318 )
1319
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1509 elif should_wrap:
1510 util.raise_(
-> 1511 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
1512 )
1513 else:
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
180
181 try:
--> 182 raise exception
183 finally:
184 # credit to
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1275 if not evt_handled:
1276 self.dialect.do_execute(
-> 1277 cursor, statement, parameters, context
1278 )
1279
/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
591
592 def do_execute(self, cursor, statement, parameters, context=None):
--> 593 cursor.execute(statement, parameters)
594
595 def do_execute_no_params(self, cursor, statement, context=None):
InternalError: (psycopg2.errors.DependentObjectsStillExist) cannot drop table users because other objects depend on it
DETAIL: constraint user_logins_user_id_fkey on table user_logins depends on table users
HINT: Use DROP ... CASCADE to drop the dependent objects too.
[SQL: DROP TABLE users]
(Background on this error at: http://sqlalche.me/e/13/2j85)
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active)
VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
[]
%%sql
INSERT INTO users
(user_first_name, user_last_name, user_email_id, user_password, user_role, is_active)
VALUES
('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true),
('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true),
('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.
[]
%%sql
INSERT INTO user_logins
(user_id)
VALUES
(1),
(2),
(3),
(1),
(1),
(4)
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
[]
%sql DROP TABLE users CASCADE
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action |
---|---|---|---|---|---|---|---|---|---|---|---|
itversity_retail_db | public | user_logins | BASE TABLE | None | None | None | None | None | YES | NO | None |
%%sql
SELECT * FROM information_schema.sequences
WHERE sequence_name = 'users_user_id_seq'
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
---|
%sql SELECT * FROM user_logins
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.
user_login_id | user_id | user_login_ts | user_ip_addr |
---|---|---|---|
1 | 1 | 2020-11-23 16:44:45.373009 | None |
2 | 2 | 2020-11-23 16:44:45.373009 | None |
3 | 3 | 2020-11-23 16:44:45.373009 | None |
4 | 1 | 2020-11-23 16:44:45.373009 | None |
5 | 1 | 2020-11-23 16:44:45.373009 | None |
6 | 4 | 2020-11-23 16:44:45.373009 | None |