Choose a unique constraint for email identities
Compute a tenant-scoped email key, test concurrent uniqueness and compare retired-key reservation with an active-only index and its restoration conflict.

Two account rows can have different identifiers and still claim the same sign-in address. A primary key does not prevent that conflict. A unique constraint can, provided the constrained value expresses the identity policy your application actually intends.
For a backend developer who knows basic inserts and keys, the difficult decision comes before the SQL: does case matter, is identity global or tenant-specific, and does retiring an account release its address? This PostgreSQL example makes those choices visible and tests two concurrent inserts. It preserves the submitted spelling while computing a separate identity key, so different writers cannot supply inconsistent normalized values.
Write down the equivalence rule
The illustrative account policy treats the whole submitted address as case-insensitive within one tenant. It supports only nonempty printable ASCII strings without spaces. It preserves dots and plus signs. Retired accounts retain their keys unless we deliberately switch to the alternative policy later in the article.
That is an application rule, not universal mailbox equivalence. RFC 5321 requires preserving local-part case in SMTP and notes that some hosts distinguish spellings such as smith and Smith, while mailbox domains are case-insensitive. An application choosing whole-address case insensitivity can collapse addresses that a mail system treats separately. Decide whether that tradeoff is compatible with the addresses you support before adopting it.
Provider behavior is another boundary. Google’s Gmail guidance says dots do not distinguish personal Gmail addresses, but explicitly qualifies addresses used through organizations with their own domains. That is no basis for removing dots from every address your product encounters. This example also keeps plus tags unchanged; it does not attempt to infer which different strings reach the same inbox.
Rejecting spaces is different from trimming them. The fixture rejects leading, trailing and embedded spaces rather than silently modifying the submitted value. A form may choose to trim before submission, but the backend contract should still be clear enough for other clients to follow.
Compute the stored key instead of trusting every caller
Run this schema in an empty PostgreSQL database:
CREATE TABLE tenants (id bigint PRIMARY KEY);
CREATE TABLE identities (
id bigint PRIMARY KEY,
tenant_id bigint NOT NULL REFERENCES tenants(id),
email_original text COLLATE "C" NOT NULL,
email_key text COLLATE "C" GENERATED ALWAYS AS (lower(email_original)) STORED NOT NULL,
deleted_at timestamptz,
CONSTRAINT email_printable_ascii CHECK (email_original ~ '^[!-~]+$'),
CONSTRAINT identities_tenant_email_key UNIQUE (tenant_id, email_key)
);
The generated column derives the key from the original spelling. A check that merely required a separately supplied key to be lowercase would be weaker: a caller could submit one address alongside a lowercase key belonging to another. Here the database recomputes the key on insertion and update, and a caller cannot override it with another value. PostgreSQL’s generated-column documentation explains that behavior.
Both text columns use the explicit C collation. The collation reference documents its traditional ASCII behavior and the role of collation in case conversion and pattern matching. Combined with the printable-ASCII check, this gives the example a narrow, inspectable normalization domain. It is not a complete international email-address design.
Nor is the regular expression a mailbox parser. It rejects empty strings, spaces, controls and non-ASCII characters, but accepts not-an-address. The local fixture confirms that acceptance intentionally. Syntax validation, length limits appropriate to the application, deliverability and mailbox verification belong to additional boundaries. Do not use the existence of this row as proof that someone owns an inbox.
The unique-constraint rule covers the combined tenant and key. Both are non-null, avoiding the default behavior in which nulls can be distinct for uniqueness. The tenant foreign key establishes that the tenant exists; it does not authorize the caller to create an identity there.
Observe both stored spelling and lookup behavior
Load the same address spelling family in two tenants:
INSERT INTO tenants VALUES (1), (2);
INSERT INTO identities (id, tenant_id, email_original) VALUES
(200, 1, 'Alice@Example.com'),
(201, 2, 'ALICE@example.com');
SELECT id, tenant_id, email_original, email_key
FROM identities ORDER BY id;
SELECT id FROM identities
WHERE tenant_id = 1 AND email_key = lower('ALICE@EXAMPLE.COM' COLLATE "C");
The two keys are both alice@example.com, but the rows are allowed because their tenants differ. Their original spellings remain different. The lookup returns identity 200 because it includes tenant one and applies the same case conversion to the search value.
If sign-in starts without a known tenant, this schema has not answered which account should be selected. Resolve that ambiguity in the product’s sign-in flow or choose global uniqueness instead. Dropping the tenant condition from a query is not a harmless simplification of a tenant-scoped identity model.
The PostgreSQL 17.11 checks reject another mixed-case Alice in tenant one with 23505. They reject an explicitly supplied generated key with 428C9, null input with 23502, an absent tenant with 23503, and the chosen character-policy violations with 23514. Updating the original address recalculates the stored key; changing it to another row’s existing key fails and preserves the prior spelling and key.
The fixture also inserts three separate keys for dotted, undotted and plus-tagged spellings. Their distinctness is expected under this application policy. A unique constraint does not discover provider aliases, and discovering an alias would not by itself establish which local account should inherit history or permissions.
Let the final write settle a concurrent conflict
A preliminary select is advisory. Two requests can both find no identity for a tenant and key before either inserts. The useful guarantee comes from the unique constraint at the write boundary, not from timing the lookups carefully.
The two-connection experiment deliberately held the first insertion uncommitted. A second insertion used a different original case but the same generated key. The second connection actually waited on a database lock. When the first transaction committed, the waiting insertion failed with 23505, leaving exactly one identity.
A second run changed the first transaction’s outcome to rollback. The waiting insertion then succeeded, and its identifier was the only stored identity for that key. This distinction matters: “the second request always loses” is not the rule. PostgreSQL waits for the conflicting transaction’s outcome and rechecks, as described in its unique-check implementation notes.
An API should handle the final conflict as an expected outcome, rather than blindly create another account or retry the same insert forever. Whether the public response reveals that an address is already registered is a separate interface decision. The database error need not expose its constraint name or the conflicting account’s details.
These checks exercised PostgreSQL connections, not an HTTP signup service or verification email. The winning insert reserves a storage key. It does not settle mailbox ownership, complete authentication or make the whole signup workflow atomic with an external mail provider.
Decide whether a retired account keeps its address
With the initial full unique constraint, retiring an identity does not release the key:
UPDATE identities SET deleted_at = '2026-09-21T00:00:00Z' WHERE id = 200;
SELECT id, email_key, deleted_at IS NOT NULL AS retired
FROM identities WHERE tenant_id = 1 ORDER BY id;
Identity 200 now has a retirement timestamp, but another Alice in tenant one still conflicts. This can preserve an account identity across temporary deactivation. It can also prevent a different person from registering an address that has genuinely changed ownership. Neither outcome is automatically correct for every product.
If active accounts alone must be unique, a partial unique index can exclude retired rows. The following is an experiment on the same disposable schema, inside a transaction that is rolled back afterward. It is not a procedure for changing a live identity system:
BEGIN;
ALTER TABLE identities DROP CONSTRAINT identities_tenant_email_key;
CREATE UNIQUE INDEX identities_active_email_key
ON identities (tenant_id, email_key) WHERE deleted_at IS NULL;
INSERT INTO identities (id, tenant_id, email_original)
VALUES (202, 1, 'alice@example.com');
SELECT id, deleted_at IS NULL AS active
FROM identities WHERE tenant_id = 1 ORDER BY id;
ROLLBACK;
The query shows identity 200 inactive and the newly inserted identity 202 active. Rollback removes the replacement and restores the original full unique constraint as well as the pre-experiment data. The SQL demonstrates the changed rule, without leaving the example permanently switched to it.
The companion checks also keep that alternative configuration long enough to attempt restoring identity 200. Clearing its retirement timestamp fails with 23505, because identity 202 now owns the active key. A second active replacement fails too. An active-only lookup must include the same retirement condition; otherwise it can find both the old record and the new one.
Address reuse therefore changes more than a database index. Password resets, invitations and old links must identify the intended account, not silently attach historical authority to whichever active row currently has the email. The fixture does not implement those flows; it makes the collision and restoration consequence observable so they can inform the design.
Change the rule as an identity migration
A future rule that removes punctuation, changes case mapping or expands the supported character set can map old keys differently. Compute proposed keys before switching writes, group collisions by the intended tenant scope and decide how each collision will be resolved. A migration that silently chooses one surviving row can assign the wrong history to an account even when every final constraint passes.
A generated key reduces disagreement among current writers, but it does not make a normalization change free. Reads, account creation, address changes, verification and recovery need to agree on the same mapping. Coordinate that transition and check existing rows before changing the expression or index. The short transaction above demonstrates semantics only, not an online migration plan or lock-duration estimate.
For a practical exercise, keep the full unique rule and retire identity 200, then predict the results of a same-tenant insert, a tenant-two lookup and an attempted key override. Next compare those results with the active-only alternative and its failed restoration. You should be able to explain each outcome using the chosen identity scope and lifecycle, while keeping syntax, ownership and authorization separate from uniqueness.