SQL错误-唯一约束
我有一个这样的数据迁移脚本。
Data_migration.sql
它的内容是
insert into table1 select * from old_schema.table1;
commit;
insert into table2 select * from old_schema.table2;
commit;
table1 具有 pk_productname 约束，当我执行脚本时，
SQL> @ "data_migration.sql"
我将遇到唯一约束（pk_productname）违规。但是当我执行单个sql语句时，我不会收到任何错误。这背后有什么原因。以及如何解决这个问题。
I have one data migration script like this.
Data_migration.sql
It's contents are
insert into table1 select * from old_schema.table1;
commit;
insert into table2 select * from old_schema.table2;
commit;
And table1 has the pk_productname constraint when I execute the script
SQL> @ "data_migration.sql"
I will get an unique constraint(pk_productname) violation. But when I execute the individual sql statements I won't get any error. Any reason behind this. And how to resolve this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
唯一约束失败意味着您正在尝试插入主键列冲突的多条记录之一。
如果在运行脚本时发生这种情况，但在运行单个语句时却没有发生这种情况，那么脚本中一定存在错误。如果没有看到脚本，我们就不可能确定该错误是什么，但最有可能的是您以某种方式运行相同的语句两次。
另一个可能的原因是约束被推迟。这意味着直到交易结束才强制执行。因此，如果您运行 INSERT 语句而不发出后续 COMMIT，则该语句看起来会成功。
在没有启用约束的情况下运行数据迁移是很常见的。之后使用 EXCEPTIONS 表重新启用它们。这使得调查问题变得更加容易。 了解更多信息。
The failure of the unique constraint means you are attempting to insert one of more records whose primary key columns collide.
If it happens when you run a script but not when you run the individual statements then there must be a bug in your script. Without seeing the script it is impossible for us to be sure what that bug is, but the most likely thing is you are somehow running the same statement twice.
Another possible cause is that the constraint is deferred. This means it is not enforced until the end of the transaction. So the INSERT statement would appear to succeed if you run it without issuing the subsequent COMMIT.
It is common to run data migration without enabled constraints. Re-enable them afterwards using an EXCEPTIONS table. This makes it easier to investigate problems. Find out more.