# Technical Advisory 79281

Publication date: April 12, 2022

## Description

In a multi-region database, calling `IMPORT INTO` on a `REGIONAL BY ROW` table could violate `UNIQUE` constraints, including `PRIMARY KEY` constraints, resulting in duplicate keys.

Affected versions: v21.2.0 to v21.2.7

## Statement

This is resolved in CockroachDB by PR 79293. The fix was applied in the maintenance release of CockroachDB v21.2.8.

This public issue is tracked by 79281.

## Mitigation

Users of CockroachDB v21.2.0 to v21.2.7 who have used `IMPORT INTO` must audit their `REGIONAL BY ROW` tables with `UNIQUE` constraints to ensure these tables do not contain `UNIQUE` constraint violations.

Users should also upgrade to v21.2.8 or a later version.

### v21.2.6 or later

Users of CockroachDB v21.2.6 and later have access to the following built-in functions, which can be used to easily validate `UNIQUE` constraints:

- `SELECT crdb_internal.revalidate_unique_constraints_in_all_tables();`
- `SELECT crdb_internal.revalidate_unique_constraints_in_table('table_name');`
- `SELECT crdb_internal.revalidate_unique_constraint('table_name', 'constraint_name');`

If any constraint fails validation, the functions will return an error with a hint about which data caused the constraint violation. These violations can then be resolved manually by updating or deleting the rows in violation.

### Prior to v21.2.6

Users of CockroachDB releases prior to v21.2.6 may audit the table for duplicate keys by running a query of the following form, for each `UNIQUE` constraint that may have been affected:

    SELECT * FROM t GROUP BY unique_col1 [ unique_col2 ...] HAVING count(*) > 1;

If a query returns any results, this indicates that the table contains `UNIQUE` constraint violations. To fix the violations, update all but one of the duplicate keys with a different value.

## Impact

`REGIONAL BY ROW` tables spanning multiple regions could contain non-unique data in unique columns if `IMPORT INTO` was used to import data with duplicate keys. Versions affected include v21.2.0 to v21.2.7, v22.1.0-alpha.1 to v22.1.0-alpha.5, and v22.1.0-beta.1.

Please reach out to the support team for more information.