Constraint — a rule the database enforces on data (NOT NULL, UNIQUE, CHECK)
Concept
Constraint — a rule enforced by the database engine on the data in a table. When a constraint is violated, the database rejects the operation.
Types of constraints:
PRIMARY KEY: One per table. Uniquely identifies each row. Implies NOT NULL + UNIQUE. The database creates a clustered index on it (InnoDB).
UNIQUE: No two rows can have the same value in this column (or combination of columns). NULL values are handled differently by DB: MySQL allows multiple NULLs in a UNIQUE column (because NULL ≠ NULL), PostgreSQL does the same.
NOT NULL: The column cannot contain a NULL value. Every insert/update must provide a value (or use the DEFAULT).
FOREIGN KEY: References a primary key in another table. Enforces referential integrity.
CHECK: A custom boolean condition that must be true for any row. CHECK (age >= 0), CHECK (price > 0), CHECK (start_date < end_date). Supported in MySQL 8.0.16+, PostgreSQL, SQLite.
DEFAULT: Provides a value when none is given on INSERT. DEFAULT NOW(), DEFAULT 0, DEFAULT 'pending'.
Database vs application constraints: Database constraints enforce invariants at the lowest level — no application bug can bypass them. Application-level validation (Laravel's FormRequest) is faster to implement but can be bypassed (direct DB access, migration bugs).
Performance: Constraints add overhead to writes (every insert/update must be checked). The overhead is small for simple constraints. Foreign key checks require index lookups.
Deferred constraints (PostgreSQL): Constraints checked at the END of a transaction rather than on each statement. Useful for circular references or bulk operations.
Code Example
<?php
Schema::create('products', function (Blueprint $table) {
$table->id(); // PRIMARY KEY (implicit)
$table->string('sku', 50)->unique(); // UNIQUE constraint
$table->string('name', 255); // implicitly NOT NULL (Blueprint default)
$table->text('description')->nullable(); // allows NULL
$table->decimal('price', 10, 2); // NOT NULL
$table->integer('stock')->default(0); // DEFAULT 0
$table->foreignId('category_id')->constrained(); // FOREIGN KEY
// CHECK constraints (Blueprint in Laravel 9+)
$table->check('price > 0', 'price_positive');
$table->check('stock >= 0', 'stock_non_negative');
});
// OR via raw SQL for complex checks
\DB::statement("ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0)");
\DB::statement("ALTER TABLE products ADD CONSTRAINT chk_date_range CHECK (start_date < end_date)");
// Constraint violations throw exceptions
try {
\DB::table('products')->insert(['sku' => 'DUPLICATE', 'name' => 'Test', 'price' => -5]);
} catch (\Illuminate\Database\QueryException $e) {
// SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'DUPLICATE' for key 'sku'
// OR: Check constraint 'price_positive' is violated
}
// Composite UNIQUE constraint (two columns together must be unique)
Schema::create('role_user', function (Blueprint $table) {
$table->foreignId('user_id')->constrained();
$table->foreignId('role_id')->constrained();
$table->unique(['user_id', 'role_id']); // combination must be unique (pivot table)
$table->timestamps();
});
// Checking constraints in Eloquent
class Product extends \Illuminate\Database\Eloquent\Model
{
protected $fillable = ['sku', 'name', 'price', 'stock', 'category_id'];
// Application-level validation (complements DB constraints)
protected static function booted(): void
{
static::saving(function (self $model) {
if ($model->price <= 0) throw new \DomainException('Price must be positive');
if ($model->stock < 0) throw new \DomainException('Stock cannot be negative');
});
}
}