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

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.

    • Adding columns to existing tables

    • Dropping columns from existing tables

    • Changing data types of existing columns

  • While creating tables in RDBMS databases, we should specify data types for the columns.

    • SERIAL is nothing but integer which is populated by a special database object called as sequence. It is typically used for surrogate primary key.

    • VARCHAR with length is used to define columns such as name, email id etc.

    • BOOLEAN is used to store true and false values.

    • We can also use DATE or TIMESTAMP to store date or time respectively.

  • We can specify default values, not null constraints as well as check constraints to the columns while creating table or adding columns using ALTER TABLE.

  • Constraints can either be added as part of CREATE TABLE statement or using ALTER TABLE for pre-created tables.

%load_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:itversity!23@localhost:5433/itversity_sms_db

%sql DROP TABLE users

%%sql result_set <<

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

%sql SELECT * FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position

%sql ALTER TABLE users ADD last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

%sql SELECT * FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position

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

%sql ALTER TABLE users ADD UNIQUE (user_email_id)
Create Database and Users Table DML – Data Manipulation Language