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 the table is created.
Key Concept 1
Explanation of the key concept 1 with inline code examples
CREATE TABLE new_table (
column1 INT,
column2 VARCHAR(50)
);
Key Concept 2
Explanation of the key concept 2 with inline code examples
ALTER TABLE users ADD COLUMN date_of_birth DATE;
Hands-On Tasks
Description of the hands-on tasks. Provide a list of tasks that the reader can perform to apply the concepts discussed in the article.
- Create a new table with columns specified.
- Add a new column ‘date_of_birth’ to the ‘users’ table.
Conclusion
Summary of the main points discussed in the article. Encourage the reader to practice or engage with the community for further learning.