Indexes for Constraints

Private: Mastering SQL using Postgresql Creating Tables and Indexes Indexes for Constraints

Let us understand details related to indexes for constraints.

Constraints such as primary key and unique are supported by indexes.
Primary Key - Unique and Not Null.
Unique - Unique and can be null.
Unless data is sorted, we need to perform full table scan to enforce uniqueness. Almost all the databases will create indexes implicitly for Primary Keys as well as Unique constraints.
We cannot define Primary Key or Unique constraint with out associated index.
It is quite common that we explicitly create indexes on foreign key columns to improve the performance.

%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 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 INT,
    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

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'users'

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

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	CHECK	2200_17365_2_not_null
itversity_retail_db	users	CHECK	2200_17365_3_not_null
itversity_retail_db	users	CHECK	2200_17365_4_not_null

%%sql

SELECT * FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
    AND tablename = 'users'

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

schemaname	tablename	indexname	tablespace	indexdef

%sql CREATE SEQUENCE users_user_id_seq

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

[]

%%sql

ALTER TABLE users 
    ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'),
    ADD PRIMARY KEY (user_id)

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

[]

%%sql

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'users'

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

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pkey
itversity_retail_db	users	CHECK	2200_17365_1_not_null
itversity_retail_db	users	CHECK	2200_17365_2_not_null
itversity_retail_db	users	CHECK	2200_17365_3_not_null
itversity_retail_db	users	CHECK	2200_17365_4_not_null

%%sql

SELECT * FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
    AND tablename = 'users'

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

schemaname	tablename	indexname	tablespace	indexdef
public	users	users_pkey	None	CREATE UNIQUE INDEX users_pkey ON public.users USING btree (user_id)

%%sql

SELECT tc.table_catalog,
    tc.table_name, 
    tc.constraint_name,
    pi.indexname
FROM information_schema.table_constraints tc JOIN pg_catalog.pg_indexes pi
    ON tc.constraint_name = pi.indexname
WHERE tc.table_schema = 'public'
    AND tc.table_name = 'users'
    AND tc.constraint_type = 'PRIMARY KEY'

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

table_catalog	table_name	constraint_name	indexname
itversity_retail_db	users	users_pkey	users_pkey

%%sql

ALTER TABLE users
    ADD UNIQUE (user_email_id)

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

[]

%%sql

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'users'

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

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pkey
itversity_retail_db	users	UNIQUE	users_user_email_id_key
itversity_retail_db	users	CHECK	2200_17365_1_not_null
itversity_retail_db	users	CHECK	2200_17365_2_not_null
itversity_retail_db	users	CHECK	2200_17365_3_not_null
itversity_retail_db	users	CHECK	2200_17365_4_not_null

%%sql

SELECT * FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
    AND tablename = 'users'

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

schemaname	tablename	indexname	tablespace	indexdef
public	users	users_pkey	None	CREATE UNIQUE INDEX users_pkey ON public.users USING btree (user_id)
public	users	users_user_email_id_key	None	CREATE UNIQUE INDEX users_user_email_id_key ON public.users USING btree (user_email_id)

%%sql

SELECT tc.table_catalog,
    tc.table_name, 
    tc.constraint_name,
    pi.indexname
FROM information_schema.table_constraints tc JOIN pg_catalog.pg_indexes pi
    ON tc.constraint_name = pi.indexname
WHERE tc.table_schema = 'public'
    AND tc.table_name = 'users'
    AND tc.constraint_type = 'UNIQUE'

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

table_catalog	table_name	constraint_name	indexname
itversity_retail_db	users	users_user_email_id_key	users_user_email_id_key

Note

Query to get all the primary key and unique constraints along with indexes.

%%sql

SELECT tc.table_catalog,
    tc.table_name, 
    tc.constraint_type,
    tc.constraint_name,
    pi.indexname
FROM information_schema.table_constraints tc JOIN pg_catalog.pg_indexes pi
    ON tc.constraint_name = pi.indexname
WHERE tc.table_catalog = 'itversity_retail_db'
    AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE')

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

table_catalog	table_name	constraint_type	constraint_name	indexname
itversity_retail_db	departments	PRIMARY KEY	departments_pkey	departments_pkey
itversity_retail_db	categories	PRIMARY KEY	categories_pkey	categories_pkey
itversity_retail_db	products	PRIMARY KEY	products_pkey	products_pkey
itversity_retail_db	customers	PRIMARY KEY	customers_pkey	customers_pkey
itversity_retail_db	orders	PRIMARY KEY	orders_pkey	orders_pkey
itversity_retail_db	order_items	PRIMARY KEY	order_items_pkey	order_items_pkey
itversity_retail_db	users	PRIMARY KEY	users_pkey	users_pkey
itversity_retail_db	users	UNIQUE	users_user_email_id_key	users_user_email_id_key

Error

It is not possible to drop the indexes that are automatically created to enforce primary key or unique constraints.

%sql DROP INDEX users_user_email_id_key

 * 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 index users_user_email_id_key because constraint users_user_email_id_key on table users requires it
HINT:  You can drop constraint users_user_email_id_key on table users instead.


The above exception was the direct cause of the following exception:

InternalError                             Traceback (most recent call last)
<ipython-input-89-7b38c07068a1> in <module>
----> 1 get_ipython().run_line_magic('sql', 'DROP INDEX users_user_email_id_key')

/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 index users_user_email_id_key because constraint users_user_email_id_key on table users requires it
HINT:  You can drop constraint users_user_email_id_key on table users instead.

[SQL: DROP INDEX users_user_email_id_key]
(Background on this error at: http://sqlalche.me/e/13/2j85)

Previous Topic

Back to Lesson

Next Topic