BUG: UI SQL construction does not respect FK reference. Assumes reference is to PK column.

[MrChadMWood]

Description

While imputing values in a foreign key column (one referencing a UNIQUE constrained column), the row could not be SET. An error occurred resulting from violation of the FK constraint, because the frontend queried the database while assuming the FK column referenced the PK column.

Expected behavior

The application should check the database's foreign key constraint for which column it references.
Then, it should use that column when constructing SQL queries for data insertion. Even if the column is not a PK.

This is expected because Postgres allows FKs on UNIQUE constrained columns. OLAP architecture will typically have higher redundancy for faster read speeds -- so I would imagine this design can be expected often.

To Reproduce

1. Create a project directory with 3 files:

• a: docker-compose.yml
• b: create_database.sql
• c: .env

docker-compose.yml

version: "3.9"
# Please refer the documentation located at https://docs.mathesar.org/installation/docker-compose/ for the setting up Mathesar using this docker-compose.yml.

services:

  db:
    image: postgres:${PG_VERSION-13}
    container_name: mathesar_db
    environment:
    # These environment variables are used to create a database and superuser when the `db` service starts.
    # Refer to https://hub.docker.com/_/postgres for more information on these variables.
      - POSTGRES_DB=${POSTGRES_DB-mathesar_django}
      - POSTGRES_USER=${POSTGRES_USER-mathesar}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD-mathesar}
    expose:
      - "5432"
    volumes:
      - postgresql_data:/var/lib/postgresql/data
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -d $${POSTGRES_DB-mathesar_django} -U $${POSTGRES_USER-mathesar}"]
      interval: 5s
      timeout: 1s
      retries: 30
      start_period: 5s
    
  postgresdb:
    image: postgres:16
    container_name: postgres_db
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - PGDATA=/data/postgres
      - POSTGRES_DB=lineitem101
      - PGPORT=5433
    volumes:
       - ./postgres_data/:/data/postgres
       - ./create_database.sql:/docker-entrypoint-initdb.d/init.sql
       - ./lists/:/data/constants/
    expose:
      - "5433"
    healthcheck:
      test: "pg_isready -U \"$$POSTGRES_USER\" -d \"$$POSTGRES_DB\""
      interval: 10s
      timeout: 2s
      retries: 10
      start_period: 5s
    restart: unless-stopped

   # A caddy reverse proxy sitting in-front of all the services.
   # It is responsible for routing traffic to the respective services
   # It is also responsible for serving static files, automatically providing SSL certificate
   # and preventing certain DDOS attacks
  caddy-reverse-proxy:
    image: mathesar/mathesar-caddy:latest
    environment:
      - DOMAIN_NAME=${DOMAIN_NAME-http://localhost}
    # caddy container is exposed to the other containers, and the host.
    ports:
      - "${HTTP_PORT-80}:80"
      - "${HTTPS_PORT-443}:443"
    volumes:
      - media:/mathesar/media
      - static:/mathesar/static
      - caddy_data:/data
      - caddy_config:/config
    labels:
      - "com.centurylinklabs.watchtower.enable=true"

  # A gunicorn WSGI HTTP Server that
  # runs the Mathesar App.
  # It depends on the `db` service
  # and will start the `db` service automatically before starting itself.
  service:
    container_name: mathesar_service
    image: mathesar/mathesar-prod:latest
    environment:
      - MODE=${MODE-PRODUCTION}
      - DEBUG=${DEBUG-False}
      - DJANGO_ALLOW_ASYNC_UNSAFE=true
      - DJANGO_SETTINGS_MODULE=${DJANGO_SETTINGS_MODULE-config.settings.production}
      - ALLOWED_HOSTS=${ALLOWED_HOSTS-*}
      - SECRET_KEY=${SECRET_KEY}
      - DJANGO_DATABASE_URL=${DJANGO_DATABASE_URL-postgres://mathesar:mathesar@mathesar_db:5432/mathesar_django}
      - MATHESAR_DATABASES=${MATHESAR_DATABASES-(mathesar_tables|postgresql://mathesar:mathesar@mathesar_db:5432/mathesar)}
      - DJANGO_SUPERUSER_PASSWORD=${DJANGO_SUPERUSER_PASSWORD}
    entrypoint: ./run.sh
    volumes:
      - static:/code/static
      - media:/code/media
    depends_on:
      # Comment the below field to disable starting the database service automatically
      db:
        condition: service_healthy
      postgresdb:
        condition: service_healthy
    labels:
      - "com.centurylinklabs.watchtower.enable=true"
    healthcheck:
      test: curl -f http://localhost:8000
      interval: 10s
      timeout: 5s
      retries: 30
      start_period: 5s
    # On prod, the HTTP port is exposed to other containers, but not the host to prevent any unnecessary conflicts with external services.
    # Do not make any changes to this port
    expose:
      - "8000"
  # A webserver responsible for
  # receiving upgrade requests and upgrading the Mathesar App docker image.
  #  It upgrades the docker image only when a http request is sent to it
  # For more information refer https://containrrr.dev/watchtower/http-api-mode/

  watchtower:
    image: containrrr/watchtower
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
    command: --http-api-update --label-enable --debug
    environment:
      - WATCHTOWER_HTTP_API_TOKEN=mytoken
    labels:
      - "com.centurylinklabs.watchtower.enable=false"
    # Watchtower HTTP API is exposed to other containers, but not the host.
    expose:
      - "8080"
  
volumes:
  postgresql_data:
  media:
  static:
  caddy_data:
  caddy_config:

create_database.sql

CREATE DATABASE lineitem101;

\c lineitem101

CREATE TABLE IF NOT EXISTS cost_type (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL UNIQUE,
    label VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS cost_code (
    id SERIAL PRIMARY KEY,
    label VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS unit_of_measure (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL UNIQUE,
    label VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS job (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS subjob (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL UNIQUE,
    label VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS subitem (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS lineitem (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    job VARCHAR NOT NULL,
    subjob VARCHAR NOT NULL,
    cost_code VARCHAR NOT NULL,
    cost_type VARCHAR NOT NULL,
    subitem VARCHAR NOT NULL,
    cost_per_measure INT NOT NULL,
    unit_of_measure VARCHAR NOT NULL,
    note VARCHAR,

    FOREIGN KEY(job) REFERENCES job(name),
    FOREIGN KEY(subjob) REFERENCES subjob(name),
    FOREIGN KEY(cost_code) REFERENCES cost_code(label),
    FOREIGN KEY(cost_type) REFERENCES cost_type(name),
    FOREIGN KEY(subitem) REFERENCES subitem(name),
    FOREIGN KEY(unit_of_measure) REFERENCES unit_of_measure(name)
);

.env

ALLOWED_HOSTS='.localhost, 127.0.0.1, [::1]'
SECRET_KEY=2gr6ud88x=(p855_5nbj_+7^bw-iz&n7ldqv%94mjaecl+b9=4
DJANGO_DATABASE_URL=postgres://mathesar:mathesar@mathesar_db:5432/mathesar_django
MATHESAR_DATABASES=(mathesar_tables|postgresql://mathesar:mathesar@mathesar_db:5432/mathesar),(lineitem101|postgresql://postgres:password@postgres_db:5433/lineitem101)
## Uncomment the setting below to put Mathesar in 'demo mode'
# DJANGO_SETTINGS_MODULE=demo.settings
DOMAIN_NAME=http://localhost

2. docker-compose up and navigate to localhost.
3. Fill in some sample data on all the tables (except lineitem)
4. Try completing a row in the lineitem table

[kgodey]

Thank you for reporting this, @MrChadMWood!

[pavish]

A quick look at this bug: The frontend currently seems to send only the PK of the selected row from the linked table while the backend expects it to be the value of FK column. It is certainly easier for the frontend to only deal with PKs w.r.t the record selector, but here it needs to send the value of the correct FK column.

I'm adding the frontend label to this issue. If we can make some changes to the API to help avoid figuring this out at the frontend, it would be ideal, so I'm also retaining the backend label.