SQL DDL Commands: Creating Tables

Understanding the CREATE TABLE Command

The CREATE TABLE command allows you to define a new table by specifying its name, columns, data types, and optional constraints.

Each column in the table must have a name and a data type that determines the kind of data it can hold.

• Syntax: CREATE TABLE table_name (column1 datatype [constraints], column2 datatype [constraints], ...);
• Common data types include INT, VARCHAR, DATE, and BOOLEAN.
• Constraints such as PRIMARY KEY, NOT NULL, and UNIQUE enforce data rules.

Defining Columns and Data Types

Choosing the right data type for each column is crucial for data accuracy and storage efficiency.

Common data types include numeric types (INT, FLOAT), character types (CHAR, VARCHAR), date/time types (DATE, TIMESTAMP), and more.

• INT: Integer numbers.
• VARCHAR(n): Variable-length string up to n characters.
• DATE: Date values (year, month, day).
• BOOLEAN: True or false values.

Using Constraints to Enforce Data Integrity

Constraints define rules that data must follow to maintain accuracy and consistency.

They can be applied at the column level or table level.

• PRIMARY KEY: Uniquely identifies each row.
• NOT NULL: Ensures a column cannot have NULL values.
• UNIQUE: Ensures all values in a column are distinct.
• FOREIGN KEY: Enforces referential integrity between tables.

Practical Example: Creating a Customer Table

Let's create a Customer table with columns for ID, Name, Email, and Registration Date.

We will include constraints to ensure data validity.