Chapter 10. Constraints

As a programmer, you create database constraints so that garbage won't go in, and therefore less garbage will come out—you hope. Is garbage-free data itself an optimization? Can it give the optimizer and the user better information to help in evaluating SELECTs? Or will the constant error testing hinder INSERTs, UPDATEs, and DELETEs? In this chapter, we'll address the good and bad effects of constraints on optimization.

Portability

MySQL does not support triggers or constraints (unless they're implicit NOT NULL constraints or explicit PRIMARY KEY and UNIQUE constraints). The gains shown throughout this chapter are for only seven DBMSs unless the test specifically uses only those three constraint types.

NOT NULL

The NULL “value” ...

Get SQL Performance Tuning now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.