SQL - CREATE Table

To create an SQL table, you must provide a name, followed by a list of columns. For each column, you must define its Data Type. Let's see a sample command

sql

CREATE TABLE users (
    user_id INT,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at DATE
);

Existence Checks

If you attempt to create or add a table that already exist within a specific container(Database) will cause an error. To make your scripts "re-runnable" without crashing, always use the IF NOT EXISTS clause.

sql

CREATE TABLE IF NOT EXISTS users (
    -- column definitions
);

Column Data Typesclause

In SQL, every column must have a "Type." This tells the server how to store the data and what operations it can perform.

Type	Purpose	Example
INT	Whole numbers	25, 1000
VARCHAR(N)	Variable-length text (up to N characters)	'John Doe'
DECIMAL(P, S)	Exact decimals (Price, Weight)	19.99
BOOLEAN	True or False values	1, 0
DATE / TIMESTAMP	Time-related data	'2024-05-20'

Constraints

A constraint is a condition defined on one or more columns of a table that the database management system automatically checks whenever data is inserted, updated, or deleted, helping maintain consistency and accuracy.

Essential Constraints

Constraint	Purpose	Human Translation
NOT NULL	Prevents empty values.	"You must provide this information."
UNIQUE	Ensures no two rows have the same value.	"No two users can have the same username."
DEFAULT	Provides a fallback value.[5][6]	"If they don't pick a color, make it 'Blue'."
CHECK	Validates data against a condition.	"The age must be 18 or higher."

Identifiers

PRIMARY KEY: The primary key in a table is the most important constraint. It uniquely identifies every single row in your table. It's effectively NOT NULL and UNIQUE combined into one. Every table should have exactly one Primary Key.
FOREIGN KEY: This constraint creates a link between two tables. It ensures that you can't reference a value in one table (like a "Department ID") that doesn't actually exist in another table (the "Departments" table, for example). Think of it as a rule for relationships.

Auto-Generating IDs

Manually assigning a user_id for every new row is a nightmare. Most SQL engines provide a way to "Auto-Increment" a number every time a new row is added.[9]

MySQL: AUTO_INCREMENT
PostgreSQL: SERIAL or GENERATED ALWAYS AS IDENTITY
SQL Server: IDENTITY(1,1)

Optimized Example

see a sample optimized example on how to create a table right.

sql

CREATE TABLE products (
    -- Auto-incrementing ID as the Primary Key
    product_id INT AUTO_INCREMENT,
    
    -- Ensuring we always have a name
    product_name VARCHAR(100) NOT NULL,
    
    -- Unique SKU (Stock Keeping Unit)
    sku_code VARCHAR(20) UNIQUE,
    
    -- Price must be positive
    price DECIMAL(10, 2) CHECK (price > 0),
    
    -- Default to 'Draft' if no status is provided
    status VARCHAR(20) DEFAULT 'Draft',
    
    -- Defining the Primary Key at the Table-level
    PRIMARY KEY (product_id)
);

Summary

The Syntax: CREATE TABLE is the fundamental command used to define the structure of your data, including column names and data types.
Operational Safety: Use the IF NOT EXISTS clause to prevent your scripts from crashing when a table with the same name already exists.
Data Integrity: Constraints like NOT NULL, UNIQUE, and CHECK act as the "laws" of your table, ensuring that only valid and high-quality data is stored.
Primary Key: Every table needs a Primary Key to uniquely identify each row. It is the combination of NOT NULL and UNIQUE.
Foreign Key: This creates a relational link between two tables, ensuring that data in one table correctly references valid data in another.
Default Values: The DEFAULT constraint allows you to specify a fallback value if no data is provided for a specific column.