SQL CREATE TABLE
beginnerbasics
2 min read
What is CREATE TABLE?
CREATE TABLE defines a new table in the database. You specify the table name, columns, data types, and constraints.
Syntax
CREATE TABLE table_name (
column_name data_type [constraints],
...
);When to Use
- Setting up a new application database
- Adding a table for a new feature
- Creating temporary or staging tables
Key Points
- Primary Key — Every table should have a primary key. Use
SERIALorGENERATED ALWAYS AS IDENTITYfor auto-incrementing IDs. - Constraints — Use
NOT NULL,UNIQUE,CHECK,DEFAULT, andREFERENCESto enforce data integrity. - IF NOT EXISTS — Adding
IF NOT EXISTSprevents an error if the table already exists. - Foreign Keys — Use
REFERENCES other_table(column)to create relationships between tables. - Naming Conventions — Use lowercase, snake_case names for tables and columns in PostgreSQL.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL CREATE TABLE Challenge
Write a query that solve this task: define a table to store student information.
Expected result
Table students is created with five columns and relevant constraints.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is CREATE TABLE?
Initializing database...Each run starts from fresh sample data.
More Examples
Create a table with a foreign key
Create an enrollments table that references students and courses.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between SERIAL and INTEGER?
SERIAL is shorthand for an INTEGER column with a sequence that auto-generates unique values. INTEGER alone requires you to supply values manually.
Can I create a table from a query?
Yes. Use CREATE TABLE new_table AS SELECT ... FROM existing_table; to copy both structure and data.