Dropping Tables

Topic
Materials

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

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%sql DROP TABLE IF EXISTS user_logins

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%sql DROP TABLE IF EXISTS users

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

ALTER TABLE user_logins
    ADD FOREIGN KEY (user_id)
    REFERENCES users(user_id)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.

Copy to clipboard

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'

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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'

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db

Copy to clipboard

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

 in execute(self, line, cell, local_ns)

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in (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):

 in execute(self, line, cell, local_ns)

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/magic.py in (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)

Copy to clipboard

%%sql

INSERT INTO users (user_first_name, user_last_name, user_email_id)
VALUES ('Donald', 'Duck', 'donald@duck.com')

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

[]

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

INSERT INTO user_logins 
    (user_id)
VALUES
    (1),
    (2),
    (3),
    (1),
    (1),
    (4)

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.

Copy to clipboard

[]

Copy to clipboard

%sql DROP TABLE users CASCADE

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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'

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
6 rows affected.

Copy to clipboard

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