Prompt

My table

                                             Table "public.usersdefaults"

┌────────────────────────────────────────┬──────────────────────────┬───────────┬──────────┬──────────────────────────────────┐
│ Column │ Type │ Collation │ Nullable │ Default │
├────────────────────────────────────────┼──────────────────────────┼───────────┼──────────┼──────────────────────────────────┤
│ usersdefaults_id │ integer │ │ not null │ generated by default as identity │
│ usersdefaults_datecreated │ timestamp with time zone │ │ not null │ CURRENT_TIMESTAMP │
│ usersdefaults_datemodified │ timestamp with time zone │ │ │ │
│ usersdefaults_usercreated │ text │ │ not null │ CURRENT_USER │
│ usersdefaults_usermodified │ text │ │ not null │ CURRENT_USER │
│ usersdefaults_users │ integer │ │ not null │ │
│ usersdefaults_backlinkset │ integer │ │ │ │
│ usersdefaults_wwwset │ integer │ │ │ │
│ usersdefaults_numberoflatestentries │ integer │ │ not null │ 200 │
│ usersdefaults_currencies │ integer │ │ │ │
│ usersdefaults_maingpgkey │ text │ │ │ │
│ usersdefaults_personaldiaryset │ integer │ │ │ │
│ usersdefaults_diaryyear │ text │ │ not null │ 'Year %Y'::text │
│ usersdefaults_diarymonth │ text │ │ not null │ '%B'::text │
│ usersdefaults_diaryday │ text │ │ not null │ '%Y-%m-%d'::text │
│ usersdefaults_screenshots │ integer │ │ │ │
│ usersdefaults_aimemory │ integer │ │ │ │
│ usersdefaults_aimemorysubtype │ integer │ │ │ │
│ usersdefaults_agendadayspan │ integer │ │ not null │ 60 │
│ usersdefaults_prospectingmethodsubtype │ integer │ │ │ │
│ usersdefaults_wrsdir │ text │ │ not null │ '~/tmp/'::text │
│ usersdefaults_markup │ integer │ │ not null │ 1 │
│ usersdefaults_llmmodels │ integer │ │ │ │
│ usersdefaults_quicknotetype │ integer │ │ │ │
│ usersdefaults_quicknotesubtype │ integer │ │ │ │
│ usersdefaults_quicknoteset │ integer │ │ │ │
│ usersdefaults_setvoicemessages │ integer │ │ │ │
└────────────────────────────────────────┴──────────────────────────┴───────────┴──────────┴──────────────────────────────────┘
Indexes:
"usersdefaults_pkey" PRIMARY KEY, btree (usersdefaults_id)
"usersdefaults_usersdefaults_users_key" UNIQUE CONSTRAINT, btree (usersdefaults_users)
Foreign-key constraints:
"usersdefaults_usersdefaults_aimemory_fkey" FOREIGN KEY (usersdefaults_aimemory) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_aisubtype_fkey" FOREIGN KEY (usersdefaults_aimemorysubtype) REFERENCES hyobjectsubtypes(hyobjectsubtypes_id)
"usersdefaults_usersdefaults_backlinkset_fkey" FOREIGN KEY (usersdefaults_backlinkset) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_currencies_fkey" FOREIGN KEY (usersdefaults_currencies) REFERENCES currencies(currencies_id)
"usersdefaults_usersdefaults_llmmodels_fkey" FOREIGN KEY (usersdefaults_llmmodels) REFERENCES llmmodels(llmmodels_id)
"usersdefaults_usersdefaults_markup_fkey" FOREIGN KEY (usersdefaults_markup) REFERENCES markuptypes(markuptypes_id)
"usersdefaults_usersdefaults_personaldiaryset_fkey" FOREIGN KEY (usersdefaults_personaldiaryset) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_prospectingmethodsubtype_fkey" FOREIGN KEY (usersdefaults_prospectingmethodsubtype) REFERENCES hyobjectsubtypes(hyobjectsubtypes_id)
"usersdefaults_usersdefaults_quicknoteset_fkey" FOREIGN KEY (usersdefaults_quicknoteset) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_quicknotesubtype_fkey" FOREIGN KEY (usersdefaults_quicknotesubtype) REFERENCES hyobjectsubtypes(hyobjectsubtypes_id)
"usersdefaults_usersdefaults_quicknotetype_fkey" FOREIGN KEY (usersdefaults_quicknotetype) REFERENCES hyobjectypes(hyobjectypes_id)
"usersdefaults_usersdefaults_screenshots_fkey" FOREIGN KEY (usersdefaults_screenshots) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_setvoicemessages_fkey" FOREIGN KEY (usersdefaults_setvoicemessages) REFERENCES hyobjects(hyobjects_id)
"usersdefaults_usersdefaults_users_fkey" FOREIGN KEY (usersdefaults_users) REFERENCES users(users_id)
"usersdefaults_usersdefaults_wwwset_fkey" FOREIGN KEY (usersdefaults_wwwset) REFERENCES hyobjects(hyobjects_id)
Triggers:
insert_username_usersdefaults BEFORE INSERT OR UPDATE ON usersdefaults FOR EACH ROW EXECUTE FUNCTION insert_username('usersdefaults_usermodified')
usersdefaults_moddatetime BEFORE UPDATE ON usersdefaults FOR EACH ROW EXECUTE FUNCTION moddatetime('usersdefaults_datemodified')

Request

Need to constrain usersdefaults_aimemory to hyobjects_id which hyobjects_hyobjectsubtypes must be usersdefaults_aimemorysubtype

Response

To enforce the constraint that usersdefaults_aimemory must reference a hyobjects_id where the corresponding hyobjects_hyobjectsubtypes is equal to usersdefaults_aimemorysubtype, you can use a check constraint. This constraint will ensure that the referenced hyobjects_id in the hyobjects table matches the hyobjects_hyobjectsubtypes in the hyobjectsubtypes table.

Here is how you can add the check constraint:

ALTER TABLE usersdefaults
ADD CONSTRAINT check_aimemory_subtype
CHECK (
    (usersdefaults_aimemory IS NULL AND usersdefaults_aimemorysubtype IS NULL) OR
    EXISTS (
        SELECT 1
        FROM hyobjects
        JOIN hyobjectsubtypes ON hyobjects.hyobjectsubtypes_id = hyobjectsubtypes.hyobjectsubtypes_id
        WHERE hyobjects.hyobjects_id = usersdefaults_aimemory
          AND hyobjects.hyobjectsubtypes_id = usersdefaults_aimemorysubtype
    )
);

This check constraint ensures that either both usersdefaults_aimemory and usersdefaults_aimemorysubtype are null, or there exists a row in the hyobjects table where the hyobjects_id matches usersdefaults_aimemory and the hyobjectsubtypes_id matches usersdefaults_aimemorysubtype.

Explanation:

1. NULL Case: If both usersdefaults_aimemory and usersdefaults_aimemorysubtype are null, the constraint is satisfied.
2. Existence Check: If neither are null, the constraint checks if there is a row in the hyobjects table where:
   • The hyobjects_id matches usersdefaults_aimemory.
   • The hyobjectsubtypes_id matches usersdefaults_aimemorysubtype.

By adding this check constraint, you ensure that the usersdefaults_aimemory and usersdefaults_aimemorysubtype columns are always consistent with the relationships defined in the hyobjects and hyobjectsubtypes tables.

Evaluation

It did not work.

Alternative with Deepseek

CREATE OR REPLACE FUNCTION validate_aimemory_subtype()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the referenced hyobjects_id has the correct hyobjectsubtypes
    IF NEW.usersdefaults_aimemory IS NOT NULL AND NEW.usersdefaults_aimemorysubtype IS NOT NULL THEN
        IF NOT EXISTS (
            SELECT 1
            FROM hyobjects
            WHERE hyobjects_id = NEW.usersdefaults_aimemory
              AND hyobjects_hyobjectsubtypes = NEW.usersdefaults_aimemorysubtype
        ) THEN
            RAISE EXCEPTION 'The referenced hyobjects_id does not match the required hyobjectsubtypes.'
                  USING ERRCODE = '23503';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

and

CREATE TRIGGER enforce_aimemory_subtype_trigger
BEFORE INSERT OR UPDATE ON usersdefaults
FOR EACH ROW
EXECUTE FUNCTION validate_aimemory_subtype();

the solution by Deepseek worked correctly.

I don't think that such request is complex, though 7B OneSQL can't handle it.

Not even Phi-3.5-mini-instruct, and not even Phi-4.