更新 Postgres 中引用的列
当多个列之一引用另一个列时,我正在努力寻找更新该列同时保持引用完整性的最佳方法。 例如,如果我有一个标签和描述表以及两个条目:
Label | Description
------------------------------------
read | This item has been read
READ | You read this thing already
现在,我不需要这些重复项。 我想向列添加一个约束,不允许存在不区分大小写的重复值,如示例中所示。 但是,我有几行其他几个表引用“READ”,即我想删除的表。
我知道 Postgres 知道其他行的哪些字段正在引用它,因为只要它们在那里我就无法删除它。 那么,我怎样才能将引用此的任何字段更新为“读取”? 这只是一个例子,我其实有几个地方想这样做。 另一个例子实际上是几个表的 int 主键,我想在其中添加一个新表作为现有表扩展的一种“基表”,因此它们现在都需要具有唯一的 ID,这意味着更新他们拥有的。
我愿意接受我可以添加的功能、我可以使用的工具或其他任何东西的食谱。
When there are one of more columns that reference another, I'm struggling for the best way to update that column while maintaining referential integrity. For example, if I have a table of labels and descriptions and two entries:
Label | Description
------------------------------------
read | This item has been read
READ | You read this thing already
Now, I don't want these duplicates. I want to add a constraint to the column that doesn't allow values that are case-insensitively duplicates, as in the example. However, I have several rows of several other tables referencing 'READ', the one I want to drop.
I know Postgres knows which fields of other rows are referencing this, because I can't delete it as long as they are there. So, how could I get any field referencing this to update to 'read'? This is just an example, and I actually have a few places I want to do this. Another example is actually an int primary key for a few tables, where I want to add a new table as a sort of 'base table' that the existing ones extend and so they'll all need to have unique IDs now, which means updating the ones they have.
I am open to recipes for functions I can add to do this, tools I can utilize, or anything else.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您有许多行引用 READ,您可以将外键更改为级联更新,更新该表集 Label = 'read' 其中 Label = 'READ' 并且所有内容都会自动修复。 之后,您可以再次将约束更改为之前的样子。
要查找引用该列的所有表,您可以使用
If you have many rows referencing READ, you could alter the foreign key to be on cascade update, update that table set Label = 'read' where Label = 'READ' and everything will get automagically fixed. After that you can alter the constraint again to be as it was before.
To find all the tables referencing the column, you can use
将来,您可以在“label”列上创建唯一索引,例如:
或者检查 手册。
这将帮助您避免下次出现这种情况。
For the future you could create an unique index on the column "label", for example:
Or check the manual.
That would help you to avoid this situation for the next time.