Tables, Rows & Columns - SQL for Beginners Lesson | DevLoom

Primary Key — the row''s identity:

-- Every table should have one PRIMARY KEY
-- It must be: unique, never NULL, never changing

CREATE TABLE customers (
  id    INT AUTO_INCREMENT PRIMARY KEY,  -- auto-generates: 1, 2, 3...
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE              -- no two customers with same email
);

All five main constraints:

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,   -- 1. PRIMARY KEY: unique row id
  customer_id INT NOT NULL,                     -- 2. NOT NULL: must have a value
  total       DECIMAL(10,2) DEFAULT 0.00,       -- 3. DEFAULT: fallback value
  status      VARCHAR(20) DEFAULT 'pending',
  email       VARCHAR(150) UNIQUE,              -- 4. UNIQUE: no duplicates in column
  
  -- 5. FOREIGN KEY: enforces the relationship to customers table
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

What happens when a constraint is violated:

-- This will FAIL — customer_id 999 doesn't exist in customers:
INSERT INTO orders (customer_id, total)
VALUES (999, 500.00);
-- Error: Cannot add or update a child row: a foreign key constraint fails

-- This will FAIL — email already exists:
INSERT INTO customers (name, email)
VALUES ('Test', 'arjun@example.com');
-- Error: Duplicate entry for key 'email'

💡 Key Insight: Constraints are your safety net. They prevent bad data from entering the and save you from corrupt records weeks later.

EXERCISE

Data Types — Choosing the Right Column Type

Every column has a data type that tells MySQL what kind of value it stores. Choosing the right type keeps your data accurate and your queries fast.

The most common MySQL data types:

-- Text types:
VARCHAR(255)    -- variable-length text, up to 255 chars (most common)
TEXT            -- longer text, no length limit, can't be indexed easily
CHAR(10)        -- fixed-length text (always exactly 10 chars)

-- Number types:
INT             -- whole numbers: -2,147,483,648 to 2,147,483,647
BIGINT          -- very large whole numbers (use for ids in big systems)
DECIMAL(10,2)   -- exact decimals, e.g. 10 digits total, 2 after point
FLOAT / DOUBLE  -- approximate decimals (avoid for money!)

-- Date / Time types:
DATE            -- '2024-01-15' (date only)
DATETIME        -- '2024-01-15 14:30:00' (date + time)
TIMESTAMP       -- like DATETIME but auto-updates, timezone-aware

-- Boolean:
TINYINT(1)      -- MySQL uses 0/1 for false/true (no native BOOLEAN)

Practical example — creating a products table:

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(200)   NOT NULL,
  description TEXT,
  price       DECIMAL(10,2)  NOT NULL,
  stock_qty                ,
  is_active   TINYINT()      ,
  created_at  DATETIME        
);

Save

EXERCISE

Foreign Keys & Referential Integrity

A Foreign Key links rows between tables and lets the database automatically enforce that relationships stay valid.

The problem Foreign Keys solve:

Without a foreign key, nothing stops you from creating an order for a customer that doesn''t exist:

-- Without FK constraint, this silently succeeds with a ghost customer:
INSERT INTO orders (customer_id, total) VALUES (99999, 500);
-- customer_id 99999 doesn't exist — corrupt data, no error

Foreign Key in action:

-- Parent table:
CREATE TABLE customers (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Child table with Foreign Key:
CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  total       DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE   -- if customer deleted, their orders are deleted too
    ON UPDATE CASCADE   -- if customer id changes, orders update automatically
);

ON DELETE options:

ON DELETE CASCADE    
  RESTRICT

Save

DevLoom

DevLoom

DevLoom

DevLoom

Tables, Rows & Columns

Tables, Rows & Columns

Lesson Contents

References

From Course

Share

Topics

Primary Keys & Constraints

Data Types — Choosing the Right Column Type

Foreign Keys & Referential Integrity