BrainPatchworkDX.com

-- 06_create_tables_basic.sql
-- Lesson 6: Create tables, and the basic column types and constraints.
--
-- Requires: Database = training.shop (lesson 5).
--
-- A table has a name and a list of columns. Each column has a name, a type
-- and optional rules (constraints).
--
-- SQLite column types used here:
-- INTEGER whole numbers
-- REAL decimal numbers
-- TEXT text of any length
-- BLOB raw bytes (pictures, files)
-- SQLite is flexible about types, but use them as shown so your data stays
-- tidy.
--
-- Constraints used here:
-- PRIMARY KEY the unique identifier of each row
-- AUTOINCREMENT the server gives each new row the next number
-- NOT NULL the value must be given (NULL means "no value")
-- UNIQUE no two rows may hold the same value
-- DEFAULT the value used when none is given

-- Customers: the people who buy things.
CREATE TABLE Customers (
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
CustomerName TEXT NOT NULL,
ContactName TEXT,
Address TEXT,
City TEXT,
PostalCode TEXT,
Country TEXT NOT NULL DEFAULT 'Unknown'
);

-- Suppliers: the companies that provide products.
CREATE TABLE Suppliers (
SupplierID INTEGER PRIMARY KEY AUTOINCREMENT,
SupplierName TEXT NOT NULL UNIQUE,
ContactName TEXT,
City TEXT,
Country TEXT
);

-- Check that both tables exist.
SELECT name
FROM sqlite_master
WHERE type = 'table'
AND name NOT LIKE 'sqlite_%'
ORDER BY name;

-- Show the columns of one table.
PRAGMA table_info(Customers);

-- Try it:
-- 1. Create a table called Shippers with ShipperID (INTEGER primary key
-- with AUTOINCREMENT), ShipperName (TEXT, NOT NULL) and Phone (TEXT).
-- 2. Run PRAGMA table_info(Shippers); and check the columns.
-- 3. Try to create Customers a second time. Read the error.