Dropping constraints to update tables - Current transaction is aborted-CodePudding

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

CodePudding user response：

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.