Durga Gadiraju

Indexes for Constraints in PostgreSQL

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.

In [1]:

%load_ext sql

In [2]:

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db

In [3]:

%sql DROP TABLE IF EXISTS users

Done.

Out[3]:

[]

In [4]:

%sql DROP SEQUENCE IF EXISTS users_user_id_seq

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[4]:

[]

In [5]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[5]:

[]

In [6]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
3 rows affected.

Out[6]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	CHECK	2200_16563_2_not_null
itversity_retail_db	users	CHECK	2200_16563_3_not_null
itversity_retail_db	users	CHECK	2200_16563_4_not_null

In [7]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
0 rows affected.

Out[7]:

schemaname	tablename	indexname	tablespace	indexdef

In [8]:

%sql CREATE SEQUENCE users_user_id_seq

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[8]:

[]

In [9]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[9]:

[]

In [ ]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
5 rows affected.

Out[ ]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pkey
itversity_retail_db	users	CHECK	2200_16563_1_not_null
itversity_retail_db	users	CHECK	2200_16563_2_not_null
itversity_retail_db	users	CHECK	2200_16563_3_not_null
itversity_retail_db	users	CHECK	2200_16563_4_not_null

In [ ]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[ ]:

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

In [ ]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[ ]:

table_catalog	table_name	constraint_name	indexname
itversity_retail_db	users	users_pkey	users_pkey

In [ ]:

%%sql

ALTER TABLE users
    ADD UNIQUE (user_email_id)

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
Done.

Out[ ]:

[]

In [ ]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
6 rows affected.

Out[ ]:

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_16563_1_not_null
itversity_retail_db	users	CHECK	2200_16563_2_not_null
itversity_retail_db	users	CHECK	2200_16563_3_not_null
itversity_retail_db	users	CHECK	2200_16563_4_not_null

In [ ]:

%%sql

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

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db
2 rows affected.

Out[ ]:

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)

In [ ]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
1 rows affected.

Out[ ]:

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.

In [ ]:

%%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:***@pg.itversity.com:5432/itversity_retail_db
8 rows affected.

Out[ ]:

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.

In [18]:

%sql DROP INDEX users_user_email_id_key

 * postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db

---------------------------------------------------------------------------
DependentObjectsStillExist                Traceback (most recent call last)
File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

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)
Input In [18], in <cell line: 1>()
----> 1 get_ipython().run_line_magic('sql', 'DROP INDEX users_user_email_id_key')

File ~/.local/lib/python3.8/site-packages/IPython/core/interactiveshell.py:2285, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2283     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2284 with self.builtin_trap:
-> 2285     result = fn(*args, **kwargs)
   2286 return result

File ~/.local/lib/python3.8/site-packages/sql/magic.py:217, in SqlMagic.execute(self, line, cell, local_ns)
    214     return
    216 try:
--> 217     result = sql.run.run(conn, parsed["sql"], self, user_ns)
    219     if (
    220         result is not None
    221         and not isinstance(result, str)
   (...)
    224         # Instead of returning values, set variables directly in the
    225         # users namespace. Variable names given by column names
    227         if self.autopandas:

File ~/.local/lib/python3.8/site-packages/sql/run.py:367, 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:

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1286, in Connection.execute(self, statement, *multiparams, **params)
   1282     util.raise_(
   1283         exc.ObjectNotExecutableError(statement), replace_context=err
   1284     )
   1285 else:
-> 1286     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File ~/.local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:325, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    321 def _execute_on_connection(
    322     self, connection, multiparams, params, execution_options, _force=False
    323 ):
    324     if _force or self.supports_execution:
--> 325         return connection._execute_clauseelement(
    326             self, multiparams, params, execution_options
    327         )
    328     else:
    329         raise exc.ObjectNotExecutableError(self)

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1478, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1466 compiled_cache = execution_options.get(
   1467     "compiled_cache", self.engine._compiled_cache
   1468 )
   1470 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1471     dialect=dialect,
   1472     compiled_cache=compiled_cache,
   (...)
   1476     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1477 )
-> 1478 ret = self._execute_context(
   1479     dialect,
   1480     dialect.execution_ctx_cls._init_compiled,
   1481     compiled_sql,
   1482     distilled_params,
   1483     execution_options,
   1484     compiled_sql,
   1485     distilled_params,
   1486     elem,
   1487     extracted_params,
   1488     cache_hit=cache_hit,
   1489 )
   1490 if has_events:
   1491     self.dispatch.after_execute(
   1492         self,
   1493         elem,
   (...)
   1497         ret,
   1498     )

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1842, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1839             branched.close()
   1841 except BaseException as e:
-> 1842     self._handle_dbapi_exception(
   1843         e, statement, parameters, cursor, context
   1844     )
   1846 return result

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:2023, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2021     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2022 elif should_wrap:
-> 2023     util.raise_(
   2024         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2025     )
   2026 else:
   2027     util.raise_(exc_info[1], with_traceback=exc_info[2])

File ~/.local/lib/python3.8/site-packages/sqlalchemy/util/compat.py:207, in raise_(***failed resolving arguments***)
    204     exception.__cause__ = replace_context
    206 try:
--> 207     raise exception
    208 finally:
    209     # credit to
    210     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    211     # as the __traceback__ object creates a cycle
    212     del exception, replace_context, from_, with_traceback

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1799, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1797                 break
   1798     if not evt_handled:
-> 1799         self.dialect.do_execute(
   1800             cursor, statement, parameters, context
   1801         )
   1803 if self._has_events or self.engine._has_events:
   1804     self.dispatch.after_cursor_execute(
   1805         self,
   1806         cursor,
   (...)
   1810         context.executemany,
   1811     )

File ~/.local/lib/python3.8/site-packages/sqlalchemy/engine/default.py:717, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    716 def do_execute(self, cursor, statement, parameters, context=None):
--> 717     cursor.execute(statement, parameters)

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: https://sqlalche.me/e/14/2j85)

Durga Gadiraju

Indexes for Constraints in PostgreSQL

Indexes for Constraints¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email