SQL Create Table

SQL CREATE TABLE

The CREATE TABLE statement is used to create a new table in a database. A table is defined by its columns, data types, and optional constraints.

Basic Syntax

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype
);

Example: Create a Customers Table

CREATE TABLE customers (
  id INT,
  name VARCHAR(100),
  email VARCHAR(150),
  country VARCHAR(50)
);

Common Data Types

• INT – integer numbers
• DECIMAL(p,s) – exact numeric values (money, measurements)
• VARCHAR(n) – variable-length text
• TEXT – longer text
• DATE – date only (YYYY-MM-DD)
• DATETIME – date and time
• BOOLEAN – true/false (implementation varies)

Primary Key

A primary key uniquely identifies each row in a table.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150)
);

AUTO_INCREMENT (MySQL / MariaDB)

AUTO_INCREMENT automatically generates numeric ids for new rows.

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150)
);

NOT NULL and UNIQUE

NOT NULL requires a value. UNIQUE prevents duplicate values.

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE
);

DEFAULT Values

DEFAULT provides a value when none is specified during INSERT.

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status VARCHAR(20) DEFAULT 'active'
);

Foreign Key (Related Tables)

Foreign keys connect tables (covered in detail later). Example: orders references customers.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Best Practices

• Use meaningful table and column names
• Choose appropriate data types (avoid storing numbers as text)
• Always define a primary key
• Add indexes for frequently searched columns

Common Mistakes

• Creating tables without a primary key
• Using overly large VARCHAR sizes without need
• Forgetting NOT NULL for required columns

Next Step

Continue with SQL DROP TABLE to learn how to remove tables safely.