CREATE TABLE & Data Types
Tables are the core building blocks of a relational database. A table is structured as rows (records) and columns (fields). Before you can store any data, you need to create a table and define its columns along with the type of data each column will hold.
Every column in a table must have a data type that specifies what kind of values it can store. Choosing the right data type ensures data integrity and optimizes storage.
- INT: Whole numbers (e.g., 1, 42, -7)
- VARCHAR(n): Variable-length text up to n characters (e.g., names, emails)
- TEXT: Long text with no practical length limit (e.g., blog posts, descriptions)
- DATE: Dates in YYYY-MM-DD format (e.g., 2026-03-29)
- BOOLEAN: True or false values (stored as 1 or 0 in MySQL)
- DECIMAL(p, s): Precise decimal numbers with p total digits and s decimal places (e.g., prices)
- FLOAT: Approximate decimal numbers (e.g., scientific calculations)
-- Create a simple users table
CREATE TABLE users (
id INT,
username VARCHAR(50),
email VARCHAR(100),
age INT,
bio TEXT,
join_date DATE,
is_active BOOLEAN
);
-- View the table structure
DESCRIBE users;
-- Or use this alternative
SHOW COLUMNS FROM users;- VARCHAR is preferred over CHAR for variable-length strings because it only uses the storage needed. CHAR(50) always stores 50 characters, padding with spaces if needed.
Constraints
Constraints are rules applied to columns that enforce data integrity. They prevent invalid data from being inserted into your tables, ensuring your database remains consistent and reliable.
The most important constraint is the PRIMARY KEY, which uniquely identifies each row in a table. A primary key must be unique and cannot be NULL.
- PRIMARY KEY: Uniquely identifies each row — no duplicates, no NULL values
- NOT NULL: The column must always have a value — it cannot be left empty
- UNIQUE: All values in the column must be different
- DEFAULT: Provides a fallback value when no value is specified during INSERT
- AUTO_INCREMENT: Automatically generates a sequential number for each new row (MySQL)
- CHECK: Ensures values meet a specific condition (e.g., age >= 0)
-- Create a table with proper constraints
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
age INT CHECK (age >= 0 AND age <= 150),
bio TEXT,
join_date DATE DEFAULT (CURRENT_DATE),
is_active BOOLEAN DEFAULT TRUE
);
-- View the table with constraints
DESCRIBE users;- AUTO_INCREMENT is MySQL syntax. In PostgreSQL, use SERIAL instead. In SQLite, INTEGER PRIMARY KEY automatically auto-increments.