Problem

When attempting to upgrade your Workbench server, you may see errors such as the below:

[rserver] ERROR Could not update database to latest schema; 

[rserver] ERROR database error 1 (Cannot execute query. Fatal error. ERROR: there is no unique constraint matching given keys for referenced table "tablename"

This error typically occurs when you have previously upgraded, then downgraded, and then again attempted to re-upgrade Workbench. 

Solution

The solution will largely depend on which database you're using.

SQLite3

You can re-generate a new SQLite3 database by removing the old one. Running the command below will back up the database under a new file name, and re-generate the database:

sudo mv /var/lib/rstudio-server/rstudio.sqlite /var/lib/rstudio-server/rstudio.sqlite.backup

Postgres

There are a number of ways to resolve this in Postgres. The simplest method is to create another database on your Postgres host, and point the Workbench server to the new (empty) database.

Note that you will need to migrate the data from your existing Workbench server to the new database that you've created. Also, you will need to update your postgres connection string to point to the new database in /etc/rstudio/database.conf (or in whichever directory that your config map exists in if using Kubnernetes.) More information on this can be found here: 
https://docs.posit.co/ide/server-pro/database/migration.html

Another solution is to revert your database to a previous snapshot.

Support Ticket

If you still have issues after completing the above, you can always lodge a support ticket, where our group of friendly, and incredibly knowledgeable staff can assist with any issues that you may be having. You can submit a ticket here:

https://support.posit.co/hc/en-us/requests/new