Mysterious foreign-key error

[bryce-carson]

At the moment, the package I'm developing uses a single database and the table definitions are as follows.

(mapcar (lambda (expression)
          (emacsql (w-project-database-connection project) expression))

        ;; A list of SQL s-expressions to create the tables.
        '([:create-table-if-not-exists module
           ([module-name content file-name section-name displacement
             module-number]
            (:primary-key [module-number module-name file-name]))]

          ;; FIXME: EmacSQL had an unhandled condition: "foreign key
          ;; mismatch - \"parent_child\" referencing \"module\"", 1,
          ;; nil, "SQL logic error"
          [:create-table-if-not-exists parent-child
           ([(parent integer) (child integer)]
            (:primary-key [parent child])
            (:foreign-key [parent] :references module [module-number]
                          :on-delete :cascade)
            (:foreign-key [child] :references module [module-number]
                          :on-delete :cascade))]

          [:create-table-if-not-exists identifier-used-in-module
           ([identifier-name
             (module-number integer)
             (line-number integer)
             type-of-usage]
            (:primary-key [identifier-name module-number line-number
                           type-of-usage]))]

          [:create-table-if-not-exists topic-referenced-in-module
           ([topic-name (module-number integer)]
            (:primary-key [topic-name module-number]))]))

This results in the following error: EmacSQL had an unhandled condition: "foreign key mismatch - \"parent_child\" referencing \"module\"", 1, nil, "SQL logic error". I have tried deleting the database, deleting the package, and then removing the foreign key constraints but the same error persists despite no foreign key constraint at that time.

Any help or guidance would be appreciated.

[bryce-carson]

I just read over the specification again. It's because I said that parent and child are integers, but I didn't constrain the type of these fields/columns in the module table, eh?

[tarsius]

I don't know and would have to experiment to find out, for which I don't have the time now.

I think the information at https://github.com/magit/emacsql?tab=readme-ov-file#schema is incorrect. As mentioned there and elsewhere in the documentation, everything is stored as strings (except nil for which null is used). integer and float are therefore not valid. I don't know what object is; if it is not the same as "string", then that would also be invalid.

[bryce-carson]

Alright. Thanks for the reply.

[bryce-carson]

It seems that some small syntax changes and removing all type constraints from table schema made it work. Here's my current code.

(mapcar (lambda (expression)
          (emacsql (w-project-database-connection project) expression))

        ;; A list of SQL s-expressions to create the tables.
        '([:create-table :if-not-exists module
           ([modulename content filename sectionname displacement
             modulenumber]
            (:primary-key [modulenumber modulename filename]))]

          [:create-table :if-not-exists parentchild
           ([parent child]
            (:primary-key [parent child])
            (:foreign-keys ))]

          [:create-table :if-not-exists identifierusedinmodule
           ([identifiername modulenumber linenumber typeofusage]
            (:primary-key [identifiername modulenumber linenumber
                           typeofusage]))]

          [:create-table :if-not-exists topicreferencedinmodule
           ([topicname modulenumber]
            (:primary-key [topicname modulenumber]))]))