删除限制到更新表 - 当前交易已中止
上下文：我想更新一个名为“项目”的表，并将项目编号从10个将项目名称为“网站”更改为45。该项目表具有PK（PNUMBER属性），并且与另一个名为“ Workson”的表相关，该表具有与Project Taber的主要键Pnumber相关的FK（属性PNO）。
该图将是这样的：
BEGIN TRANSACTION;
ALTER TABLE Workson DROP CONSTRAINT fk_workson_projects;
ALTER TABLE Projects DROP CONSTRAINT pk_projects;
UPDATE Projects
SET pnumber = 45
WHERE pname = 'Website';
ALTER TABLE Workson ADD CONSTRAINT fk_workson_projects FOREIGN KEY (pno) REFERENCES projects(pnumber);
ALTER TABLE Projects ADD CONSTRAINT pk_projects PRIMARY KEY (pnumber);
COMMIT;
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02
放下约束并在交易中再次添加回去？
先感谢您
Context: I want to update a table called "Projects" and change the project number from 10 to 45 where the project name is "Website". This Project table has a PK (pnumber attribute) and its related to another table called "WorksOn" that has a FK (attribute pno) related to the primary key pnumber of Project table.
The diagram would be something like this:
I was trying something like:
BEGIN TRANSACTION;
ALTER TABLE Workson DROP CONSTRAINT fk_workson_projects;
ALTER TABLE Projects DROP CONSTRAINT pk_projects;
UPDATE Projects
SET pnumber = 45
WHERE pname = 'Website';
ALTER TABLE Workson ADD CONSTRAINT fk_workson_projects FOREIGN KEY (pno) REFERENCES projects(pnumber);
ALTER TABLE Projects ADD CONSTRAINT pk_projects PRIMARY KEY (pnumber);
COMMIT;
Essentially dropping the constraints, updating the table and adding the same constraints again but I keep getting this error:
ERROR: current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02
How could I update the information by dropping the constraints and adding them back again inside a transaction?
Thank you in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要在之前创建主键，可以创建一个引用它的外键。查看错误消息会告诉您。
You need to create the primary key before you can create a foreign key that references it. Looking at the error messages would have told you that.