Postgres 17

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE images (
  id INTEGER NOT NULL,
  status VARCHAR(16) NOT NULL,
  image VARCHAR(4096) NOT NULL,

  CHECK (status IN ('pending', 'invalid', 'completed')),
  PRIMARY KEY (id),
  UNIQUE (id, status)
);

CREATE TABLE invalid_images (
  id INTEGER NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'invalid',
  error_message VARCHAR(128) NOT NULL,

  CHECK (status = 'invalid'),
  PRIMARY KEY (id, status),
  FOREIGN KEY (id, status) REFERENCES images (id, status)
);

CREATE TABLE detected_animals (
  image_id INTEGER NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'completed',
  ndx INTEGER NOT NULL,
  latin_name VARCHAR(64),

  CHECK(status = 'completed'),
  PRIMARY KEY (image_id, status, ndx),
  FOREIGN KEY (image_id, status) REFERENCES images (id, status)
);

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT INTO images (id, status, image) VALUES (1, 'pending', '0x1234567');

INSERT 0 1

-- cannot set error message of pending image
INSERT INTO invalid_images (id, error_message) VALUES (1, 'Corrupted JPEG file');

ERROR:  insert or update on table "invalid_images" violates foreign key constraint "invalid_images_id_status_fkey"
DETAIL:  Key (id, status)=(1, invalid) is not present in table "images".

INSERT INTO images (id, status, image) VALUES (2, 'invalid', '0xABCDEF');

INSERT INTO invalid_images (id, error_message) VALUES (2, 'Parse error');

INSERT 0 1

INSERT 0 1

-- cannot change image id=2 to 'completed' state
-- we need to delete a row from invalid_images first
UPDATE images SET status = 'completed' WHERE id = 2;

ERROR:  update or delete on table "images" violates foreign key constraint "invalid_images_id_status_fkey" on table "invalid_images"
DETAIL:  Key (id, status)=(2, invalid) is still referenced from table "invalid_images".

INSERT INTO images (id, status, image) VALUES (3, 'completed', '0xFFFFFFF');

INSERT INTO detected_animals (image_id, ndx, latin_name)
  VALUES (3, 1, 'Felis catus'), (3, 2, 'Ovis aries');

INSERT 0 1

INSERT 0 2

-- cannot record detected animals in invalid and pending images

INSERT INTO detected_animals (image_id, status, ndx, latin_name)
  VALUES (1, 'pending', 1, 'Canis lupus');

ERROR:  new row for relation "detected_animals" violates check constraint "detected_animals_status_check"
DETAIL:  Failing row contains (1, pending, 1, Canis lupus).

INSERT INTO detected_animals (image_id, status, ndx, latin_name)
  VALUES (2, 'invalid', 1, 'Melopsittacus undulatus');

ERROR:  new row for relation "detected_animals" violates check constraint "detected_animals_status_check"
DETAIL:  Failing row contains (2, invalid, 1, Melopsittacus undulatus).