Database Essentials using Postgres - Creating Tables and Indexes - 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.

  1. Use the prior users table with out any constraints.

  2. Add primary key constraint on user_id.

  3. Add unique constraint on user_email_id.

  4. Add not null constraints user_email_validated, user_role, created_dt, last_updated_ts.

  5. Add check constraint to user_role with ā€˜Uā€™ and ā€˜Aā€™ as accepted values.

  6. 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@pg.itversity.com:5432/itversity_retail_db
%sql DROP TABLE IF EXISTS users
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
%%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
)
%sql CREATE SEQUENCE users_user_id_seq
%sql ALTER TABLE users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq')
%%sql

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

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

ALTER TABLE users
    ADD COLUMN last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
%%sql 

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%sql ALTER TABLE users ADD PRIMARY KEY (user_id)
%%sql 

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%sql ALTER TABLE users DROP CONSTRAINT users_pkey
%%sql 

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%sql ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY (user_id)
%%sql 

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%sql ALTER TABLE users ADD UNIQUE (user_email_id)
%%sql 

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%%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
%%sql

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

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'users'
%%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)
)
%%sql 

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

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

SELECT table_catalog,
    table_name,
    constraint_type,
    constraint_name 
FROM information_schema.table_constraints

[Watch the video tutorial here](https://www.youtube.com/watch?v=uzbyTZN9RRk)