Web Development

Unique Constraints in MariaDB - Let Concurrent Requests Compete at the Database

Written by Adam Muiz Updated: 30 Oct 2026 5 min read
Unique Constraints in MariaDB - Let Concurrent Requests Compete at the Database

Two registration requests ask for the same username at nearly the same time. Each request checks the database, and each sees that the name appears available. If both are then allowed to insert a row, which layer makes sure the database ends with only one owner?

An application-level availability check can produce a helpful early message, but it observes one moment. Another request can write after that check and before the insert. A UNIQUE key moves the final decision to the place where both writes must meet: the database. The useful promise is narrow. MariaDB can reject a stored value or combination that its unique index already contains; it cannot decide whether the business rule was modeled correctly.

This article focuses on that boundary in a small PHP application using MariaDB. It does not replace a general transaction design, and it does not claim that the request arriving first at the web server must win.

Start by naming the one-owner rule

A unique key should express an invariant, not merely accelerate a query. Examples include “one account per username,” “one webhook receipt per provider and delivery ID,” or “one category slug within each site.” Each sentence identifies both the value and its scope.

For a globally unique username, the table can declare a named key:

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT uq_users_username UNIQUE (username)
) ENGINE=InnoDB;

The primary key identifies each row. The additional unique key protects a different rule: two rows may not store usernames that compare equal under the column's collation. Naming the key makes schema inspection and diagnostics easier to understand, although application behavior should not depend on parsing the human-readable error message.

If the same username may exist in different sites or tenants, global uniqueness would be too strong. A composite key can express the smaller scope:

CONSTRAINT uq_users_site_username UNIQUE (site_id, username)

This does not make site_id unique or username unique by itself. It makes the complete pair unique. That distinction is often the difference between a constraint that describes the domain and one that rejects valid data.

The availability check still has a role, but not the last word

A form can check whether a username appears available before submission. That improves feedback, and it may prevent an obviously doomed attempt. It cannot reserve the value merely by reading it.

Consider two independent requests:

  1. Request A queries for river and finds no row.
  2. Request B queries for river and also finds no row.
  3. Both attempt an insert.
  4. MariaDB accepts one stored value and rejects the conflicting write through the unique key.

The exact scheduling is a database and workload detail, not a fair queue promised to the application. What the constraint provides is the final state: both conflicting values do not become committed rows. The application should therefore treat “available” as provisional until the write succeeds.

Trying to close the gap only with a wider application check can add locking and transaction complexity while still missing another writer that does not follow the same code path. A schema constraint covers PHP handlers, imports, maintenance scripts, and future applications that use the same table.

Define what “the same value” means

The word duplicate sounds obvious until strings and missing values are involved. The database applies concrete comparison rules, and those rules need to match the invariant.

NULL is not a reserved empty slot

MariaDB's CREATE TABLE documentation states that a unique key permits multiple rows with NULL in a nullable column. If every account must have a unique username, UNIQUE should therefore be paired with NOT NULL. Otherwise, several rows can truthfully say that no username value is present.

This behavior can be useful when an optional identifier is absent, but it should be a deliberate model. Empty strings, whitespace-only strings, and NULL are also different inputs. Application validation may reject or normalize some of them before the database evaluates uniqueness.

Collation participates in equality

For character columns, uniqueness is not necessarily byte-for-byte identity. MariaDB's error 1062 reference explains that the column's collation determines whether values are duplicates. Under an applicable case-insensitive collation, River and river compare equal. Accent sensitivity and trailing-space treatment can also affect the result.

That can be exactly right for a login name and wrong for another identifier. Do not change a collation merely to silence a duplicate error. First decide which distinctions the domain intends to preserve, then choose and test the character set, collation, validation, and normalization rules together. Changing comparison rules on an established table can reveal collisions that were previously allowed.

Let the insert decide, then translate the expected conflict

MariaDB reports a duplicate unique or primary key as error 1062 with SQLSTATE 23000. PDO exception mode is the default from PHP 8.0, according to the PHP manual. A small handler can attempt the insert and translate only the known duplicate condition:

<?php
$insert = $pdo->prepare(
    'INSERT INTO users (username, display_name)
     VALUES (:username, :display_name)'
);

try {
    $insert->execute([
        'username' => $validatedUsername,
        'display_name' => $validatedDisplayName,
    ]);
} catch (PDOException $error) {
    $sqlState = (string) $error->getCode();
    $driverCode = (int) ($error->errorInfo[1] ?? 0);

    if ($sqlState === '23000' && $driverCode === 1062) {
        throw new DomainException('That username is no longer available.');
    }

    throw $error;
}

The dual check matters. PDO standardizes SQLSTATE, but 23000 is an integrity-constraint class, not a duplicate-only code. The PDOException documentation exposes driver-specific information through errorInfo; MariaDB's narrow duplicate code is 1062. Matching the database's prose is more fragile and can leak a value or index name if the raw message reaches a public response.

The example is illustrative. A real endpoint still needs input validation, authorization, CSRF protection where applicable, safe error logging, and an appropriate HTTP or form response. If several unique keys can fail, error 1062 alone may not identify which domain field conflicted. The application may need a constrained mapping from known key metadata, a follow-up read that respects disclosure rules, or a generic conflict response. It should not tell an unauthenticated visitor that a sensitive email address already has an account unless that disclosure is intentional.

Choose duplicate semantics instead of hiding the error

A duplicate is not always a failed business operation. MariaDB documents several alternatives, but they mean different things:

  • Plain INSERT: reject the conflicting write. This is a clear default when a user must choose another value.
  • INSERT IGNORE: skip a duplicate row and report a warning. This can be appropriate for a consciously designed bulk-import policy, but it can also make a caller believe work happened when a row was omitted.
  • INSERT ... ON DUPLICATE KEY UPDATE: turn a duplicate into an update. This is useful only when the existing row is the intended update target and the caller is authorized to change it.
  • REPLACE: delete the conflicting row and insert a new one. Delete-and-insert semantics can affect identifiers, foreign keys, and triggers, so the familiar word “replace” should not be mistaken for an ordinary update.

The official ON DUPLICATE KEY UPDATE documentation adds an important warning: if more than one unique index matches, only the first is updated, and MariaDB does not recommend this statement for tables with more than one unique index. An upsert is therefore a business policy, not a universal way to avoid exception handling.

Add the rule to existing data cautiously

A new table begins empty; an established table may already contradict the proposed invariant. Audit it using the same column definitions and collation that the key will use. For a nullable email column, a first diagnostic query might be:

SELECT email, COUNT(*) AS row_count
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

Run such a query only in an appropriately protected administrative context because its result contains account data. A non-empty result is not permission to delete arbitrary rows. The duplicates may represent merged accounts, historical imports, case variants, or a rule the application never previously promised. Decide how records should be reconciled, redirected, renamed, or reviewed before changing the schema.

After the data model and records agree, the migration can add the key:

ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

MariaDB's ALTER TABLE documentation says this operation stops with an error when duplicates exist. It also documents ALTER ... IGNORE, which can discard duplicates without control over which rows survive. That is a poor cleanup mechanism when rows have meaning.

Treat the migration as an operational change. ALTER TABLE can wait for metadata locks, and available algorithms and lock levels depend on the MariaDB version, storage engine, and exact operation. Inspect the installed version, test on representative data, keep a recovery plan, and verify the resulting definition with SHOW CREATE TABLE. No universal “online” claim should be inferred from the short SQL example.

What the unique key does not guarantee

A unique key is strong because its claim is limited. It does not:

  • authorize the caller to claim or update a value;
  • prove that a username, email address, or external identifier is genuine;
  • normalize Unicode, whitespace, or application-specific aliases on its own;
  • make several related statements all-or-nothing;
  • coordinate independent databases or remote services;
  • guarantee that the request humans perceive as “first” is the one MariaDB accepts.

Transactions remain necessary when one operation changes several rows together. Foreign keys protect relationships. Validation explains malformed input. Authorization decides who may act. A unique key contributes one separate guarantee: according to its columns and comparison rules, conflicting stored keys are rejected.

A review checklist

  • Write the invariant in one sentence, including its scope.
  • Choose a single-column or composite key that matches that scope.
  • Decide whether missing values are valid; add NOT NULL when absence is not allowed.
  • Test case, accent, whitespace, and normalization examples under the actual collation.
  • Keep availability checks provisional and let the write face the constraint.
  • Handle SQLSTATE and the MariaDB driver code without returning raw database messages.
  • Choose rejection, ignore, update, or replacement semantics from the business operation.
  • Audit existing duplicates and resolve them deliberately before migration.
  • Test concurrent attempts and verify the final row count, not only the response from one request.

Conclusion

The check-then-insert race is not fixed by asking the database the same question more confidently. The availability query can help a person, while the unique key protects the stored invariant when concurrent writers meet. One write may then receive a normal, expected conflict that PHP translates into domain language.

The difficult part is not typing UNIQUE. It is deciding the correct scope, nullability, and comparison rules; choosing what a duplicate should mean for this operation; and reconciling older data without silently destroying it. Once those decisions are explicit, the constraint can do a small but important job reliably: refuse a second owner for a key the schema says belongs to one row.

References