constraint violations in tests

I had some test code that looked something like

@Test public void a() {
  db.insert(0, "a");
  assertThat(db.query(1).getKey()).isEqualTo("a");
}

@Test public void b() {
  db.insert(0, "a");
  db.insert(1, "b");
  assertThat(db.query(2).getKey()).isEqualTo("b");
}

@Test public void c() {
  assertThat(db.query(3)).isNull();
}

There’s some stuff to like about this:

1. pretty straightforward which test is touching which records
2. pretty clear what you expect to see in the database.

There’s a bunch of coincidences here that make this work, though:

• Some JUnit magic that clears the database before running the test.
• no other tests running concurrently
• the database must be non-empty

But it wasn’t working, so I got constraint violation errors about a duplicate of id=1.

The 1/2/3 values are accidental complexity – I have to assign them in my test code, even though that’s normally the database’s job! The first two would be easy to fix with something like:

long id = db.insertReturningId("a");
assertThat(db.query(id).getKey()).isEqualTo("a");

but what about the third? One approach is just do a bunch of queries:

long id = 1;
while (db.query(id) != null) {
  id++;
}
assertThat(db.query(id)).isNull();

but that test always passes, so is it a useful test?

(introduce a new PK, uuid?)

(random id)

(use -1?)