SQL CREATE TABLE Statement

What Is the SQL CREATE TABLE Statement?

The CREATE TABLE statement is used to define a new table in a database. It lets you specify column names, data types, and constraints like primary keys or NOT NULL requirements.

Basic Syntax

Real-World Example: Creating the TokTuk Tables

Let's look at how we created the tables for our TokTuk social media platform:

Users Table

This table stores user account information:

Videos Table

This table stores information about uploaded videos:

Interactions Table

This table tracks user interactions with videos:

Understanding Constraints

PRIMARY KEY

Uniquely identifies each row in a table. In our examples, user_id, video_id, and interaction_id are primary keys.

FOREIGN KEY

Creates a relationship between tables. For example, videos.user_id references users.user_id.

NOT NULL

Ensures a column cannot contain NULL values. Used for required fields like username and email.

UNIQUE

Ensures all values in a column are distinct. Used for email to prevent duplicate accounts.

DEFAULT

Sets a default value when no value is specified. For example, views starts at 0.

Best Practices for CREATE TABLE

Always define a primary key for each table
Use appropriate data types for each column
Add foreign key constraints to maintain referential integrity
Use NOT NULL for required fields
Add UNIQUE constraints where appropriate
Consider adding indexes for frequently queried columns
Use clear, descriptive column names
Document your schema design decisions

Interview Master

SQL Learning Hub