Data Types and Constraints

Choosing the right data type and constraints when creating a table keeps data accurate, consistent, and efficient to store and query.

Common Data Types

Choosing an appropriate type and size for each column affects storage efficiency and the kinds of validation the database enforces automatically.

• INT — whole numbers, ideal for IDs and counts.
• DECIMAL(p, s) — precise decimal numbers, essential for currency (never use FLOAT for money).
• VARCHAR(n) — variable-length text up to n characters.
• DATETIME / DATE — date and time values.
• BIT — a boolean-style true/false/NULL value.

Constraints

Constraints enforce rules directly at the database level, preventing invalid data from ever being stored regardless of which application inserts it.

CREATE TABLE Products (
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100) NOT NULL,
    SKU VARCHAR(20) NOT NULL UNIQUE,
    Price DECIMAL(10,2) NOT NULL CHECK (Price >= 0),
    CategoryId INT FOREIGN KEY REFERENCES Categories(CategoryId)
);

Common Mistakes

• Using FLOAT for monetary values, which can introduce rounding errors — DECIMAL is the correct choice for currency.
• Forgetting NOT NULL on columns that should always have a value, allowing incomplete data to slip in.
• Choosing an undersized VARCHAR length that later truncates real data unexpectedly.
• Omitting foreign key constraints, which allows "orphaned" rows that reference IDs that don't actually exist in the related table.