Publication date: April 12, 2022
Affected versions: v21.2.0 to v21.2.7
This public issue is tracked by 79281.
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
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.
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.