SQL CREATE TABLE

The joins-overview lesson back in Module 4 introduced departments, employees' department_id and manager_id, projects, and project_assignments, and promised the real CREATE TABLE syntax would come in this module. This is that lesson, the statements that actually build every table this course has been querying ever since.

Learning Objectives

• Write a CREATE TABLE statement with columns, types, and a primary key.
• Use a FOREIGN KEY to link a table to another, like employees.department_id to departments.id.
• Know how auto-incrementing primary keys differ across every database in this course.

Creating departments

id is the primary key, uniquely identifying each row, AUTO_INCREMENT means the database assigns the next whole number automatically on every insert, no need to specify it manually. VARCHAR(50) is a variable-length text column capped at 50 characters, covered in full in the data types lesson later in this course.

CREATE TABLE departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

Creating employees, With a Foreign Key

FOREIGN KEY (department_id) REFERENCES departments(id) is what formally makes department_id a real foreign key, the database now enforces that any value stored there must match an actual id in departments (or be NULL). manager_id's foreign key points back at employees(id) itself, the formal, enforced version of the self-join relationship from Module 4, a table can absolutely reference itself this way.

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  department_id INT,
  salary DECIMAL(10,2),
  hire_date DATE,
  manager_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Creating projects and project_assignments

project_assignments is the many-to-many linking table from Module 4, its two foreign keys are what actually enforce that every row connects a real employee to a real project, nothing more needed for the basic shape, a composite PRIMARY KEY spanning both columns is covered in Module 8's Constraints.

CREATE TABLE projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE project_assignments (
  employee_id INT,
  project_id INT,
  FOREIGN KEY (employee_id) REFERENCES employees(id),
  FOREIGN KEY (project_id) REFERENCES projects(id)
);

The Finished Schema

All four CREATE TABLE statements above build exactly this: employees pointing at departments through department_id, and project_assignments linking employees to projects. Every join and subquery since Module 4 has been querying this same shape.

Auto-Incrementing IDs by Database

Try It Yourself

Write a CREATE TABLE statement for a new reviews table: id (auto-incrementing primary key), employee_id (foreign key to employees), rating (an integer), on MySQL.

CREATE TABLE reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  rating INT,
  FOREIGN KEY (employee_id) REFERENCES employees(id)
);

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Create tables in dependency order, referenced tables before the tables with foreign keys pointing at them.
• Use consistent, descriptive column names across related tables, department_id on employees clearly points at departments, not some abbreviated or inconsistent name.
• Look up the correct auto-increment syntax for the target database rather than assuming one pattern works everywhere.

Interview Questions

Summary

CREATE TABLE defines a table's columns, types, and keys, FOREIGN KEY formally links one table to another, enforced by the database itself. Auto-incrementing primary key syntax is one of the most fragmented areas across this course's six databases, five genuinely different patterns for the same idea.

What's Next?

DROP TABLE next, removing a table entirely, and what happens when other tables still hold foreign keys pointing at it.