SQL Schema Definition, Data Types and Constraints

1. SQL Command Families

SQL is a declarative language for defining, querying, changing, protecting and controlling relational data.

Commands are commonly grouped as:

• DDL defines structure: CREATE, ALTER, DROP and TRUNCATE;
• DML changes rows: INSERT, UPDATE and DELETE;
• DQL retrieves rows through SELECT;
• DCL manages privileges through GRANT and REVOKE;
• TCL controls transactions through COMMIT, ROLLBACK and SAVEPOINT.

The boundaries are useful vocabulary, although products may classify individual commands differently. The actual transactional behavior of each MySQL command matters more than its category label.

2. Creating a Database and Table

CREATE DATABASE sales;
USE sales;

CREATE TABLE customer (
    customer_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(254) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    joined_on DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    CONSTRAINT uq_customer_email UNIQUE (email),
    CONSTRAINT chk_customer_status
        CHECK (status IN ('ACTIVE', 'INACTIVE'))
);

CREATE TABLE gives every column a name and data type, then adds rules that valid rows must satisfy. Naming table-level constraints produces clearer diagnostics and makes later schema changes easier.

3. Choosing Data Types

Select a type from the meaning and required range of the value. A correct type prevents invalid representations, supports suitable operations and avoids wasted storage.

Do not choose every identifier as text or every number as the largest numeric type. Also do not select a narrow type without considering future range. Changing a heavily used column later can be expensive.

MySQL modes influence whether invalid or out-of-range values cause errors or conversions. Production systems should use strict behavior and validate important domain rules with constraints.

4. Integer Types

MySQL supplies TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT in signed and unsigned forms. The type determines storage and range.

An identifier is not automatically a measurement. Arithmetic on customer_id has little business meaning even though it is stored as an integer.

UNSIGNED expands the nonnegative range, but foreign-key columns must use compatible types and signedness. Display width syntax historically associated with integers does not limit the numeric range and should not be mistaken for validation.

5. Exact and Approximate Numbers

DECIMAL(p, s) stores exact fixed-point values. p is total precision and s is digits after the decimal point.

price DECIMAL(12, 2) NOT NULL

DECIMAL is appropriate for money and quantities requiring exact decimal arithmetic.

FLOAT and DOUBLE store approximate binary floating values. They suit scientific measurements where range and performance matter more than exact decimal representation. Equality comparisons and repeated financial calculations can surprise when approximate types are used.

A CHECK constraint can enforce range:

CHECK (price >= 0)

6. Character Data

CHAR(n) is fixed length, while VARCHAR(n) stores variable-length character data up to a limit. CHAR can suit genuinely fixed codes; VARCHAR suits names, email addresses and varying labels.

TEXT types store larger textual values. They have different indexing, default and storage considerations depending on MySQL version.

The declared length is interpreted with the character set and storage depends on encoded bytes. Choose limits from domain rules, not arbitrary defaults such as VARCHAR(255) for every field.

7. Character Sets and Collations

A character set defines how characters are encoded. utf8mb4 supports the full Unicode range and is the usual modern MySQL choice.

A collation defines comparison and ordering rules. It can control case sensitivity, accent sensitivity and linguistic ordering. Under a case-insensitive collation, two differently cased strings may compare equal and conflict with a UNIQUE constraint.

Character set and collation can be set at server, database, table or column levels. Mixed settings can force conversion and cause surprising comparison results.

8. Date and Time Types

DATE stores calendar dates, TIME stores time values or durations within its supported semantics, DATETIME stores a date and time without automatic time-zone conversion and TIMESTAMP commonly represents an instant with session time-zone conversion behavior.

YEAR stores a year. Choose it only when a year alone is the fact.

Store temporal values using temporal types rather than formatted strings. This enables validation, ordering, arithmetic and date functions. Decide explicitly whether a value is a local civil time or a global instant and handle application time zones consistently.

9. Binary and Large Objects

BINARY and VARBINARY store byte strings. BLOB types store larger binary values. They are appropriate for hashes, encoded identifiers, compressed data or file content.

Storing large files in a database gives transactional and authorization integration but increases database size, backup cost and transfer load. Another design stores files in object storage and records controlled references and metadata in the database.

Binary values do not use character collation. Do not place arbitrary bytes in a text column.

10. JSON and Specialized Types

MySQL JSON validates JSON documents and provides path operations. It is useful for sparse or evolving subordinate attributes, but core identifiers, relationships and frequently constrained properties usually belong in ordinary typed columns.

ENUM restricts a value to a declared list and SET permits a declared combination. These types can be convenient but couple allowed values to schema changes. A reference table is often better when values have labels, lifecycle, ordering or additional properties.

11. NULL

NULL represents missing, unknown or inapplicable information. It is not zero, an empty string or the text “NULL.”

Comparisons with NULL use three-valued logic. Use IS NULL and IS NOT NULL rather than = NULL.

NOT NULL states that every row must provide a known applicable value for the column. Use it whenever absence is not meaningful. Excessive nullable columns can signal that several entity types have been combined in one table.

12. DEFAULT

A default supplies a value when an INSERT omits the column:

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

A default does not generally replace an explicitly supplied NULL when NULL is allowed. It also does not validate meaning. A default status of ACTIVE is appropriate only if that is the correct state for every new row that omits status.

Defaults can use permitted constants or expressions according to the MySQL version and type.

13. Primary Keys

A primary key uniquely and non-nullably identifies every row:

PRIMARY KEY (order_id)

or:

PRIMARY KEY (order_id, line_number)

A table has at most one primary-key constraint, but the constraint may contain several columns. MySQL indexes the primary key and InnoDB uses it in clustered storage, making compact and stable keys valuable.

Identity should not depend on row position or current display order.

14. UNIQUE Constraints

UNIQUE enforces candidate-key or business uniqueness:

CONSTRAINT uq_employee_email UNIQUE (email)

MySQL generally permits several NULL values in a nullable unique column because NULL values are not treated as equal for this constraint. Combine UNIQUE with NOT NULL when every row must possess one unique value.

A composite unique constraint applies to the combination. It does not make each participating column individually unique.

15. Foreign Keys

CREATE TABLE order_header (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    ordered_at DATETIME NOT NULL,
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
);

A foreign key requires each non-NULL child value to match a referenced candidate key. Referencing and referenced columns need compatible definitions. InnoDB supports enforcement; storage-engine choice matters.

Referential actions such as CASCADE, SET NULL, RESTRICT and NO ACTION must match entity lifecycle. SET NULL also requires nullable child columns.

16. CHECK Constraints

CHECK expresses a Boolean condition:

quantity INT NOT NULL,
unit_price DECIMAL(12, 2) NOT NULL,
CHECK (quantity > 0),
CHECK (unit_price >= 0)

In modern MySQL, a row is rejected when the check evaluates FALSE. UNKNOWN from NULL does not itself reject the row, so NOT NULL is required when the value must exist.

Checks should be deterministic row rules. Rules requiring other rows may need keys, foreign keys, transaction logic or carefully designed triggers.

17. AUTO_INCREMENT

AUTO_INCREMENT generates numeric identifiers:

id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

Generated values are convenient surrogate keys. They are not guaranteed to be gap-free. Rolled-back inserts, failed statements, server behavior and concurrent work can leave gaps.

Do not use an auto-increment sequence where consecutive numbering is a legal requirement. Such numbering needs a separately designed transactional process.

18. ALTER TABLE

ALTER TABLE changes an existing definition:

ALTER TABLE customer
    ADD COLUMN phone VARCHAR(30) NULL;

ALTER TABLE customer
    ADD CONSTRAINT uq_customer_phone UNIQUE (phone);

It can add, modify, rename or drop columns and constraints. The exact syntax and whether an operation is instant, in-place or table-copying depend on the MySQL version, engine and change.

Before adding a stricter constraint, inspect and repair existing rows that violate it. Schema migration should include compatibility, deployment order, locking and rollback planning.

19. DROP and TRUNCATE

DROP TABLE removes the table definition and its data:

DROP TABLE obsolete_data;

TRUNCATE TABLE removes all rows while retaining the table definition. It is a DDL operation in MySQL, has different logging and identity behavior from DELETE and does not accept a WHERE clause.

DELETE is a DML statement that can remove selected rows and participates in transaction behavior according to the engine. These commands are not interchangeable merely because all can make rows disappear.

20. INSERT

Always name target columns:

INSERT INTO customer
    (email, full_name, joined_on)
VALUES
    ('anita@example.com', 'Anita Rao', '2026-02-10');

Omitted columns receive permitted defaults, generated values or NULL. The statement fails when a required column has no supplied value or default.

Multirow INSERT reduces round trips:

INSERT INTO category (category_name)
VALUES ('Books'), ('Music'), ('Games');

Constraints are checked for inserted rows and transaction behavior controls visibility and recovery.

21. UPDATE

UPDATE changes rows that satisfy a predicate:

UPDATE customer
SET status = 'INACTIVE'
WHERE customer_id = 42;

Without WHERE, every row is targeted. First run an equivalent SELECT when forming a high-impact update. In application code, use transactions and inspect the affected-row count.

Assignments conceptually use the old row values according to statement semantics and every resulting row must satisfy constraints.

22. DELETE

DELETE removes matching rows:

DELETE FROM customer
WHERE customer_id = 42;

Foreign keys may reject the deletion, cascade it or nullify child references. Understand these effects before deleting parent rows.

Deleting all rows with DELETE is different from TRUNCATE in logging, transaction, trigger, foreign-key and identity behavior. Select the command from its defined semantics.

23. MySQL DDL and Transactions

Many MySQL DDL statements cause implicit commits before and after execution. A later ROLLBACK should not be assumed to undo CREATE, ALTER, DROP or TRUNCATE.

Modern atomic DDL improves crash safety for supported operations, but atomic execution is not the same as user-controlled transactional rollback.

Keep schema migration separate from ordinary business transactions. Test migration behavior on the exact MySQL version and storage engine in use.

24. Building a Reliable Schema

Translate stable business rules into types and constraints. Use NOT NULL for required attributes, primary and unique keys for identity, foreign keys for references, checks for row-level conditions and deliberate defaults.

Choose exact types for exact values, complete Unicode settings for user text and temporal types for time. Name constraints and migrations clearly. Inspect generated DDL and catalog metadata rather than relying on tool displays alone.

A reliable schema prevents invalid states at the shared data boundary. Application validation remains valuable for guidance and user experience, while database constraints ensure that every authorized client observes the same rules.