Database Essentials using Postgres - Creating Tables and Indexes - Dropping Tables

Databases Postgres
1

Description Paragraph

This article will guide you on how to drop tables in PostgreSQL using the DROP TABLE command. You will learn about the dependencies that are automatically dropped, dealing with child tables using CASCADE, and dropping foreign key constraints. Hands-on tasks will be provided to help you practice the concepts discussed.

Explanation for the video

[Replace this text with a placeholder for the video]

Key Concepts Explanation

Dropping Tables with DROP TABLE

To drop a table in PostgreSQL, you can use the DROP TABLE command. When you drop a table, all direct dependent objects such as indexes, primary key constraints, unique constraints, and not null constraints will be automatically dropped.

DROP TABLE table_name;

Using CASCADE for Child Tables

If there are child tables dependent on the table you want to drop, you can specify CASCADE to drop both the foreign key constraints and the dependent objects. This won’t drop the child tables themselves.

DROP TABLE table_name CASCADE;

Hands-On Tasks

  1. Use DROP TABLE to drop the ‘users’ table.
  2. Perform an insert operation on the ‘users’ and ‘user_logins’ tables.
  3. Drop the ‘users’ table with the CASCADE option.
  4. Check the status of the tables and sequences using information_schema views.
  5. Verify the data in the ‘user_logins’ table.

Conclusion

In this article, we discussed how to drop tables in PostgreSQL using the DROP TABLE command. By understanding the dependencies and options like CASCADE, you can effectively manage your database. Practice the hands-on tasks provided to reinforce your learning.

Feel free to watch the accompanying video for a visual walkthrough of the concepts discussed. Join the community to ask questions or engage with other learners.

Dropping Tables

%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
%sql DROP TABLE IF EXISTS user_logins
%sql DROP TABLE IF EXISTS users
%sql DROP SEQUENCE IF EXISTS users_user_id_seq
%%sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    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 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

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

SELECT * FROM information_schema.tables
WHERE table_name IN ('users', 'user_logins')
%%sql

SELECT * FROM information_schema.sequences
WHERE sequence_name = 'users_user_id_seq'
%%sql

SELECT * FROM information_schema.sequences
WHERE sequence_name = 'user_logins_user_login_id_seq'

Watch the video tutorial here