SQL CREATE TABLE
CREATE TABLE defines a new table — its columns, their types, and any column-level constraints.
Anatomy
SQL
CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL, email VARCHAR(120) UNIQUE, country CHAR(2), active BOOLEAN NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );
Per column you can declare
| Part | What it does |
|---|---|
| Name | What you'll reference it by — pick descriptive, snake_case. |
| Type | Storage shape — INT, VARCHAR(n), TEXT, DATETIME, … |
NOT NULL | Disallows empty values. |
DEFAULT x | Fills in x when no value is supplied. |
PRIMARY KEY | Identifies each row uniquely. Implies NOT NULL + an index. |
UNIQUE | No two rows may share this column's value. |
CHECK (cond) | Disallow rows where cond is false. |
REFERENCES other(id) | Foreign key. |
If not exists
SQL
CREATE TABLE IF NOT EXISTS customers (...);
From a query
SQL
CREATE TABLE customers_au AS SELECT * FROM customers WHERE country = 'AU';
Quick and portable, but no constraints/indexes are copied — you'd add those after.
Tip: Add
created_at and updated_at columns by default. Future-you will thank you when debugging.Example
Example
CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL, email VARCHAR(120) UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );Try it Yourself »
Exercise
Mark the id column as primary key.
id INT
KEY
Seven letters; pairs with KEY.
Discussion
Loading…