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.

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

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

[]

%%sql

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

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

[]

%%sql

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

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

[]

%%sql

ALTER TABLE users
    ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP    

 * 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_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null 
%sql ALTER TABLE users 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_17328_1_not_null
itversity_retail_db users CHECK 2200_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null 
%sql ALTER TABLE users DROP CONSTRAINT users_pkey

 * 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
4 rows affected.
table_catalog table_name constraint_type constraint_name
itversity_retail_db users CHECK 2200_17328_1_not_null
itversity_retail_db users CHECK 2200_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null 
%sql ALTER TABLE users ADD CONSTRAINT users_pk 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_pk
itversity_retail_db users CHECK 2200_17328_1_not_null
itversity_retail_db users CHECK 2200_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null 
%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_pk
itversity_retail_db users UNIQUE users_user_email_id_key
itversity_retail_db users CHECK 2200_17328_1_not_null
itversity_retail_db users CHECK 2200_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null 
%%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:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

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

 * 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
11 rows affected.
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_17328_1_not_null
itversity_retail_db users CHECK 2200_17328_2_not_null
itversity_retail_db users CHECK 2200_17328_3_not_null
itversity_retail_db users CHECK 2200_17328_4_not_null
itversity_retail_db users CHECK 2200_17328_5_not_null
itversity_retail_db users CHECK 2200_17328_7_not_null
itversity_retail_db users CHECK 2200_17328_9_not_null
itversity_retail_db users CHECK 2200_17328_10_not_null 
%%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 

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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.
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_17351_1_not_null 
%%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 table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'user_logins'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
3 rows affected.
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_17351_1_not_null

Error

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

%%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-60-a1fbf34721c3> in <module>
----> 1 get_ipython().run_cell_magic('sql', '', '\nDROP TABLE users\n')

/opt/anaconda3/envs/beakerx/lib/python3.6/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2369             with self.builtin_trap:
   2370                 args = (magic_arg_s, cell)
-> 2371                 result = fn(*args, **kwargs)
   2372             return result
   2373 

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

Note

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

%%sql

DROP TABLE users CASCADE

 * 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 = 'user_logins'

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
2 rows affected.
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_17351_1_not_null 
%sql DROP TABLE IF EXISTS user_logins

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

[]
Different Types of Constraints Indexes on Tables