MongoDb Uniqueness without Index-CodePudding

Is it possible to enforce uniqueness in mongodb on a non-index field? The SQL equivalent would be

    CREATE TABLE short_to_long_map (
    ID int NOT NULL UNIQUE,
    short_url varchar(255) NOT NULL UNIQUE,
    long_url varchar(255) NOT NULL
);
// Will succeed
    INSERT INTO short_to_long_map VALUES (1, 'https://my.url/1234', 'https://long-long-long-url.com');

// Will fail b/c same short URL
    INSERT INTO short_to_long_map VALUES (2, 'https://my.url/1234', 'https://another-long-long-long-url.com')

It seems like it might, but not sure if a proxy collection is the only way.

CodePudding user response：

One option is to use upsert with $setOnInsert: For example:

db.collection.update(
{b: "https://my.url/1234"},
{$setOnInsert: {a: 2, c: "https://another-long-long-long-url.com"}},
{upsert: true})

This query will create a document with fields a, b, c only if there is no document with this value at field b.

You can see it on this playground example.

Running this query when there is a document with the same b value, will return:

WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

If there is no document with the same value at b, it will be created:

can be seen here

CodePudding user response：

You cannot have a unique constraint without an index, an index will be created automatically. See output for your example:

CREATE TABLE SHORT_TO_LONG_MAP (
    ID INTEGER NOT NULL UNIQUE,
    short_url VARCHAR2(255) NOT NULL UNIQUE,
    long_url VARCHAR2(255) NOT NULL
);

Table created.
    
SELECT table_name, index_type, index_name, uniqueness, GENERATED
FROM user_indexes 
WHERE table_name = 'SHORT_TO_LONG_MAP';

 ----------------------------------------------------------------- 
|TABLE_NAME        |INDEX_TYPE|INDEX_NAME    |UNIQUENESS|GENERATED|
 ----------------------------------------------------------------- 
|SHORT_TO_LONG_MAP |NORMAL    |SYS_C00165589 |UNIQUE    |Y        |
|SHORT_TO_LONG_MAP |NORMAL    |SYS_C00165590 |UNIQUE    |Y        |
 -----------------------------------------------------------------