Creating Tables

CREATE TABLE: Decide the Shape Before the Data

In a relational database you cannot store a single row until you have described the table it goes into. That description — the column names, the type of each column, and the rules each column obeys — is written once with CREATE TABLE and then enforced on every insert for the rest of the table's life.

Beginners often experience this as bureaucracy. It is better understood as the database doing work on your behalf. Every rule you write into the table is a check you never have to remember to write in your application code, and it holds even when data arrives from somewhere you did not anticipate: an import script, an admin panel, a colleague's one-off query at midnight.

So spend a few minutes on the design before typing. For a students table, ask what one row represents (one student), what identifies it (a roll number or a generated id), which facts must always be present (a name), and which are optional (a phone number). Those four answers turn almost directly into the statement.

-- A first students table
CREATE TABLE students (
    id         INT,
    name       VARCHAR(100),
    email      VARCHAR(150),
    branch     VARCHAR(20),
    marks      INT,
    phone      VARCHAR(15),
    joined_on  DATE
);

-- Inspect what you built (MySQL)
DESCRIBE students;
SHOW COLUMNS FROM students;

-- See the exact statement MySQL stored, including defaults it filled in
SHOW CREATE TABLE students;

Choosing Data Types Without Regret

A data type is a promise about what a column can hold. Choosing well costs nothing at the time and saves real pain later, because changing a column's type on a table that already has millions of rows is slow and occasionally destructive.

Three choices are worth thinking about rather than guessing. First, money must never be stored in FLOAT or DOUBLE. Those types store approximations in binary, so a price of 0.10 is not exactly 0.10, and adding a thousand of them drifts. Use DECIMAL(10, 2), which stores digits exactly. This is the single most common data-type bug in student e-commerce projects, and it shows up as a total that is off by a paisa in a way nobody can explain.

Second, phone numbers are text, not numbers. An Indian mobile number stored as an integer loses a leading zero, cannot hold a +91 prefix, and might overflow. You are never going to add two phone numbers together, so the fact that they are made of digits is irrelevant. The rule is: if you would not do arithmetic on it, it is text. Pincodes and Aadhaar-style identifiers fall in the same bucket.

Third, dates belong in date types. Storing '02-08-2026' in a VARCHAR means the database sorts it as text — so 02 January sorts before 30 December of the previous year — and you cannot ask for "the last thirty days" without heroics. A real DATE column sorts correctly and works with every date function in the lesson ahead.

• INT — whole numbers such as marks, quantities, counts
• BIGINT — whole numbers too large for INT, which tops out near 2.1 billion when signed
• DECIMAL(p, s) — exact numbers with p total digits and s after the point; the correct type for prices
• FLOAT / DOUBLE — approximate numbers, fine for sensor readings and averages, wrong for money
• VARCHAR(n) — text up to n characters, storing only what you use; the everyday text type
• CHAR(n) — always exactly n characters, padded; only worth it for fixed codes such as a 2-letter state code
• TEXT — long text with no practical limit, for descriptions and reviews
• DATE / DATETIME / TIMESTAMP — a day, a day with a time, and a point in time respectively
• BOOLEAN — true or false; in MySQL this is simply another name for TINYINT(1), storing 1 and 0

-- The same idea, with types chosen deliberately
CREATE TABLE products (
    id          INT,
    name        VARCHAR(200),        -- generous, but bounded
    description TEXT,                -- can be long
    price       DECIMAL(10, 2),      -- exact rupees and paise
    stock       INT,
    is_listed   BOOLEAN,
    created_at  TIMESTAMP
);

-- Why FLOAT is wrong for money: this comparison is not reliably true
-- SELECT 0.1 + 0.2 = 0.3;      -- with FLOAT columns, can return 0

-- With DECIMAL the arithmetic is exact
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) AS total;

Constraints: Rules the Database Refuses to Break

A constraint is a rule attached to a column or table that the database enforces on every write. The table built so far accepts nonsense happily: two students with the same id, a student with no name, marks of -40. Constraints close those doors.

PRIMARY KEY is the one you will use in every table. It marks the column that identifies a row, and it implies two things at once: values must be unique, and they can never be NULL. It also creates an index automatically, which is why looking a row up by its primary key stays fast even in a huge table.

NOT NULL says a value must be supplied. UNIQUE says no two rows may share a value — the right rule for an email address, since two accounts with the same email would break your login. DEFAULT supplies a value when the insert does not mention the column. CHECK tests a condition and rejects the row if it fails, which is how you say marks must lie between 0 and 100.

A useful way to decide is to ask what should happen if the value is missing or wrong. If the answer is "the row is meaningless", use NOT NULL. If the answer is "it is fine, we just do not know yet", leave it nullable. A middle name and a phone number are genuinely optional; a student's name is not.

• PRIMARY KEY — identifies the row; unique and never NULL; indexed automatically
• NOT NULL — the column must always be given a value
• UNIQUE — no two rows may hold the same value in this column
• DEFAULT value — used when an INSERT does not mention the column
• CHECK (condition) — the row is rejected unless the condition holds
• AUTO_INCREMENT — MySQL generates the next number for you (see the next section)
• FOREIGN KEY — the value must exist in another table; covered with joins later in the course

-- The same table, this time with rules the database will enforce
CREATE TABLE students (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    branch     VARCHAR(20)  NOT NULL,
    marks      INT CHECK (marks >= 0 AND marks <= 100),
    phone      VARCHAR(15),                       -- optional on purpose
    is_active  BOOLEAN   DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- These now fail, which is exactly what we wanted
-- INSERT INTO students (name, email, branch) VALUES (NULL, 'a@x.com', 'CSE');
--   -> Column 'name' cannot be null
-- INSERT INTO students (name, email, branch, marks)
--   VALUES ('Rahul', 'a@x.com', 'ECE', 150);
--   -> Check constraint 'students_chk_1' is violated

-- Naming a constraint makes the error message readable later
CREATE TABLE marks_audit (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    score INT,
    CONSTRAINT chk_score_range CHECK (score >= 0 AND score <= 100)
);

Generated Ids, and Why Every Database Spells It Differently

Almost every table wants a numeric id that the database allocates automatically, so your code never has to work out "what is the next free number?" — a question that is impossible to answer safely when two users insert at the same instant.

This is one of the sharpest dialect differences in SQL, so learn all four spellings now rather than being confused by a copied example later. MySQL writes AUTO_INCREMENT. PostgreSQL historically used SERIAL and now prefers GENERATED ALWAYS AS IDENTITY. SQL Server writes IDENTITY(1,1). SQLite gives this behaviour to any column declared INTEGER PRIMARY KEY.

One behaviour surprises people regardless of dialect: the numbers are not reused and not guaranteed to be gap-free. Delete row 7 and the next insert still gets 8. A failed insert or a rolled-back transaction can also consume a number. That is deliberate — reusing ids would let a deleted student's id silently attach itself to a new student, and any old report referring to id 7 would then be wrong. Treat ids as labels, never as a count of rows.

-- MySQL
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    total DECIMAL(10,2)
);

-- PostgreSQL (modern, standard form)
-- CREATE TABLE orders (
--     id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
--     total NUMERIC(10,2)
-- );

-- PostgreSQL (older shorthand, still very common)
-- CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC(10,2));

-- SQL Server
-- CREATE TABLE orders (id INT IDENTITY(1,1) PRIMARY KEY, total DECIMAL(10,2));

-- SQLite
-- CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL);

Changing a Table Afterwards

No schema survives contact with real requirements. ALTER TABLE lets you add a column, drop one, rename one, change a type, or add a constraint to a table that already holds data.

Two operations deserve care. Adding a NOT NULL column to a table that already has rows fails unless you also give a DEFAULT, because the existing rows would otherwise be left with no value — the database is refusing to create data that breaks the rule you just wrote. And narrowing a type, say from VARCHAR(200) to VARCHAR(50), will either error or truncate depending on the server's settings, so check the longest existing value first with SELECT MAX(LENGTH(column)).

On a large production table, ALTER TABLE can also lock the table while it rebuilds. That is not something you will feel on a practice table of fifty rows, but it is why real teams schedule schema changes rather than running them during a busy hour.

-- Add a column
ALTER TABLE students ADD COLUMN semester INT;

-- Add a NOT NULL column to a table that already has rows:
-- this needs a default, or existing rows would violate the rule
ALTER TABLE students ADD COLUMN city VARCHAR(60) NOT NULL DEFAULT 'Unknown';

-- Remove a column (the data in it is gone for good)
ALTER TABLE students DROP COLUMN semester;

-- Change a column's type or rules (MySQL)
ALTER TABLE students MODIFY COLUMN phone VARCHAR(20);

-- Rename a column (MySQL 8.0+, and PostgreSQL)
ALTER TABLE students RENAME COLUMN phone TO mobile;

-- Add a constraint later
ALTER TABLE students ADD CONSTRAINT uq_students_email UNIQUE (email);

-- Check before narrowing a text column
SELECT MAX(LENGTH(name)) AS longest_name FROM students;