MariaDB / Basic: relational foundations / Lesson 2

Schemas, exact values and multilingual text

Design a small warehouse table whose types express business meaning. Learn how NULL, strict mode and collation affect stored values and comparisons.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Choose precise numeric and temporal types.
Explain character sets, collations and missing values.

Types are part of the business rule

Start from facts rather than from sample appearance. A parcel identifier is a key, a price is an exact decimal and a display name is text. DECIMAL(9,2) allows seven integer digits and two fractional digits; it is preferable to floating point for this money example. VARCHAR length is measured in characters, while storage also depends on the character set. A postal code should remain text when leading zeroes and letters have meaning.

Declare the contract explicitly

This definition is for an empty disposable schema named harbor_lab, not an instruction to provision a production server. We spell out InnoDB and utf8mb4 so that environmental defaults cannot silently choose a different contract. The selected unicode collation is broadly available, but languages and identifier rules may demand another verified collation. CHECK rejects negative prices on supported modern MariaDB releases. Historical versions before 10.2.1 accepted CHECK syntax without enforcing it, which matters during migrations.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE TABLE harbor_lab.parcels (
  parcel_id INT PRIMARY KEY,
  label VARCHAR(80) NOT NULL,
  price DECIMAL(9,2) NOT NULL CHECK (price >= 0),
  delivered_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

A primary key identifies a row; delivered_at is allowed to be unknown. Object creation has transaction implications discussed later.

Missing is different from zero

NULL represents an absent or unknown value, not an empty string or a zero date. Compare it with IS NULL because ordinary equality with NULL is unknown. Decide whether a timestamp means an instant or a local wall-clock value. DATETIME does not perform the session time-zone conversion associated with TIMESTAMP. Precision and valid ranges differ by type and version. Store an agreed UTC convention when appropriate, and carry the source time zone separately when the business needs it.

Inspect stored values and comparisons

Assuming only the two inserted rows exist, this query returns parcel 1 with price 12.30. A decimal value has numeric meaning even if a client formats its trailing zero differently. The second parcel is excluded because its delivery time is known. Arabic text requires a capable connection character set as well as a capable column; a correct table definition cannot repair bytes already corrupted by a wrongly configured client. SHOW CREATE TABLE reveals the effective definition.

sql Illustrative SQL; expected results are reasoned, not measured.

INSERT INTO harbor_lab.parcels VALUES
 (1, 'صندوق', 12.30, NULL),
 (2, 'Envelope', 4.00, '2026-10-01 09:00:00');
SELECT parcel_id, price FROM harbor_lab.parcels
WHERE delivered_at IS NULL ORDER BY parcel_id;

Expected output

parcel_id | price
1         | 12.30

Expected logical result under the stated two-row fixture.

Validate before accepting conversions

A character set defines encodable characters; a collation defines comparison and sorting rules. A case-insensitive collation can make two visibly different labels compare equal, which also affects unique indexes. Inspect SHOW COLLATION for the deployed version instead of borrowing a MySQL-specific collation name. Strict SQL mode helps reject out-of-range or truncated data instead of accepting a coercion with a warning. Applications should still validate input and inspect warnings during imports; server constraints remain the final guard.

Common errors and fixes

ERROR 1366: Incorrect string value

Why it happens: Connection or column encoding cannot represent the incoming text.
How to fix it: Inspect both character-set settings and import encoding; resend intact source text.
Prevent it next time: Specify utf8mb4 end to end and test Arabic round trips.

Undelivered rows disappear from a filter

Why it happens: The predicate used delivered_at = NULL.
How to fix it: Use delivered_at IS NULL for missing values.
Prevent it next time: Include missing, empty and present values in query examples.

A price is silently rounded or clipped

Why it happens: An unsuitable type or permissive SQL mode allowed conversion.
How to fix it: Choose an adequate DECIMAL precision and validate conversion warnings.
Prevent it next time: Define numeric bounds and strict-mode expectations before importing.

Check your understanding

1. A delivery fee may reach 1234567.89. Is DECIMAL(9,2) sufficient, and would DECIMAL(8,2) be sufficient?

Show worked solution

DECIMAL(9,2) fits seven integer digits and two fractional digits, so it fits exactly. DECIMAL(8,2) permits only six integer digits, so it cannot represent that fee. Choose a larger documented business bound if growth is plausible; do not solve an exact-money requirement by switching to FLOAT.

2. Add a display label in Arabic without making it a business identifier. Which checks establish correctness?