# Why do I get errors about UNIQUE KEYs?

SingleStore does not support unique keys unless the columns in the unique key are a superset of the columns in the shard key.

This requirement is necessary because the shard key informs the query execution system which partition contains the searched value(s). Then the unique key index locates the distinct values searched for within the partition and enforces uniqueness within the partition. For more information about shard keys, see [Shard Keys](https://docs.singlestore.com/db/v9.1/create-a-database/optimizing-table-data-structures/shard-keys.md).

## Unique Key Restriction Workarounds

* Have an application enforce the uniqueness of the proposed unique key by confirming the value to be inserted or updated does not exist in the table. Additionally, a non-unique key can be created on the same column to increase execution time.
* Use the `AUTO_INCREMENT` argument for one column and use the `UNIQUE` constraint on the other column. Set the shard key on the column that has the `UNIQUE` constraint on it:
  ```sql
  CREATE TABLE user_emails(
      id BIGINT NOT NULL AUTO_INCREMENT,
      email VARCHAR(140) NOT NULL,
      KEY(id),
      SHARD KEY (email),
      CONSTRAINT emp_email_uk UNIQUE (email)
  );
  ```
  The `AUTO_INCREMENT` column's uniqueness is not enforced directly, but the way the values for it are generated guarantees uniqueness.
* Create two tables for the same data set, but with different keys to enforce uniqueness on different columns. In the following example, the `users` table enforces uniqueness and shards on the `id` column because a primary key acts as both a shard key and a unique index. The `users_email` table enforces uniqueness and shards on the `email` column while adding an additional index on the `id` column.

  A stored procedure can be used to update both tables when a new user is added, so both `UNIQUE` constraints are enforced.
  ```sql
  /* Table #1 */
  CREATE TABLE users (
  id bigint NOT NULL PRIMARY KEY,
  email varchar(140) NOT NULL,
  );

  /* Table #2 */
  CREATE TABLE users_email (
  id bigint NOT NULL 
  email varchar(140) NOT NULL PRIMARY KEY,
  KEY(id),
  );
  ```

***

Modified at: March 14, 2023

Source: [/db/v9.1/reference/troubleshooting-reference/query-errors/why-do-i-get-errors-about-unique-keys/](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/query-errors/why-do-i-get-errors-about-unique-keys/)

(An index of the documentation is available at /llms.txt)