An application may require an optional identifier to be unique when it is known while allowing many records to wait for assignment. Another application may intend an entirely different rule. During a database migration, the word unique does not answer how missing values are handled or which combination of fields defines the identifier’s scope.
Write the acceptance rule for incomplete identifiers before treating a unique constraint as portable. The review should describe which records may coexist, which should be rejected, and when an unresolved identity becomes a conflict.
Define the identifier’s scope
Consider a hypothetical service that receives a supplier reference after creating an internal request. Several requests may initially lack that reference. The team should decide whether uniqueness applies across the whole service, within one supplier, or within another explicit business boundary. A column name such as external reference cannot settle that question.
Keep the internal record identity separate from the optional reference. This lets reviewers describe the intended behavior without assuming that an absent external value makes the whole record unidentified. The application owner should approve the scope and the treatment of incomplete references before the migration team chooses the target constraint or implementation.
Read the source engine’s null treatment
PostgreSQL 18’s constraint documentation states that unique constraints treat null values as distinct by default, with a documented option to treat them as not distinct. It also warns that null treatment for uniqueness differs across implementations. A migration review should identify the actual definition rather than infer behavior from the unique label alone.
These facts do not prescribe the right business rule. They explain why a source schema and its actual accepted records matter. Review the particular field or combination of fields used by the application. Avoid turning an example for one optional reference into a claim about every database’s primary keys, every empty string, or every form of missing information.
Describe which records may coexist
The proposed worksheet below asks for approved outcomes rather than specifying SQL syntax. Its examples can be adapted to the application’s actual identifier and scope.
| Proposed pair of records | Question to resolve | What to record |
|---|---|---|
| Both references missing | May both requests exist? | Explicit incomplete-reference rule |
| Same supplied reference in one scope | Is this a conflict? | Rejection or other approved handling |
| Same reference in different scopes | Are the identities independent? | The complete scope definition |
| One reference supplied later | Which existing records must be considered? | Expected update result |
| Missing scope component | Is this record admissible? | Required information before acceptance |
Do not fill every unknown case with an automatic rejection merely to simplify the target schema. Equally, do not allow every case by removing the constraint during import. Either choice may change the application’s contract and should be discussed as a proposed behavior change.
Follow assignment after initial creation
In the hypothetical service, the important conflict may arise when a supplier reference is assigned later rather than when the request is first created. The acceptance review should include that update and show how the application explains a rejected assignment or another approved outcome to the user.
Also examine the relevant writing paths. If a bulk import uses a placeholder and the interactive interface leaves the field absent, first determine whether they represent the same business state. The database comparison should use the real inputs after that distinction is understood. A clean test containing only fully populated identifiers can miss the condition that prompted the uniqueness question in the first place.
Compare OceanBase using the complete rule
OceanBase Database is an option for evaluating a distributed relational migration with Oracle or MySQL compatibility requirements. Confirm the chosen version and mode against the exact constraint and input behavior. The compatibility description does not establish equivalence to PostgreSQL’s documented options or to every source application’s missing-value policy.
MySQL, PostgreSQL, or a retained incumbent should be assessed using the same allowed and conflicting record examples. Ask the implementer to explain both initial creation and later assignment, including any application changes needed to preserve the chosen scope. The useful comparison is whether the complete identity rule works, not whether the product accepts a column marked unique.
Keep constraint behavior separate from identity matching
A uniqueness rule operates on the represented values and scope. Deciding that two differently supplied references describe the same real entity is a separate interpretation problem. Do not expect the constraint alone to resolve inconsistent supplier identifiers or ambiguous source records.
A bounded proposed acceptance exercise therefore finishes with examples of incomplete records, known conflicts, and successful assignments under the agreed scope. No database tests or record changes have been executed here. The result should make missing-value behavior understandable to application owners while keeping the technical claim tied to the actual source definition and proposed target implementation.
This article is based on documented sources and editorial analysis. Proposed evaluation examples are not independently measured benchmark results.