CREATE

CREATE TABLE: defining structure (with valid syntax)

CREATE TABLE declares columns, their types, and constraints. A frequent beginner bug is a trailing comma after the last column — it fails on MySQL. There is no comma before the closing parenthesis:

CREATE TABLE Students (
    RollNumber   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    Email        VARCHAR(255) UNIQUE,
    Cgpa         DECIMAL(3,2) CHECK (Cgpa >= 0),
    DeptId       INT,
    CONSTRAINT fk_dept FOREIGN KEY (DeptId) REFERENCES Departments(Id)
);   -- no comma after the last item

-- Idempotent create (MySQL / Postgres):
CREATE TABLE IF NOT EXISTS Students ( ... );

-- Postgres also: CREATE INDEX IF NOT EXISTS ...
CREATE INDEX IF NOT EXISTS idx_students_email ON Students (Email);

IF NOT EXISTS avoids failing when the object is already there (migrations, re-runs). It does not alter an existing table to match a new definition — schema drift still needs migrations (ALTER / expand-contract).

Primary key: surrogate vs natural

• Natural key — a real-world unique attribute (email, ISBN, country code). Pros: meaningful joins, no extra column. Cons: business values change (email rename, ISBN edition rules); wide keys bloat secondary indexes in InnoDB (clustered PK is copied into every secondary leaf).
• Surrogate key — synthetic BIGINT GENERATED / SERIAL / UUID. Pros: stable, narrow, never changes meaning. Cons: joins need an extra column; you still need a UNIQUE constraint on the natural business key.

Default judgment: surrogate PK + UNIQUE on the natural business key for mutable domains; natural PK only for truly immutable codes (ISO country, small enum-like tables).

When-NOT for a single-column PK: many-to-many link tables often use a composite PK (user_id, role_id) instead of a useless surrogate — uniqueness is the pair.

Choosing types deliberately

Type	Use for	Why
CHAR(n)	fixed-length codes (e.g. CHAR(2) state)	always n bytes; pads with spaces
VARCHAR(n)	variable text up to n	stores only what's used + length
TEXT	unbounded / large text	no short limit; indexing rules differ by engine
DECIMAL(p,s)	money, exact ratios	p total digits, s after the point — exact, unlike FLOAT
INT / BIGINT	ids, counts	4 / 8 bytes; prefer BIGINT for high-volume surrogates
BOOLEAN / TINYINT(1)	flags	Postgres BOOLEAN; MySQL often TINYINT
TIMESTAMPTZ / DATETIME	events vs civil time	see Date Functions — prefer absolute instants for events
JSON / JSONB	semi-structured attributes	when schema varies; not a substitute for core relational columns you filter on heavily

Use DECIMAL, never FLOAT, for money. FLOAT is binary floating point: 0.1 + 0.2 ≠ 0.3. DECIMAL(10,2) stores cents exactly.

Constraints: when to use / when-not

• NOT NULL — when the app cannot function without the value. When-not: truly optional attributes (middle name).
• UNIQUE — natural business keys, even if PK is surrogate. When-not: soft-deleted rows that re-use email unless you use a partial unique index WHERE deleted_at IS NULL.
• FOREIGN KEY — enforce parent existence; protects against orphan children. When-not: extreme write-scale shards where FKs cannot cross shards (enforce in app + async checks), or bulk-load windows where you defer validation deliberately.
• CHECK — cheap domain rules (qty >= 0). When-not: rules that need other tables (use triggers or app transactions).

CREATE INDEX at create time

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_open_created ON orders (created_at) WHERE status = 'open';  -- Postgres partial

Index the columns you filter and join on — not every column. Each index is write amplification (see B+tree page).

Takeaways

• No trailing comma before ); constraints can be inline or named with CONSTRAINT.
• IF NOT EXISTS is for idempotent deploys, not schema migration.
• Prefer surrogate PK + UNIQUE natural key unless the natural key is immutable.
• CHAR = fixed width, VARCHAR = variable; DECIMAL(p,s) for exact money; avoid FLOAT for currency.
• FKs/UNIQUE/CHECK are judgment calls — know when-not (sharding, soft delete, bulk load).

---

Re-authored for correctness and design depth for this guide (trailing-comma fix retained; IF NOT EXISTS, PK judgment, types, constraints when-not, CREATE INDEX added). Per the MySQL/PostgreSQL CREATE TABLE references. See also: Keys, Integrity Constraints, How Indexes Work.

I just read a lesson on **CREATE** (Databases) and want to truly understand it. Explain CREATE from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **CREATE** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **CREATE** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **CREATE** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.