I have a problem. I need to update the primary key column value in table tabA. Table tabA has got a foreign key relation with table tabB. When I try updating table tabA with new values for the primary key column I get an primary key violation error as there are records in the child table with the values which I am goin to update in the parent table. Is the anyway to do this with deleting records in the child table or disabling the relationship between these tables. One roundabout solution I saw was to create a dummy record in tabA and when any record is getting updated in tabA replace the child nodes column values with dummy records and then after inserting the new values update the child table with new values. Can anyone please give me a better Solution.
Thanks in advance.
ALTER TABLE <table_name> CONSTRAINT <constraint_name> INITIALLY DEFERRED;
Here is th explanation:
Specify NOT DEFERRABLE to indicate that this constraint is checked at the end of each DML statement. If you do not specify either word, then NOT DEFERRABLE is the default.
Specify INITIALLY IMMEDIATE to indicate that at the start of every transaction, the default is to check this constraint at the end of every DML statement. If you do not specify INITIALLY, INITIALLY IMMEDIATE is the default.
Specify INITIALLY DEFERRED to indicate that this constraint is DEFERRABLE and that, by default, the constraint is checked only at the end of each transaction.