Where to Validate: Database vs Application

“Should I validate this in PHP, or in the database?” Both. They do different jobs.

The rule of thumb

> Validate in the application so the user gets a good message. Enforce in the database so the data can never be wrong.

What belongs in the database

• Required values: NOT NULL.
• Identity and uniqueness: PRIMARY KEY, UNIQUE (emails, usernames, slugs, order numbers).
• Relationships: FOREIGN KEY.
• Simple domain rules: CHECK for ranges, allowed values and formats.
• Types and sizes: DECIMAL(8,2), DATE, VARCHAR(100).
• Defaults.

What belongs in the application

• User-facing messages (“Please enter a valid email”).
• Complex rules that involve other tables or external data (“a user can have at most three active projects”, “the coupon is valid in this country”).
• Rules that change often.
• Authorisation (“may this user do this?”).
• Cross-field rules with context (the user’s role, time of day).

Rules that need to look at other rows are hard in constraints. Use a trigger, a transaction with locking, or a design that turns the rule into a unique key.

The race that only a constraint can stop

1
2
3
4
5
6

CREATE TABLE coupons (code VARCHAR(10) PRIMARY KEY, redeemed_by VARCHAR(20) NULL);
INSERT INTO coupons VALUES ('SAVE10', NULL);

CREATE TABLE redemptions (coupon_code VARCHAR(10) PRIMARY KEY, user_name VARCHAR(20) NOT NULL, FOREIGN KEY (coupon_code) REFERENCES coupons (code));
INSERT INTO redemptions VALUES ('SAVE10', 'asha');
INSERT INTO redemptions VALUES ('SAVE10', 'ben');

1

ERROR 1062 (23000): Duplicate entry 'SAVE10' for key 'redemptions.PRIMARY'

“A coupon can be used only once” is guaranteed by the primary key on redemptions.coupon_code. If two users submit at the same moment, exactly one insert succeeds. A check in application code (“has it been used?”) before the insert cannot give that guarantee.

Show friendly errors for database violations

The application should catch the specific errors and translate them:

In PHP with PDO, the SQLSTATE and the driver code are available in the exception’s errorInfo. Do not show the raw error to users, and do log it on the server.

An example of layered validation

Take an email field on a sign-up form:

1. Browser: type="email" and required. Fast feedback, easy to bypass.
2. Application: trim, lower-case, validate format, check the length, check “already used” for a friendly message.
3. Database: email VARCHAR(255) NOT NULL, UNIQUE (email). The final guarantee.

Each layer catches different problems, and the last one cannot be skipped.

Keeping the layers consistent

Duplicated rules drift apart. Reduce the problem:

• Put the same limits in both places, and generate one from the other if possible (for example, read column lengths from the schema).
• Write tests that try to insert invalid data directly and expect an error.
• Keep the schema in version control with the application.

When constraints get in the way

Occasionally a constraint blocks a legitimate bulk import or migration. The right answer is to load the data in a sensible order, or into a staging table, then move it (Importing CSV Safely), not to remove the constraint for good.