允许唯一的密钥列将空值用作密钥而不是忽略

发布于 2025-02-07 13:20:35 字数 2057 浏览 2 评论 0原文

我是使用Graph QL的新手，我正在尝试为其中一个表设置一个唯一的键。

对于上下文，将根据part_number + agrompance_id的组合确定表的键。因此，每个组织ID只能有一个part_number，但是不同的组织可以具有相同的部分_number。

我遇到的问题是anchancy_id是一个可确定的字段。无效时，这代表全局数据。因此，从本质上讲，我希望它的作用与组织_id相同。

即，如果我有part_number：abc123，我想强制执行每个组织中只有一个存在其中一个，而其中只有一个不存在，而没有anchancy_id。

目前，我有一个唯一的密钥设置，可用于product_pn_organization_id，对于具有组织ID的产品，一切正常，但是一旦组织ID为null Graph，null Graph就完全忽略了唯一的密钥约束。因此，当我使用product_pn_organization_id限制part_number上的product_pn_organization_id限制时：abc123 agristomation_id：null（假设已经存在）而不是更新行时，它会创建一个新行。

如果我使用agrishate_id（part_number：abc123，agryagy_id：1）运行相同的插入，再次假设已经存在此行），它将更新列而不是创建新的行。

目前，我能想到的唯一解决方案是创建一个代表“全局”的组织，并将其作为默认的agryagy_id，以便组织_id实际上永远不会null。但是，如果可能的话，我宁愿避免这种情况。

希望有人对如何在这里前进有一些建议。谢谢！

根据请求，以下是突变：

此突变将一个新的行插入NULL的新行。

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC123"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

理想情况下，此查询将从第一个查询更新该行，而是创建一个新行。

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC124"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

此查询插入相同的PN，但使用agristomation_id插入。

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC123", organization_id: "00000000-0000-0000-0000-000000000000"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

与第二个查询不同，此查询实际上更新了属于组织_id/pn组合的行，而不是创建新行。

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC124", organization_id: "00000000-0000-0000-0000-000000000000"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

原文

I am new to using graph ql, and I am trying to set up a unique key for one of my tables.

For context, the table's key will be determined on the combination of part_number + organization_id. So each organization ID can only have one part_number, but different organizations can have the same part_number.

The issue I am running into is that organization_id is a nullable field. When null, this represents global data. So essentially, I want it to act the same as an organization_id.

IE, if I had the part_number: ABC123, I want to enforce that only one of those exist for each organization_id AND only one of those exists for a row with no organization_id.

Currently, I have a unique key set to product_pn_organization_id, and everything works fine for products with an organization ID, but as soon as the organization ID is null graph ql completely ignores the unique key constraint. So when I run an insert mutation with the product_pn_organization_id constraint on a part_number: ABC123 organization_id: null (assuming this already exists) instead of updating the row, it creates a new row.

If I run the same insert with an organization_id (part_number: ABC123, organization_id: 1, again assuming this row already exists) it will update the columns instead of creating a new row.

Right now, the only solution I can think of is creating an organization that represents 'global' and having that as the default organization_id so that organization_id is never actually null. However, I would rather avoid that if possible.

Hoping someone has some advice on how to move forward here. Thanks!

Per request, here are the mutations:

This mutation inserts a new row with organization_id set to null.

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC123"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

Ideally, this query would update the row from the first query, but instead creates a new row.

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC124"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

This query inserts the same PN but with an organization_id.

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC123", organization_id: "00000000-0000-0000-0000-000000000000"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

Unlike the second query, this query actually updates the row belonging to the organization_id/pn combination instead of creating a new row.

mutation MyMutation {
  insert_products(objects: {pn: "ABC123", manufacturer_pn: "MANABC124", organization_id: "00000000-0000-0000-0000-000000000000"}, on_conflict: {constraint: products_pn_organization_id_key, update_columns: manufacturer_pn}) {
    returning {
      id
    }
  }
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╰ゝ天使的微笑 2025-02-14 13:20:35

您可能在Postgres DB的顶部运行，并且需要更新到V 15以获得支持。更多信息在这里，并摘录：

在Postgres 14及以上版本中，独特的约束总是处理null
值不等于其他空值。如果您要插入空
值到表，您有一个唯一的约束，零值是
被认为是独特的。零总是不同于
另一个空。当您将五个记录插入
“ old_null_style”表“ val1”总是相同的值
“你好”，然后“ val2”始终为空。
即使您有一个独特的约束，实际上支持您
插入五次或您想要的五次，因为您
具有使每一行与另一行不同的零值，并且
因为唯一的约束包括“ val1”和“ val2”，所以所有
行是唯一的。