CODEWITHSUNDEEP
uniqueauto-incrementsinglestore
Kokizzu
Kokizzu

Reputation: 26888

How to prevent duplicate row insert in MemSQL?

I have AUTO_INCREMENT PRIMARY KEY and another column that I can't set UNIQUE because unlike standard RDBMS like MySQL or PostgreSQL, MemSQL only allow only one of them, not both.

Is there workaround to prevent duplicate rows without sacrificing the auto_increment column?

I can use unique as primary key and use atomic counter in other product/service like Redis/atomic variable, but when I need to update the unique column I have to delete it first then reinsert, which is bad/unpreferred way for me..

Upvotes: 0

Views: 475

Answers (1)

Jack Chen
Jack Chen

Reputation: 1214

MemSQL does support multiple unique keys together with a primary key. However, MemSQL requires that the columns in each unique or primary key must be a superset of the columns in the shard key - i.e. that all values that would be considered duplicate under each unique key have the same shard key, so that they get mapped to the same partition. This further implies that all the unique/primary keys must share at least one column in common.

For your case, it is not possible to have both a unique/primary key on the autoincrement column and the other column. But you can have a unique/primary key on the other column, without a unique key on the auto_increment column - just define it as a non-unique key. The automatically generated values will still be unique. Do note that then the table won't be able to enforce uniqueness if you manually insert values that are duplicate with other auto_increment values.

Upvotes: 2

Related Questions