Lesson 2: Tables, rows, columns, constraints (what DBAs must enforce)

Section 2 — Lesson 2: Tables, Rows, Columns, and Constraints (What DBAs Must Enforce)

Welcome back. In the last lesson, you learned the difference between data and information,
and why SQL Server sits at the center of business applications.

Now we are going to get practical. This lesson teaches you the building blocks of every relational system:
tables, rows, columns, and constraints.
If you master these concepts early, you will avoid the most common mistakes that cause broken reports, duplicates,
orphan records, and performance issues in production.

Lesson description: You will learn how SQL Server stores data in tables, how rows and columns represent
real-world entities, how to choose correct data types, how NULL works, and how constraints enforce correctness so
applications can trust the database. You will also learn the “DBA verification habit” (how to prove the database is
enforcing rules, not just hoping it is).

1) The Relational Model in Plain Language

Relational databases store data in tables. A table represents one real-world thing (an entity).
Each row is one instance of that thing, and each column is a property (attribute).

• Table = a container for a specific entity (Customer, Order, Invoice, Product).
• Row = one record (one customer, one order, one invoice).
• Column = one attribute (CustomerName, OrderDate, TotalAmount).
• Schema = a logical namespace for objects (commonly dbo).

DBA mindset: A database is not “random tables.” A database is a carefully designed model of a business.
The quality of that model determines how reliable and scalable the application becomes.

2) How to Think Like a DBA When You See a Table

When a DBA reviews a table design, they immediately ask:

1. What entity is this table representing?
2. What uniquely identifies each row? (Primary Key strategy)
3. What columns must never be missing? (NOT NULL discipline)
4. What values must be validated? (Constraints: CHECK/DEFAULT/UNIQUE)
5. How will this table be queried? (Index and performance implications)
6. How will this table relate to other tables? (Foreign Keys and referential integrity)

Your goal is to train this instinct early. It is exactly how job interviews and real DBA work feel.

3) Columns and Data Types (DBA Rule: Smallest Correct Type)

Choosing the correct data type is not a cosmetic choice—it impacts storage, performance, correctness, indexing,
and how applications behave. Many production problems start with poor data type choices.

Common SQL Server data types you must know

• Whole numbers: TINYINT, SMALLINT, INT, BIGINT
  Use for: IDs, counters, quantities (choose based on expected range)
• Exact numeric / currency: DECIMAL(p,s) (recommended for money)
  Why: exact precision, avoids rounding errors common with floating-point types
• Floating point: FLOAT, REAL
  Use for: scientific measurements where tiny rounding differences are acceptable (not currency)
• Date/time: DATE, DATETIME2 (modern standard), TIME
  DBA note: prefer DATETIME2 over older DATETIME for better precision and range
• Text: VARCHAR(n) / NVARCHAR(n)
  Use NVARCHAR: when you need full Unicode support (international characters)
• Boolean: BIT (0/1)
• Unique identifiers: UNIQUEIDENTIFIER (GUID)

Two professional rules you should follow

• Rule 1: Do not store dates as text. Use DATE or DATETIME2.
• Rule 2: Do not store money in FLOAT. Use DECIMAL.

4) NULL vs NOT NULL (One of the Biggest Sources of Bugs)

In SQL Server, NULL means “unknown” or “not provided.” It does not mean zero and it does not mean empty text.
NULL can be useful, but uncontrolled NULL usage causes reporting mistakes and application bugs.

DBA guidance

• Use NOT NULL when the business requires a value (IDs, required references, created dates, statuses).
• Allow NULL only when the business truly allows “unknown” or “not applicable.”
• Make defaults explicit when possible (CreatedAt default, Status default).

Common example

• Good: CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
• Risky: CreatedAt DATETIME2 NULL (later you find rows with no create date)

5) Constraints (The Database Must Enforce Rules—Not Just the App)

A constraint is a rule enforced by SQL Server. DBAs rely on constraints because application code changes,
but database integrity must remain stable and trustworthy.

Why DBAs care about constraints

• They prevent invalid data from entering the system.
• They reduce support emergencies caused by “impossible” values.
• They improve trust in reporting and analytics.
• They protect the database even when applications have bugs or missing validations.

Main constraint types (overview)

• PRIMARY KEY: unique row identity (you will deep-dive in Lesson 3).
• FOREIGN KEY: valid relationships between tables (you will deep-dive in Lesson 4).
• UNIQUE: prevents duplicates where duplicates cause business damage.
• CHECK: enforces valid ranges and rules (quantity > 0, totals >= 0, status in allowed list).
• DEFAULT: supplies standard values when none are provided.

6) The DBA Verification Habit (Prove the Rule Works)

Professional DBAs do not assume rules are working—they verify.
Every time you add a constraint or modify a table, you should test it by attempting an invalid insert/update
(in a safe lab or test environment) and confirming SQL Server blocks it.

Example verification pattern (concept)

1. Create or review a rule (e.g., Quantity > 0).
2. Attempt to insert an invalid value (Quantity = 0).
3. Confirm SQL Server rejects it and returns a constraint error.
4. Document what the rule prevents (business impact).

This habit is what makes you reliable in production—especially during audits or incidents.

7) Mini Example (Read Carefully)

Imagine a table called Booking. A job-ready DBA would expect:

• BookingID should uniquely identify each booking (PK).
• CustomerID should reference a real customer (FK).
• PickupTime should be a date/time type (not text).
• Status should be constrained to valid values (NEW, CONFIRMED, COMPLETED, CANCELED).
• TotalAmount should be DECIMAL and not negative (CHECK constraint).
• CreatedAt should be not null with a default timestamp.

Notice what is happening: you are not memorizing terms—you are learning how to review database designs like a professional.

8) Practice (Write This in Your DBA Notebook)

1. Pick an entity: Customer, Booking, Order, Invoice, or Product.
2. List 6 columns you would store for that entity.
3. For each column, write: data type + NULL/NOT NULL + one constraint if needed.
4. Write one paragraph explaining what “bad data” your constraints would prevent.

Lesson Summary

• Tables represent entities; rows represent records; columns represent attributes.
• Correct data types and NULL discipline prevent long-term reliability problems.
• Constraints are database-enforced rules that protect applications from data disasters.
• A DBA verifies rules with evidence instead of assuming they work.

Next Lesson: Keys (PK/FK/Unique/Check Constraints) — Why They Prevent Data Disasters

In the next lesson, we will go deeper into keys and constraints as a DBA would:
how to choose primary key strategies, how foreign keys enforce relationships,
and how to design UNIQUE and CHECK constraints that prevent duplicates, orphans, and invalid values in production.