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.
%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 |