CodeRang
SQL topics

SQL CREATE TABLE: Syntax and Schema Design

SQL CREATE TABLE creates a table and defines the rules its rows must follow. This example shows how columns, keys, relationships, and constraints fit together in a practical schema.

Updated · SQL Arena team

SQL for backend developers trackAll problems

What you will practice

  • Choose column types and define a PRIMARY KEY
  • Link tables with FOREIGN KEY and reason about ON DELETE
  • Use UNIQUE, CHECK, NOT NULL, and DEFAULT correctly
  • Inspect constraints through information_schema
  • Modify an existing schema with deliberate DDL changes

A practice problem in the trainer

This is how a problem looks in SQL Arena: the task, the editor, the result and the database schema.

PrevNextRandom
TaskSubmissionsAI Mentor
Inspect the constraints on employees

The database contains departments and employees. The employees table has a primary key, a foreign key to departments, a unique email rule, and a non-negative salary check. Return constraint_name, constraint_type, and column_name, ordered by the constraint name.

Query 1PostgreSQLv16
SELECT tc.constraint_name, tc.constraint_type, kcu.column_name
FROM information_schema.table_constraints AS tc
LEFT JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.constraint_schema = kcu.constraint_schema
WHERE tc.table_schema = 'public' AND tc.table_name = 'employees'
  AND tc.constraint_name NOT LIKE '%_not_null'
ORDER BY tc.constraint_name;
Submit
Resultdepartmentsemployees
Answer accepted

How an SQL CREATE TABLE Query Works

Start with the parent table. departments uses id as its PRIMARY KEY, so each department has a stable identifier. In employees, department_id references departments(id). ON DELETE RESTRICT means a department cannot be removed while employee rows still point to it. That clause is part of the data model, not just extra syntax.

The child table combines several protections. PRIMARY KEY identifies each row. UNIQUE makes email a separate business key. CHECK (salary >= 0) rejects negative salaries, NOT NULL marks required values, and DEFAULT 0 supplies a value when salary is omitted. Schema design exercises are useful because they force you to choose the right rule for each requirement.

The verification query reads information_schema.table_constraints and LEFT JOINs key_column_usage. The first view reports constraint names and types; the second provides related columns. LEFT JOIN matters because a CHECK constraint may have no matching key-column row. The filter keeps public.employees, and ORDER BY makes the result deterministic.

This is a useful habit after CREATE TABLE or ALTER TABLE. Query the catalog and verify the actual keys, types, and names instead of assuming the final schema matches the migration.

CREATE TABLE creates a new table and defines its columns, data types, keys, defaults, and constraints. A typical create table query in SQL includes a table name followed by column definitions inside parentheses. PRIMARY KEY identifies rows, FOREIGN KEY preserves relationships, UNIQUE prevents duplicate values, and CHECK enforces a condition. ALTER TABLE is used later when an existing schema must change, for example to add or rename a column or add a constraint. Exact ALTER TABLE syntax can vary between SQL dialects.

SQLPostgreSQL v16
SELECT tc.constraint_name, tc.constraint_type, kcu.column_name
FROM information_schema.table_constraints AS tc
LEFT JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.constraint_schema = kcu.constraint_schema
WHERE tc.table_schema = 'public' AND tc.table_name = 'employees'
  AND tc.constraint_name NOT LIKE '%_not_null'
ORDER BY tc.constraint_name;
Query result
constraint_nameconstraint_typecolumn_name
employees_department_fkFOREIGN KEYdepartment_id
employees_email_keyUNIQUEemail
employees_pkeyPRIMARY KEYid
employees_salary_checkCHECKNULL

Common SQL CREATE TABLE Mistakes

01

Adding a FOREIGN KEY without choosing delete behavior

A relationship is incomplete if you have not thought through what happens when the parent row is deleted. RESTRICT, CASCADE, and SET NULL encode different business rules and can have very different consequences.

02

Treating CHECK as a replacement for NOT NULL

A CHECK expression is for validating a rule about a value. If a column is required, say so with NOT NULL as well; otherwise NULL handling may not match what you intended.

03

Reading the migration instead of the real schema

After several ALTER TABLE statements, the live structure may differ from what you remember. information_schema queries let you inspect what the database actually has.

Practice problems on this topic

SQL for backend developers track

SQL Arena includes DDL exercises on dropping or renaming columns, creating indexes, changing data types, building views, and adding required fields with defaults. Easy exercises are free; harder ones require Premium. For a broader backend path, continue with the “SQL for Backend Developers” track. SQL Arena has 1,192 auto-checked tasks and separate browser sandboxes for PostgreSQL, MySQL, ClickHouse, and BigQuery. Solutions are checked on hidden data. ER diagrams help inspect relationships, and EXPLAIN is available when schema work intersects with indexes.

Reference on this topic

In-depth articles with syntax and examples in the SQL Arena reference.

Frequently asked questions

What is SQL CREATE TABLE?

SQL CREATE TABLE creates a new table in a database. The statement defines its columns, data types, and optional constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

How do you write a CREATE TABLE query in SQL?

Start with CREATE TABLE and the table name, then put the column definitions inside parentheses. Each column normally has a name and data type, followed by any required constraints or default values.

How do you create a table with a primary key in SQL?

Define a PRIMARY KEY on the column or columns that uniquely identify each row. For a single column, it can often be written directly after the column definition or as a separate table-level constraint.

How do you create a table with a foreign key in SQL?

Define a FOREIGN KEY that references a candidate key, commonly a primary key, in another table. This establishes a relationship and lets the database enforce referential integrity.

What is the difference between CREATE TABLE and ALTER TABLE?

CREATE TABLE defines a new table from scratch. ALTER TABLE changes the structure of a table that already exists, such as by adding a column or constraint.

See also

Practice SQL CREATE TABLE Queries

Start with free Easy DDL exercises, then move to harder work on constraints, indexes, and schema changes. No card is required; 292 free tasks are free across all four SQL dialects.

Practice DDL in SQL Arena

More practice from CodeRang