Somewhere along the way “every table needs an auto-increment id” stopped being a habit and became a law, and the law does real damage, because plenty of tables already have a perfectly good identity sitting in the data and the extra integer column exists only to satisfy an ORM that was never asked whether the natural key was enough; an order line is (order_id, line_no), a tenant-scoped page is (tenant_id, slug), a daily reading is (sensor_id, day), and forcing a surrogate key onto those tables buys you a second index to maintain and a uniqueness rule the database no longer enforces for you. The interesting question is what a storage engine should actually give you when the honest key is a pair, and MongrelDB’s answer is opinionated in a way worth understanding before you design the schema.

One primary key, and the engine means it

MongrelDB enforces at most one primary key column per table, and this is not a client-side nicety; the schema validator in the core rejects a second PRIMARY_KEY flag with “schema may contain at most one primary key column” at table creation time, so the mistake never reaches the insert path. The reason is that the primary key is not really “the unique column” in this engine, it is the row’s identity, the value that write keys, point lookups, deletes, and change-data-capture events are all addressed by, and an identity wants to be one value the way a memory address wants to be one value. Uniqueness over several columns is a different job entirely, and it belongs to the constraint system.

The composite key is a unique constraint

The engine’s UniqueConstraint takes a list of column ids, not a single column, and it is enforced atomically at transaction commit: an existence scan against the transaction’s read snapshot plus a first-committer-wins registration, which means two concurrent transactions inserting the same pair cannot both succeed, and one of them gets a clean rejection instead of a corrupted table. From PHP, the constraint rides along in the same createTable call as the columns, because the column descriptors and the constraint set are both pass-through payloads to the daemon:

$db->createTable('pages', [
    ['id' => 1, 'name' => 'tenant_id', 'ty' => 'int64',   'primary_key' => false, 'nullable' => false],
    ['id' => 2, 'name' => 'slug',      'ty' => 'varchar', 'primary_key' => false, 'nullable' => false],
    ['id' => 3, 'name' => 'title',     'ty' => 'varchar', 'primary_key' => false, 'nullable' => false],
    ['id' => 4, 'name' => 'body',      'ty' => 'json',    'primary_key' => false, 'nullable' => true],
], constraints: [
    'uniques' => [
        ['id' => 1, 'name' => 'uq_pages_tenant_slug', 'columns' => [1, 2]],
    ],
]);

$db->put('pages', [1 => 7, 2 => 'pricing', 3 => 'Pricing']);
$db->put('pages', [1 => 7, 2 => 'pricing', 3 => 'Pricing v2']);
// ConstraintException: the (tenant_id, slug) pair already exists

The columns array holds column ids, not names, which matches how the rest of the wire format addresses columns, and notice what is not in that table: there is no primary key at all, which is legal, because the composite unique constraint is doing the identity work and nothing else needs a handle. The second put fails with the typed ConstraintException, which carries an errorCode you can switch on (and an opIndex when the failure comes out of a multi-op batch commit), so your catch block can tell a uniqueness violation apart from a foreign key or check failure without string matching. One behavior to know before you rely on it: the constraint follows SQL semantics for nulls, so a row where any of the constrained columns is NULL sits outside the uniqueness check entirely, the same way UNIQUE behaves in Postgres, and if nulls should be rejected you say so with 'nullable' => false on the columns rather than expecting the constraint to do it.

When the surrogate id earns its keep

None of this means the auto-increment column is always wrong, because a single-column identity is genuinely convenient when other tables need to point at this row, when you want the where('pk', ...) point lookup or a delete by key to be one cheap operation, or when the natural key is long strings you would rather not repeat in every foreign reference. That is what the surrogate key is for in MongrelDB: one int64 primary key with 'auto_increment' => true, the engine fills the value at insert time, and the composite unique constraint still guards the natural key underneath it, so the integer is a handle, not a substitute for integrity:

$db->createTable('readings', [
    ['id' => 1, 'name' => 'id',        'ty' => 'int64',   'primary_key' => true, 'nullable' => false,
     'auto_increment' => true],
    ['id' => 2, 'name' => 'sensor_id', 'ty' => 'int64',   'primary_key' => false, 'nullable' => false],
    ['id' => 3, 'name' => 'day',       'ty' => 'date',    'primary_key' => false, 'nullable' => false],
    ['id' => 4, 'name' => 'value',     'ty' => 'float64', 'primary_key' => false, 'nullable' => false],
], constraints: [
    'uniques' => [
        ['id' => 1, 'name' => 'uq_readings_sensor_day', 'columns' => [2, 3]],
    ],
]);

The rule of thumb is the one the old-timers used before the ORMs made the decision for us: if the natural key is short, stable, and never updated, let the unique constraint carry it and skip the surrogate; if the key is wide, or ten child tables need to reference it, or you expect to re-key rows, pay for the integer. What you should not do is add the id column out of reflex and then forget the unique constraint, because that combination gives you the storage cost of both designs and the integrity of neither, which is exactly the failure the “always auto-increment” habit was supposed to prevent and somehow manages to cause instead.