DDL – Data Definition LanguageΒΆ

Let us get an overview of DDL Statements which are typically used to create database objects such as tables.

  • DDL Stands for Data Definition Language.

  • We execute DDL statements less frequently as part of the application development process.

  • Typically DDL Scripts are maintained separately than the code.

  • Following are the common DDL tasks.

    • Creating Tables - Independent Objects

    • Creating Indexes for performance - Typically dependent on tables

    • Adding constraints to existing tables (NOT NULL, CHECK, PRIMARY KEY, UNIQUE etc)

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 DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • Following are less common DDL tasks which can be taken care using ALTER command.

    • Adding columns to existing tables.

    • Dropping columns from existing tables.

    • Changing data types of existing columns.

  • We can also define comments both at column level as well as table level. However in postgres, we can only add comments after table is created.

%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
Done.
[]
%%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 DEFAULT FALSE,
    user_password VARCHAR(200),
    user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A
    is_active BOOLEAN DEFAULT FALSE,
    created_dt DATE DEFAULT CURRENT_DATE,
    last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql COMMENT ON TABLE users IS 'Stores all user details'
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql COMMENT ON COLUMN users.user_id IS 'Surrogate Key'
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql COMMENT ON COLUMN users.user_first_name IS 'User First Name'
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%sql COMMENT ON COLUMN users.user_role IS 'U for user A for admin'
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql

SELECT * FROM information_schema.tables 
WHERE table_name = 'users'
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
table_catalog table_schema table_name table_type self_referencing_column_name reference_generation user_defined_type_catalog user_defined_type_schema user_defined_type_name is_insertable_into is_typed commit_action
itversity_retail_db public users BASE TABLE None None None None None YES NO None
%%sql 

SELECT * FROM information_schema.columns 
WHERE table_name = 'users'
ORDER BY ordinal_position
 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable
itversity_retail_db public users user_id 1 nextval('users_user_id_seq'::regclass) NO integer None None 32 2 0 None None None None None None None None None None None None itversity_retail_db pg_catalog int4 None None None None 1 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_first_name 2 None NO character varying 30 120 None None None None None None None None None None None None None None None itversity_retail_db pg_catalog varchar None None None None 2 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_last_name 3 None NO character varying 30 120 None None None None None None None None None None None None None None None itversity_retail_db pg_catalog varchar None None None None 3 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_email_id 4 None NO character varying 50 200 None None None None None None None None None None None None None None None itversity_retail_db pg_catalog varchar None None None None 4 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_email_validated 5 false YES boolean None None None None None None None None None None None None None None None None None itversity_retail_db pg_catalog bool None None None None 5 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_password 6 None YES character varying 200 800 None None None None None None None None None None None None None None None itversity_retail_db pg_catalog varchar None None None None 6 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users user_role 7 'U'::character varying NO character varying 1 4 None None None None None None None None None None None None None None None itversity_retail_db pg_catalog varchar None None None None 7 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users is_active 8 false YES boolean None None None None None None None None None None None None None None None None None itversity_retail_db pg_catalog bool None None None None 8 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users created_dt 9 CURRENT_DATE YES date None None None None None 0 None None None None None None None None None None None itversity_retail_db pg_catalog date None None None None 9 NO NO None None None None None NO NEVER None YES
itversity_retail_db public users last_updated_ts 10 CURRENT_TIMESTAMP YES timestamp without time zone None None None None None 6 None None None None None None None None None None None itversity_retail_db pg_catalog timestamp None None None None 10 NO NO None None None None None NO NEVER None YES