FK Column Is Not Given Dropdown / Selector -- Only Raw Text Input

[MrChadMWood]

Description

Foreign key column only accepts raw input. It is not showing a down carrot in the cell, and clicking does not generate a popup of records.

Expected behavior

Clicking a cell in a FK column should produce a popup.

To Reproduce

I am unsure what is causing the issue. You can attempt replicating my database and see if that reproduces the issue. I checked the browser console and did not find any errors of note.

Attempt adding values to the historic_quantity or historic_cost table:

Environment

• OS: Linux
• Browser: Firefox
• Mathesar: 0.1.3
• Engine: Docker

Additional context

My database:

CREATE DATABASE estimations101;

-- Attach databse
\c estimations101;

CREATE SCHEMA IF NOT EXISTS historic_costs;

SET search_path = historic_costs;

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

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

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

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

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

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

    UNIQUE(name, cost_code_id),
    FOREIGN KEY (cost_code_id) REFERENCES cost_code(id)
);

CREATE TABLE IF NOT EXISTS unit_of_measure (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    unit_scope_id BIGINT NOT NULL,

    UNIQUE(name, unit_scope_id),
    FOREIGN KEY (unit_scope_id) REFERENCES unit_scope(id)
);

CREATE TABLE IF NOT EXISTS measurement_definition (
    id SERIAL PRIMARY KEY,
    unit_of_measure_id BIGINT NOT NULL,
    project_attribute_id BIGINT NOT NULL,
    subitem_id BIGINT NOT NULL,

    FOREIGN KEY(unit_of_measure_id) REFERENCES unit_of_measure(id),
    FOREIGN KEY(project_attribute_id) REFERENCES project_attribute(id),
    FOREIGN KEY(subitem_id) REFERENCES subitem(id),
    UNIQUE(unit_of_measure_id, project_attribute_id, subitem_id)
);

CREATE TABLE IF NOT EXISTS historic_quantity (
    id SERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    measurement_definition_id BIGINT NOT NULL,
    quantity INT NOT NULL,

    FOREIGN KEY(project_id) REFERENCES project(id),
    FOREIGN KEY(measurement_definition_id) REFERENCES measurement_definition(id),
    UNIQUE(project_id, measurement_definition_id),
    CONSTRAINT count_not_negative CHECK (quantity >= 0)
);

CREATE TABLE IF NOT EXISTS historic_cost (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    project_id BIGINT NOT NULL,
    measurement_definition_id BIGINT NOT NULL,
    subitem_id BIGINT NOT NULL,
    cost_type_id BIGINT NOT NULL,
    cost_per_measure INT NOT NULL,
    cost_total INT,
    note VARCHAR,

    FOREIGN KEY(project_id) REFERENCES project(id),
    FOREIGN KEY(measurement_definition_id) REFERENCES measurement_definition(id),
    FOREIGN KEY(subitem_id) REFERENCES subitem(id),
    FOREIGN KEY(cost_type_id) REFERENCES cost_type(id),
    CONSTRAINT cost_provided CHECK (cost_total IS NOT NULL OR cost_per_measure IS NOT NULL)
);

-- Inserrt constant values
INSERT INTO unit_scope (id, name) VALUES (0, 'PROJECT'), (1, 'SUBITEM');
INSERT INTO project_attribute (id, name) VALUES (0, 'SELF');
INSERT INTO cost_type (id, name) VALUES (0, 'STANDARD');

CREATE OR REPLACE VIEW pretty_measurement_definition AS (
    SELECT
        md.id,
        unit_of_measure.name AS unit_of_measure,
        unit_scope.name AS scope,
        COALESCE(project_attribute.name, subitem.name) AS measured_obj,
        cost_code.name AS cost_code
    FROM measurement_definition md
    JOIN unit_of_measure ON md.unit_of_measure_id = unit_of_measure.id
    JOIN unit_scope ON unit_of_measure.unit_scope_id = unit_scope.id
    JOIN project_attribute ON md.project_attribute_id = project_attribute.id
    JOIN subitem ON md.subitem_id = subitem.id
    JOIN cost_code ON subitem.cost_code_id = cost_code.id
);

CREATE OR REPLACE VIEW pretty_historic_quantity AS (
    SELECT
        hq.id,
        project.name AS project,
        md.unit_of_measure,
        md.scope,
        md.measured_obj,
        quantity,
        md.cost_code
    FROM historic_quantity hq
    JOIN project ON hq.project_id = project.id
    JOIN pretty_measurement_definition md 
        ON md.id = hq.measurement_definition_id
);

CREATE OR REPLACE VIEW pretty_historic_cost AS (
    SELECT
        hc.id,
        date,
        md.unit_of_measure,
        md.scope,
        md.measured_obj,
        subitem.name AS cost_of,
        cost_per_measure,
        cost_total,
        cost_type.name AS cost_type,
        note
    FROM historic_cost hc
    JOIN project ON hc.project_id = project.id
    JOIN pretty_measurement_definition md 
        ON md.id = hc.measurement_definition_id
    JOIN subitem ON hc.subitem_id = subitem.id
    JOIN cost_type ON hc.cost_type_id = cost_type.id
);

CREATE INDEX idx_measurement_definition_id ON measurement_definition (id);
CREATE INDEX idx_historic_quantity_id ON historic_quantity (id);
CREATE INDEX idx_historic_cost_id ON historic_cost (id);
CREATE INDEX idx_hc_md_id ON historic_cost (measurement_definition_id);
CREATE INDEX idx_hq_md_id ON historic_quantity (measurement_definition_id);
CREATE INDEX idx_project_id ON project (id);
CREATE INDEX idx_subitem_id ON subitem (id);
CREATE INDEX idx_cost_code_id ON cost_code (id);
CREATE INDEX idx_cost_type_id ON cost_type (id);
CREATE INDEX idx_unit_of_measure_id ON unit_of_measure (id);

[MrChadMWood]

FYI, seems related to using a CHECK constraint on a table. Removing the constraint resolves the issue,

[seancolsen]

@MrChadMWood

Thank you for this detailed bug report! I've spent some time investigating this issue, and your SQL made this very straightforward for me to reproduce.

On the latest develop, I am able to reproduce the issue as you described it. The constraints API responds with HTTP 500 errors.

I'm confident this issue is a duplicate of #3109, which has an open PR #3243.

Using the code from that PR, I'm no longer able to reproduce this issue.

With that fix applied:

• That the icon for each FK column appears as a link, indicating that Mathesar has determined those columns to be foreign key columns:

  

• I'm able to open the record selector on FK cells as shown below:

  

The PR #3243 should be merged soon (once we sort out some changes to our CI pipeline). That means that this fix should be in our upcoming 0.1.4 release which we expect to have out sometime in the next several weeks.

If you have any further questions or concerns, please feel free to reply here and we can re-open this ticket if needed.