See the schema below. There is clearly no unique index beside the PK, and the UPDATE didn't change any columns in the PK.
CREATE TABLE players (
game INT NOT NULL,
mode INT NULL,
"time" TIMESTAMP NULL,
map INT NULL,
length INT NULL,
build INT NULL,
region INT NULL,
hero INT NULL,
hero_level INT NULL,
team INT NULL,
winner BOOL NULL,
blizzid INT NOT NULL,
skill FLOAT NULL,
battletag STRING COLLATE en_u_ks_level1 NULL,
talents INT[] NULL,
data JSON NULL,
CONSTRAINT "primary" PRIMARY KEY (game ASC, blizzid ASC),
INDEX players_region_blizzid_time_idx (region ASC, blizzid ASC, "time" DESC),
INDEX players_region_battletag_idx (region ASC, battletag ASC),
INDEX players_build_map_mode_hero_level_idx (build ASC, map ASC, mode ASC, hero_level ASC) STORING (hero, winner, skill),
INDEX players_build_map_hero_level_idx (build ASC, map ASC, hero_level ASC) STORING (hero, winner),
INDEX players_build_mode_hero_level_idx (build ASC, mode ASC, hero_level ASC) STORING (hero, winner, skill),
INDEX players_build_hero_level_idx (build ASC, hero_level ASC) STORING (hero, winner),
INDEX players_build_hero_idx (build ASC, hero ASC) STORING (winner, hero_level, length, map, mode),
INDEX players_build_hero_map_mode_hero_level_idx (build ASC, hero ASC, map ASC, mode ASC, hero_level ASC) STORING (winner, talents, skill),
INDEX players_build_hero_map_hero_level_idx (build ASC, hero ASC, map ASC, hero_level ASC) STORING (winner, talents),
INDEX players_build_hero_mode_hero_level_idx (build ASC, hero ASC, mode ASC, hero_level ASC) STORING (winner, talents, skill),
INDEX players_build_hero_hero_level_idx (build ASC, hero ASC, hero_level ASC) STORING (winner, talents),
FAMILY "primary" (game, mode, "time", map, length, build, region, hero, hero_level, team, winner, blizzid, skill, battletag, talents, data)
)
version (master branch at d23c18a + a patch dealing with the job registry): CockroachDB CCL v2.0-alpha.20180212-1682-g3d9738f
Running a job that does a bunch of updates (
update players set skill = $1 where game = $2 and blizzid = $3) and got an error:ERROR: duplicate key value (build,map,mode,hero_level)=(67,14,1,1) violates unique constraint "players_build_map_mode_hero_level_idx" (SQLSTATE 23505)See the schema below. There is clearly no unique index beside the PK, and the UPDATE didn't change any columns in the PK.
For the schema: