Durga Gadiraju

Managing Constraints in PostgreSQL

Managing Constraints¶

Let us understand how we can manage constraints.

We can add constraints while creating the tables or after creating the tables.
Constraints such as NOT NULL, CHECK, FOREIGN KEY are automatically dropped when we drop the table.
Even PRIMARY KEY and UNIQUE constraints are dropped if they are not used to enforce constraints. When PRIMARY KEY or UNIQUE constraint is referred by child table then there can be errors.
We can add constraints to existing table using ALTER TABLE with ADD. We can specify the name using CONSTRAINT keyword.
Constraints from the table can be dropped using ALTER TABLE with DROP.
Let us perform tasks to understand how we can use ALTER TABLE command to add or drop the constraints.
- Use the prior users table with out any constraints.
- Add primary key constraint on user_id.
- Add unique constraint on user_email_id.
- Add not null constraints user_email_validated, user_role, created_dt, last_updated_ts
- Add check constraint to user_role with ‘U’ and ‘A’ as accepted values.
- Add new table user_logins with below columns and establish foreign key relationship with users.
  - user_login_id – SERIAL and PRIMARY KEY
  - user_id – INT
  - user_login_time – TIMESTAMP defaulted to CURRENT_TIMESTAMP
  - user_logins is child table to users with many to one relationship. Hence, create foreign key between user_logins.user_id to users.user_id.

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 CREATE SEQUENCE users_user_id_seq

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

Out[6]:

[]

In [7]:

%sql ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq')

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

Out[7]:

[]

In [8]:

%%sql

ALTER TABLE users
    ALTER COLUMN user_email_validated SET DEFAULT FALSE,
    ALTER COLUMN is_active SET DEFAULT FALSE

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

Out[8]:

[]

In [9]:

%%sql

ALTER TABLE users
    ALTER COLUMN user_role SET DATA TYPE CHAR(1),
    ALTER COLUMN user_role SET DEFAULT 'U'

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

Out[9]:

[]

In [10]:

%%sql

ALTER TABLE users
    ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

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

Out[10]:

[]

In [11]:

%%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[11]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null

In [12]:

%sql ALTER TABLE users ADD PRIMARY KEY (user_id)

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

Out[12]:

[]

In [13]:

%%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[13]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pkey
itversity_retail_db	users	CHECK	2200_16527_1_not_null
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null

In [14]:

%sql ALTER TABLE users DROP CONSTRAINT users_pkey

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

Out[14]:

[]

In [15]:

%%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
4 rows affected.

Out[15]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	CHECK	2200_16527_1_not_null
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null

In [16]:

%sql ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY (user_id)

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

Out[16]:

[]

In [17]:

%%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[17]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pk
itversity_retail_db	users	CHECK	2200_16527_1_not_null
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null

In [18]:

%sql ALTER TABLE users ADD UNIQUE (user_email_id)

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

Out[18]:

[]

In [19]:

%%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[19]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pk
itversity_retail_db	users	UNIQUE	users_user_email_id_key
itversity_retail_db	users	CHECK	2200_16527_1_not_null
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null

In [20]:

%%sql

ALTER TABLE users
    ALTER COLUMN user_email_validated SET NOT NULL, 
    ALTER COLUMN user_role SET NOT NULL, 
    ALTER COLUMN created_dt SET NOT NULL, 
    ALTER COLUMN last_updated_ts SET NOT NULL

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

Out[20]:

[]

In [21]:

%%sql

ALTER TABLE users
    ADD CHECK (user_role IN ('U', 'A') )

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

Out[21]:

[]

In [22]:

%%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
11 rows affected.

Out[22]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	users	PRIMARY KEY	users_pk
itversity_retail_db	users	UNIQUE	users_user_email_id_key
itversity_retail_db	users	CHECK	users_user_role_check
itversity_retail_db	users	CHECK	2200_16527_1_not_null
itversity_retail_db	users	CHECK	2200_16527_2_not_null
itversity_retail_db	users	CHECK	2200_16527_3_not_null
itversity_retail_db	users	CHECK	2200_16527_4_not_null
itversity_retail_db	users	CHECK	2200_16527_5_not_null
itversity_retail_db	users	CHECK	2200_16527_7_not_null
itversity_retail_db	users	CHECK	2200_16527_9_not_null
itversity_retail_db	users	CHECK	2200_16527_10_not_null

In [23]:

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

Out[23]:

[]

In [24]:

%%sql 

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

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

Out[24]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	user_logins	PRIMARY KEY	user_logins_pkey
itversity_retail_db	user_logins	CHECK	2200_16550_1_not_null

In [25]:

%%sql

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

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

Out[25]:

[]

In [26]:

%%sql 

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

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

Out[26]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	user_logins	PRIMARY KEY	user_logins_pkey
itversity_retail_db	user_logins	FOREIGN KEY	user_logins_user_id_fkey
itversity_retail_db	user_logins	CHECK	2200_16550_1_not_null

{error}
This will fail as there is a child table user_logins for users table.

In [27]:

%%sql

DROP TABLE users

 * 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 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)
Input In [27], in <cell line: 1>()
----> 1 get_ipython().run_cell_magic('sql', '', '\nDROP TABLE users\n')

File ~/.local/lib/python3.8/site-packages/IPython/core/interactiveshell.py:2338, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2336 with self.builtin_trap:
   2337     args = (magic_arg_s, cell)
-> 2338     result = fn(*args, **kwargs)
   2339 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 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: https://sqlalche.me/e/14/2j85)

{note}
You can use `CASCADE` to drop foreign key constraints from child tables before dropping the table users.

In [29]:

%%sql

DROP TABLE users CASCADE

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

Out[29]:

[]

In [30]:

%%sql 

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

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

Out[30]:

table_catalog	table_name	constraint_type	constraint_name
itversity_retail_db	user_logins	PRIMARY KEY	user_logins_pkey
itversity_retail_db	user_logins	CHECK	2200_16550_1_not_null

In [31]:

%sql DROP TABLE IF EXISTS user_logins

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

Out[31]:

[]

Durga Gadiraju

Managing Constraints in PostgreSQL

Managing Constraints¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email