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,UNIQUEetc)
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
ALTERcommand.- 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.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%sql DROP TABLE IF EXISTS users
Done.
Out[3]:
[]
In [4]:
%%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:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[4]:
[]
In [5]:
%sql COMMENT ON TABLE users IS 'Stores all user details'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[5]:
[]
In [6]:
%sql COMMENT ON COLUMN users.user_id IS 'Surrogate Key'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[6]:
[]
In [7]:
%sql COMMENT ON COLUMN users.user_first_name IS 'User First Name'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[7]:
[]
In [8]:
%sql COMMENT ON COLUMN users.user_role IS 'U for user A for admin'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db Done.
Out[8]:
[]
In [9]:
%%sql
SELECT * FROM information_schema.tables
WHERE table_name = 'users'
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[9]:
| 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 |
In [10]:
%%sql
SELECT * FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[10]:
| 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 |