CREATE TABLE Statement
SQL · Working with Tables
Theory
CREATE TABLE defines a new, empty table: its name, each column's name and data type, and the rules (constraints) the data must follow. Common types are INT for whole numbers, DECIMAL(10,2) for money with exact decimals, VARCHAR(n) for text up to n characters, DATE and TIMESTAMP for time values, and BOOLEAN. Constraints such as PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT and FOREIGN KEY can be written next to a column or as separate lines at the end.
The table holds no rows until you INSERT them. Running CREATE TABLE for a name that already exists fails, so many engines support CREATE TABLE IF NOT EXISTS. You can also create a table from a query with CREATE TABLE new_t AS SELECT ..., which copies the data and column types but usually not the constraints. Type names and limits differ by engine, so check your database's documentation.
Why we use it
A database needs to know the shape of your data before it can store it. CREATE TABLE fixes the columns, types and rules up front, so bad data is rejected and every query can rely on a known structure.
When to use it
Use CREATE TABLE when designing a new entity (customers, orders) or a staging table for loads. Use CREATE TABLE AS SELECT for quick copies, and ALTER TABLE to change a table that already exists.
How it works
- Choose a table name
- List each column with a data type
- Add column rules: NOT NULL, DEFAULT, UNIQUE, CHECK
- Add table rules: PRIMARY KEY, FOREIGN KEY
- Name already exists? Yes: Error (or skip with IF NOT EXISTS). No: Create the empty table.
- Table is ready for INSERT
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype
);Example
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2)
);Key points
- Define constraints like PRIMARY KEY inline or at the end
- Data types vary slightly by database engine
- Use DECIMAL, not FLOAT, for money
- CREATE TABLE IF NOT EXISTS avoids errors on re-runs
- CREATE TABLE ... AS SELECT copies data but usually not constraints