Xata•2mo ago

schema migration to add link between users and projects tables.

Trying to run a migration in the xata migration editor. It runs without error but I cannot get the link created. When I use the interactive tool to create the link_to_table it creates a link between the tables but when i try to add a record it gives this error " columns invalid: column [user_xata_id]: column not found " [ { "alter_column": { "table": "projects", "column": "userid", "references": { "name": "fk_users_id", "table": "users", "column": "id", "on_delete": "CASCADE" }, "up": "(SELECT CASE WHEN EXISTS (SELECT 1 FROM users WHERE users.id = userid) THEN userid ELSE NULL END)", "down": "userid" } } ] So not sure what to do? Thanks Andrew

2 Replies

kostas•2mo ago

Hi the pgroll operation to create a link from a table projects to a table users with cascade deletion is as follows. Note however that the "Link" type is a Xata-specific column type which is set to only work with xata_id as the foreign key. Adding a customized link column with an altered foreign key will end up with unpredictable problems.

      {
        "add_column": {
          "table": "projects",
          "column": {
            "pk": false,
            "name": "users",
            "type": "text",
            "unique": false,
            "comment": "{\"xata.link\":\"users\"}",
            "nullable": true,
            "references": {
              "name": "users_link",
              "table": "users",
              "column": "xata_id",
              "on_delete": "CASCADE"
            }
          }
        }
      }

      {
        "add_column": {
          "table": "projects",
          "column": {
            "pk": false,
            "name": "users",
            "type": "text",
            "unique": false,
            "comment": "{\"xata.link\":\"users\"}",
            "nullable": true,
            "references": {
              "name": "users_link",
              "table": "users",
              "column": "xata_id",
              "on_delete": "CASCADE"
            }
          }
        }
      }

Andrew•2mo ago

Thanks for the reply. Worked perfectly.👍

Gaming

Programming

schema migration to add link between users and projects tables.