Every lesson so far has used tables that already existed. When you build your own site – a contact form, a booking system, a small shop – you design the tables yourself. The SQL CREATE TABLE statement describes a table’s columns: their names, what type of data they hold and a few basic rules. Good table design at the start saves a lot of fixing later.
The shape of a SQL CREATE TABLE statement
Give the table a name, then list the columns in brackets, separated by commas. Each column has a name, a data type and, optionally, extra rules such as NOT NULL or DEFAULT.
CREATE TABLE Suppliers (
SupplierID INTEGER PRIMARY KEY,
SupplierName TEXT NOT NULL,
City TEXT,
Phone TEXT,
IsActive INTEGER DEFAULT 1,
JoinedOn TEXT DEFAULT CURRENT_DATE
);
INSERT INTO Suppliers (SupplierName, City, Phone)
VALUES ('Nilgiri Tea Estates', 'Coonoor', '0423-2230000'),
('Malabar Spice Co', 'Kozhikode', '0495-2700000');
SELECT * FROM Suppliers;Returns 2 rows with SupplierID 1 and 2, IsActive 1 and JoinedOn set to today's date.
Look at what we did not type in the INSERT: the ID, IsActive and JoinedOn. The database filled them in. INTEGER PRIMARY KEY numbers the rows automatically, and DEFAULT supplies a value whenever the insert leaves a column out. CURRENT_DATE gives today’s date as YYYY-MM-DD text in SQLite.
Phone numbers and PIN codes are stored as text, not numbers. You never do maths with them, and a number column would drop a leading zero – 018956 would become 18956.
Data types: SQLite vs MySQL
This is where SQLite differs most from other databases. SQLite stores every value in one of five storage classes: NULL, INTEGER, REAL, TEXT and BLOB. The type you write for a column is only a preference (called type affinity), so SQLite will happily accept many other type names and map them to these five. MySQL, by contrast, has many precise types and enforces them.
| Kind of data | SQLite | MySQL | SQL Server |
|---|---|---|---|
| Whole numbers | INTEGER | INT, BIGINT | INT, BIGINT |
| Money / exact decimals | INTEGER (paise) or REAL | DECIMAL(10,2) | DECIMAL(10,2) |
| Short text | TEXT | VARCHAR(100) | NVARCHAR(100) |
| Long text | TEXT | TEXT | NVARCHAR(MAX) |
| Date | TEXT (‘YYYY-MM-DD’) | DATE | DATE |
| Date and time | TEXT (‘YYYY-MM-DD HH:MM:SS’) | DATETIME | DATETIME2 |
| True / false | INTEGER (0 or 1) | BOOLEAN (stored as TINYINT(1)) | BIT |
| Auto-numbered ID | INTEGER PRIMARY KEY | INT AUTO_INCREMENT PRIMARY KEY | INT IDENTITY(1,1) PRIMARY KEY |
Because SQLite is flexible, it may store a value that does not match the column’s type. The next example puts a word into an INTEGER column; typeof() shows how each value was actually stored.
CREATE TABLE StockCheck (Qty INTEGER);
INSERT INTO StockCheck VALUES (5), ('7'), ('ten');
SELECT Qty, typeof(Qty) AS StoredAs
FROM StockCheck;Returns 3 rows: 5 integer, 7 integer (the text '7' was converted) and ten text.
MySQL in its default strict mode would reject 'ten' with an error. If you want the same strictness in SQLite (version 3.37 or later), add the word STRICT after the closing bracket: CREATE TABLE StockCheck (Qty INTEGER) STRICT;. Strict tables accept only INT, INTEGER, REAL, TEXT, BLOB and ANY as column types.
AUTOINCREMENT or not?
In SQLite, INTEGER PRIMARY KEY already gives automatic IDs. Adding the AUTOINCREMENT keyword only guarantees that IDs of deleted rows are never reused, and the SQLite documentation recommends avoiding it unless you need that. Do not confuse it with MySQL’s AUTO_INCREMENT (with an underscore), which is what MySQL needs for automatic IDs.
Changing a table with ALTER TABLE
Requirements change. ALTER TABLE modifies an existing table without losing its data. SQLite supports adding a column, renaming a column or the table, and (since version 3.35) dropping a column. MySQL also lets you change a column’s type with MODIFY; in SQLite you would rebuild the table for that.
ALTER TABLE Customers ADD COLUMN Phone TEXT;
UPDATE Customers
SET Phone = '0413-2220000'
WHERE CustomerID = 2;
SELECT CustomerName, City, Phone
FROM Customers
LIMIT 3;Returns 3 rows. Bay Side Cafe has the new phone number; the other rows show NULL in Phone.
Copying data into a new table, and DROP TABLE
CREATE TABLE … AS SELECT builds a table from a query result – handy for a quick backup or report table. It copies columns and data, but not keys or other rules. When a table is no longer needed, DROP TABLE deletes it along with all its rows. Add IF EXISTS to avoid an error if it is already gone, and IF NOT EXISTS to CREATE TABLE when a setup script may run twice.
CREATE TABLE TamilNaduCustomers AS
SELECT CustomerID, CustomerName, City
FROM Customers
WHERE State = 'Tamil Nadu';
DROP TABLE IF EXISTS OldPriceList;
SELECT name
FROM sqlite_master
WHERE type = 'table';Returns 7 table names: the six sample tables plus TamilNaduCustomers.
Watch out: Treat DROP TABLE as permanent. SQLite can roll it back inside a transaction, but MySQL commits a DROP TABLE immediately, so ROLLBACK will not bring it back. On a live site, take a backup first and double-check the table name.
So far our tables have very few rules. In SQL Constraints and Keys you will add primary keys, foreign keys, UNIQUE and CHECK rules that protect your data. For a reminder of adding rows, see SQL INSERT, UPDATE and DELETE.