Topic 2 of 52 · Beginner
CREATE TABLE
Define a new table: its name, columns, data types and constraints.
01CREATE TABLE
Module 02 – Database & Table Management (DDL)
02What is it?
CREATE TABLE is the DDL command that builds a brand-new, empty table. You give the table a name and list every column with its data type and any rules (constraints) it must follow.
After it runs, the table exists in the database with zero rows. INSERT is then used to add data.
03Why do we need it?
Data in a relational database can only live inside tables. Before a company can store customers, orders or payments, someone has to design and create the tables that will hold them.
Choosing the right types and constraints up front stops bad data (text in a salary column, missing names, duplicate IDs) from ever getting in.
04Syntax
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
[table_constraints]
);MySQL 8.0 enforces CHECK constraints from 8.0.16. Older versions parsed but ignored them.
05Parameter explanation
- IF NOT EXISTS
- Optional. Skips creation silently if a table with that name already exists instead of raising an error.
- table_name
- Name of the new table. Must be unique within the database.
- column datatype
- Each column needs a name and a type such as INT, VARCHAR(50), DECIMAL(10,2) or DATE.
- constraints
- Column rules: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT value, CHECK (condition), REFERENCES other_table(col).
- table_constraints
- Rules that span columns, e.g. PRIMARY KEY (a, b) or FOREIGN KEY (dept_id) REFERENCES departments(dept_id).
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.The engine checks that no table called projects already exists.
- 2.It records three columns with their types: project_id (INT), title (VARCHAR(80)) and budget (DECIMAL(12,2)).
- 3.project_id is marked as the primary key, so it must be unique and not NULL.
- 4.title gets a NOT NULL rule; budget gets a default of 0 when no value is supplied.
- 5.The empty table is saved in the schema. No rows are returned — only a success message.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
A data engineer onboarding a new source system (say, a CRM) first creates a raw/staging table whose columns mirror the source file, e.g. CREATE TABLE stg_crm_contacts (...).
Production tables are usually created through version-controlled migration scripts, so the same CREATE TABLE runs identically in dev, test and production.
12Common mistakes
CREATE TABLE projects (project_id INT, title VARCHAR(80),);
Trailing comma after the last column — the parser expects another column.
CREATE TABLE projects (project_id INT, title VARCHAR(80));
CREATE TABLE projects (project_id, title VARCHAR(80));
In MySQL every column must have a data type.
CREATE TABLE projects (project_id INT, title VARCHAR(80));
CREATE TABLE employees (emp_id INT);
employees already exists, so this fails with 'table already exists'.
CREATE TABLE IF NOT EXISTS employees (emp_id INT); -- or choose a new name
13Important comparisons
| Command | What it does | Data copied? |
|---|---|---|
| CREATE TABLE t (...) | New empty table with columns you define | No |
| CREATE TABLE t AS SELECT ... | New table shaped by a query | Yes, the query rows |
| CREATE TABLE t LIKE src (MySQL) | Empty copy of src's structure incl. indexes | No |
| ALTER TABLE | Changes an existing table | Keeps existing data |
14Practical exercises
Beginner1. Create a table clients with client_id INT primary key and client_name VARCHAR(100) NOT NULL.
Reveal solution (15)
Two columns; the PK makes client_id unique, NOT NULL forces a name.
Beginner2. Create a table cities with city VARCHAR(50) and state VARCHAR(50), then insert ('Hyderabad','Telangana').
Reveal solution (15)
A table without constraints accepts any values of the right type.
Beginner3. Create skills(skill_id INT PRIMARY KEY, skill VARCHAR(40)) only if it does not already exist.
Reveal solution (15)
Running it twice proves IF NOT EXISTS prevents the 'already exists' error.
Intermediate4. Create leaves(leave_id INT PK, emp_id INT, days INT) where days must be between 1 and 30.
Reveal solution (15)
CHECK constraints are enforced in MySQL 8.0.16+.
Intermediate5. Create attendance with a status column that defaults to 'Present'. Insert a row without status and show it.
Reveal solution (15)
Omitted columns take their DEFAULT value.
Intermediate6. Create salaries_history with a composite primary key (emp_id, effective_date).
Reveal solution (15)
The same emp_id can appear many times, but never twice for the same date.
Advanced7. Create assets(asset_id PK, emp_id INT referencing employees, serial VARCHAR(30) UNIQUE NOT NULL).
Reveal solution (15)
Combines FK, UNIQUE and NOT NULL in one design.
Advanced8. Design a staging table stg_orders for a CSV with order_id, customer, amount, order_ts plus an audit column loaded_at defaulting to the current time.
Reveal solution (15)
Staging tables usually avoid strict constraints but add audit columns.
16Key takeaways
- CREATE TABLE defines the structure; it creates no rows.
- Every column needs a data type; constraints guard data quality.
- Use IF NOT EXISTS for re-runnable scripts.
- Composite keys and foreign keys are declared as table constraints at the end.
17Knowledge check
1. What does CREATE TABLE return when it succeeds?
2. Which clause avoids an error if the table already exists?
3. How do you declare a primary key on two columns?
Interview questions
What's the difference between a column constraint and a table constraint?
A column constraint is written next to one column (salary INT NOT NULL). A table constraint is written after all columns and can cover several columns, e.g. PRIMARY KEY (emp_id, effective_date).
Does CREATE TABLE commit an open transaction in MySQL?
Yes. DDL statements cause an implicit commit in MySQL, so they can't be rolled back with ROLLBACK.
Lesson test
Take an 8-question test on “CREATE TABLE” — concepts, output prediction, bug spotting and interview questions.