Academic Block

Academic Block logo
Menu
SQL CREATE TABLE
Learn how to create SQL tables, define columns and data types, set constraints, and insert data into newly created tables.

What is CREATE TABLE?

The CREATE TABLE statement is used to create a new table in a database. A table stores data in rows and columns, where each column represents a particular type of information and each row represents a record.

When creating a table, you define the column names, data types, and optional constraints such as PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT.

Basic CREATE TABLE Syntax

The basic syntax for creating a table is: 

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

Creating a Simple Table

The following SQLite-compatible example creates a simple students table. 

DROP TABLE IF EXISTS students;

CREATE TABLE students (
  student_id INTEGER,
  student_name TEXT,
  age INTEGER,
  course TEXT
);

INSERT INTO students VALUES
  (1, 'Aarav', 21, 'Physics'),
  (2, 'Meera', 22, 'Chemistry'),
  (3, 'Kabir', 20, 'Mathematics');

SELECT *
FROM students
ORDER BY student_id;

CREATE TABLE with PRIMARY KEY

A PRIMARY KEY uniquely identifies each row in a table. In SQLite, an INTEGER PRIMARY KEY column can automatically receive integer row identifiers when a value is not supplied. 

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  price REAL
);

INSERT INTO products (product_name, price) VALUES
  ('Wireless Mouse', 899.00),
  ('Laptop Stand', 1499.00),
  ('USB Hub', 799.00);

SELECT *
FROM products
ORDER BY product_id;

CREATE TABLE with NOT NULL

The NOT NULL constraint prevents a column from containing a NULL value. 

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary REAL
);

INSERT INTO employees
  (employee_name, department, salary)
VALUES
  ('Anaya', 'Engineering', 72000),
  ('Rohan', 'Design', 68000),
  ('Tara', 'Marketing', 64000);

SELECT *
FROM employees
ORDER BY employee_id;

CREATE TABLE with UNIQUE

The UNIQUE constraint prevents duplicate values in a column. 

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  username TEXT UNIQUE,
  email TEXT UNIQUE
);

INSERT INTO users (username, email) VALUES
  ('aarav01', 'aarav@example.com'),
  ('meera22', 'meera@example.com'),
  ('kabir88', 'kabir@example.com');

SELECT *
FROM users
ORDER BY user_id;

CREATE TABLE with DEFAULT

The DEFAULT constraint supplies a value automatically when an INSERT statement does not provide one. 

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
  task_id INTEGER PRIMARY KEY,
  task_name TEXT NOT NULL,
  status TEXT DEFAULT 'Pending'
);

INSERT INTO tasks (task_name) VALUES
  ('Design homepage'),
  ('Write documentation'),
  ('Test search feature');

SELECT *
FROM tasks
ORDER BY task_id;

CREATE TABLE with Multiple Constraints

A table can use several constraints together to improve data integrity. 

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  account_name TEXT NOT NULL,
  account_email TEXT UNIQUE NOT NULL,
  balance REAL DEFAULT 0
);

INSERT INTO accounts
  (account_name, account_email)
VALUES
  ('Nisha', 'nisha@example.com'),
  ('Dev', 'dev@example.com');

SELECT *
FROM accounts
ORDER BY account_id;

CREATE TABLE IF NOT EXISTS

IF NOT EXISTS prevents an error when the table already exists. SQLite supports this syntax. 

DROP TABLE IF EXISTS departments;

CREATE TABLE IF NOT EXISTS departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT NOT NULL
);

INSERT INTO departments (department_name) VALUES
  ('Engineering'),
  ('Research'),
  ('Operations');

SELECT *
FROM departments
ORDER BY department_id;

CREATE TABLE with CHECK

The CHECK constraint ensures that inserted or updated values satisfy a specified condition. 

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  course_name TEXT NOT NULL,
  duration INTEGER CHECK (duration > 0)
);

INSERT INTO courses (course_name, duration) VALUES
  ('SQL Basics', 20),
  ('Web Design', 35),
  ('Data Analysis', 45);

SELECT *
FROM courses
ORDER BY course_id;

CREATE TABLE with Foreign Key

A FOREIGN KEY establishes a relationship between columns in different tables. 

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES
  (1, 'Isha'),
  (2, 'Karan');

INSERT INTO orders VALUES
  (101, 1, 2400.00),
  (102, 2, 3150.00);

SELECT
  orders.order_id,
  customers.customer_name,
  orders.amount
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id
ORDER BY orders.order_id;

Creating a Table from Another Table

SQLite does not support the SQL Server-style SELECT INTO syntax. Instead, SQLite can create a new table from query results using CREATE TABLE AS SELECT. 

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS engineering_staff;

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees VALUES
  (1, 'Aarav', 'Engineering', 78000),
  (2, 'Meera', 'Design', 69000),
  (3, 'Kabir', 'Engineering', 82000),
  (4, 'Riya', 'Marketing', 65000);

CREATE TABLE engineering_staff AS
SELECT
  employee_id,
  employee_name,
  salary
FROM employees
WHERE department = 'Engineering';

SELECT *
FROM engineering_staff
ORDER BY employee_id;

Viewing Table Structure

SQLite provides PRAGMA table_info() to inspect the columns and structure of a table. 

DROP TABLE IF EXISTS vehicles;

CREATE TABLE vehicles (
  vehicle_id INTEGER PRIMARY KEY,
  brand TEXT NOT NULL,
  model TEXT NOT NULL,
  year INTEGER
);

SELECT
  cid,
  name,
  type,
  "notnull",
  dflt_value,
  pk
FROM pragma_table_info('vehicles')
ORDER BY cid;

Common CREATE TABLE Constraints

Constraint Purpose Example
PRIMARY KEY Uniquely identifies rows id INTEGER PRIMARY KEY
NOT NULL Prevents NULL values name TEXT NOT NULL
UNIQUE Prevents duplicate values email TEXT UNIQUE
DEFAULT Supplies a default value status TEXT DEFAULT ‘Pending’
CHECK Validates a condition age INTEGER CHECK(age > 0)
FOREIGN KEY Connects related tables FOREIGN KEY (user_id)

Common Data Types

Data Type Typical Use Example
INTEGER Whole numbers age INTEGER
REAL Decimal numbers price REAL
TEXT Text and strings name TEXT
BLOB Binary data file_data BLOB

Important Points

  • CREATE TABLE creates a new table in a database.
  • Every column should have a name and an appropriate data type.
  • PRIMARY KEY can uniquely identify each record.
  • NOT NULL prevents missing values in required columns.
  • UNIQUE prevents duplicate values.
  • DEFAULT supplies a value when one is not provided.
  • CHECK can enforce conditions on inserted or updated data.
  • FOREIGN KEY can establish relationships between tables.

Best Practices

  • Give tables and columns clear, meaningful names.
  • Choose appropriate data types for each column.
  • Use a primary key when each row needs a unique identifier.
  • Use constraints to protect data integrity.
  • Avoid unnecessary columns and duplicated information.
  • Design relationships between tables carefully before inserting large amounts of data.
  • Use IF NOT EXISTS when appropriate to avoid errors during repeated setup.

The CREATE TABLE statement is one of the most important SQL commands because it defines the structure in which database records are stored. By combining columns, data types, and constraints, you can create reliable and well-organized tables.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code
👁️ Preview (query result)

Click “Run Code” to see the result here.