PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist #20738

poscogrubb · 2025-08-06T20:53:20Z

poscogrubb
Aug 6, 2025

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

Yes

The bug

After upgrading from v1.135.3 to v1.136.0, immich_server outputs the following error:

immich_server            | PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist

The OS that Immich Server is running on

Linux 5.10.60-qnap

Version of Immich Server

v1.136.0

Version of Immich Mobile App

v1.136.0

Platform with the issue

Server
Web
Mobile

Your docker-compose.yml content

#
# WARNING: Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.
#

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - 2283:2283
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always

  redis:
    container_name: immich_redis
    image: docker.io/redis:6.2-alpine@sha256:d6c2911ac51b289db208767581a5d154544f2b2fe4914ea5056443f62dc6e900
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
    volumes:
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    healthcheck:
      test: pg_isready --dbname='${DB_DATABASE_NAME}' || exit 1; Chksum="$$(psql --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' --tuples-only --no-align --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      # start_interval: 30s
      start_period: 5m
    command: ["postgres", "-c" ,"shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"]
    restart: always

volumes:
  model-cache:

Your .env content

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/share/CACHEDEV1_DATA/x/immich
# The location where your database files are stored
DB_DATA_LOCATION=./postgres

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
TZ="US/Eastern"

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
# IMMICH_VERSION=release
IMMICH_VERSION=v1.135.3

# Connection secret for postgres. You should change it to a random password
DB_PASSWORD=redacted

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

Edit .env to change IMMICH_VERSION from v1.135.3 to v1.136.0
docker compose pull && docker compose up -d; docker compose logs -f

Relevant log output

immich_server            | [Nest] 7  - 08/06/2025, 4:23:38 PM    WARN [Microservices:DatabaseRepository] Migration "1752267649968-StandardizeNames" failed
immich_server            | PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist
immich_server            |     at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
immich_server            |     at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
immich_server            |     at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |     at Socket.emit (node:events:518:28)
immich_server            |     at addChunk (node:internal/streams/readable:561:12)
immich_server            |     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |     at Readable.push (node:internal/streams/readable:392:5)
immich_server            |     at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
immich_server            |   severity_local: 'ERROR',
immich_server            |   severity: 'ERROR',
immich_server            |   code: '42704',
immich_server            |   file: 'pg_constraint.c',
immich_server            |   line: '872',
immich_server            |   routine: 'get_relation_constraint_oid'
immich_server            | }
immich_server            | microservices worker error: PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist, stack: PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist
immich_server            |     at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
immich_server            |     at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
immich_server            |     at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |     at Socket.emit (node:events:518:28)
immich_server            |     at addChunk (node:internal/streams/readable:561:12)
immich_server            |     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |     at Readable.push (node:internal/streams/readable:392:5)
immich_server            |     at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
immich_server            | microservices worker exited with code 1
immich_server            | Killing api process
immich_server exited with code 0

Additional information

I tried to follow the suggestions in #20530, but it seems that my missing constraints are a little different, so I am unsure what SQL commands I need to fix my db schema.

Output of docker exec immich_server sh -c 'DB_URL=postgres://$DB_USERNAME:$DB_PASSWORD@database:5432/$DB_DATABASE_NAME npm run migrations:debug && cat migrations.sql'

> [email protected] migrations:debug
> node ./dist/bin/migrations.js debug


Wrote migrations.sql
-- UP
ALTER TABLE "asset_faces" ADD CONSTRAINT "FK_02a43fd0b3c50fb6d7f0cb7282c" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "FK_420bec36fc02813bddf5c8b73d4" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "exif" ADD CONSTRAINT "FK_c0117fdbc50b917ef9067740c44" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target

Answered by jrasm91

Aug 7, 2025

There are records in these tables that are orphans (assetId columns with values that don't exist in the asset table, for example). Those records can/need to be safely deleted before you can add the constraint.

View full answer

jrasm91 · 2025-08-07T00:34:53Z

jrasm91
Aug 7, 2025
Maintainer

Yes, you should run those 4 statements on your database

5 replies

poscogrubb Aug 7, 2025
Author

The 4 statements generate errors:

psql (14.10 (Debian 14.10-1.pgdg120+1))
Type "help" for help.

immich=# ALTER TABLE "asset_faces" ADD CONSTRAINT "FK_02a43fd0b3c50fb6d7f0cb7282c" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "asset_faces" violates foreign key constraint "FK_02a43fd0b3c50fb6d7f0cb7282c"
DETAIL:  Key (assetId)=(acc5fc95-2461-4358-9bdb-9ef555dde5d2) is not present in table "assets".
immich=# ALTER TABLE "asset_job_status" ADD CONSTRAINT "FK_420bec36fc02813bddf5c8b73d4" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "asset_job_status" violates foreign key constraint "FK_420bec36fc02813bddf5c8b73d4"
DETAIL:  Key (assetId)=(7a23f897-42eb-4aee-b80b-7ce97ca9554e) is not present in table "assets".
immich=# ALTER TABLE "exif" ADD CONSTRAINT "FK_c0117fdbc50b917ef9067740c44" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "exif" violates foreign key constraint "FK_c0117fdbc50b917ef9067740c44"
DETAIL:  Key (assetId)=(04473cc7-afc7-4d83-8b13-76a7f9f5051c) is not present in table "assets".
immich=# ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "assets" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "smart_search" violates foreign key constraint "smart_search_assetId_fkey"
DETAIL:  Key (assetId)=(9bebb363-ade7-4736-b939-52776f055b7d) is not present in table "assets".

jrasm91 Aug 7, 2025
Maintainer

There are records in these tables that are orphans (assetId columns with values that don't exist in the asset table, for example). Those records can/need to be safely deleted before you can add the constraint.

Answer selected by poscogrubb

poscogrubb Aug 7, 2025
Author

~~What's the SQL to do that?~~ Never mind; I figured it out. Thanks.

DELETE FROM asset_faces af
WHERE NOT EXISTS ( SELECT 1 FROM public.assets a WHERE a.id = af."assetId" );
DELETE FROM asset_job_status ajs
WHERE NOT EXISTS ( SELECT 1 FROM public.assets a WHERE a.id = ajs."assetId" );
DELETE FROM exif ee
WHERE NOT EXISTS ( SELECT 1 FROM public.assets a WHERE a.id = ee."assetId" );
DELETE FROM smart_search ss
WHERE NOT EXISTS ( SELECT 1 FROM public.assets a WHERE a.id = ss."assetId" );

jrasm91 Aug 7, 2025
Maintainer

Perfect! Were you able to upgrade then?

poscogrubb Aug 10, 2025
Author

Yes, I'm all updated now. Thanks for the help.

poscogrubb · 2025-08-10T03:21:43Z

poscogrubb
Aug 10, 2025
Author

Resolved errors by deleting dangling references in database tables.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Uh oh!

PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist #20738

Uh oh!

{{title}}

Uh oh!

Replies: 2 comments 5 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Uh oh!

PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist #20738

Uh oh!

poscogrubb Aug 6, 2025

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

The bug

The OS that Immich Server is running on

Version of Immich Server

Version of Immich Mobile App

Platform with the issue

Your docker-compose.yml content

Your .env content

Reproduction steps

Relevant log output

Additional information

Replies: 2 comments · 5 replies

Uh oh!

jrasm91 Aug 7, 2025 Maintainer

Uh oh!

poscogrubb Aug 7, 2025 Author

Uh oh!

jrasm91 Aug 7, 2025 Maintainer

Uh oh!

Uh oh!

poscogrubb Aug 7, 2025 Author

Uh oh!

jrasm91 Aug 7, 2025 Maintainer

Uh oh!

poscogrubb Aug 10, 2025 Author

Uh oh!

poscogrubb Aug 10, 2025 Author

poscogrubb
Aug 6, 2025

Replies: 2 comments 5 replies

jrasm91
Aug 7, 2025
Maintainer

poscogrubb Aug 7, 2025
Author

jrasm91 Aug 7, 2025
Maintainer

poscogrubb Aug 7, 2025
Author

jrasm91 Aug 7, 2025
Maintainer

poscogrubb Aug 10, 2025
Author

poscogrubb
Aug 10, 2025
Author