Client Database Migration Data script
This SQL script performs a series of database updates and structural modifications to tables. The script ensures data consistency by updating specific columns and establishing foreign key constraints between related tables. This script also aims to fix data integrity issues, such as null and invalid foreign key values, and creates necessary constraints to enforce referential integrity. Below is a step-by-step breakdown of the changes being applied to the database.
Step-by-Step Script
Updating the DocumentId
in the Suspense
Table:
update dbo."Suspense" set "DocumentId" = ( select "Id" from dbo."Document" where "TransactionId" = ( Select "Id" from dbo."Transaction" where "AgentId" = ( Select "Id" from dbo."Agent" where "Name" = 'Migration Agent') ) )
Action: This update sets the DocumentId
in the Suspense
table by matching the AgentId
in the Transaction
table, which is associated with an agent named 'Migration Agent'.
Purpose: Ensures that the
Suspense
table contains the correctDocumentId
based on the relatedTransaction
andAgent
.
2. Adding Foreign Key Constraint on DocumentId
in Suspense
Table:
ALTER TABLE dbo."Suspense" ADD CONSTRAINT "Document_Suspense_DocumentId_fkey" FOREIGN KEY ("DocumentId") REFERENCES dbo."Document" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
Action: Adds a foreign key constraint on the
DocumentId
column in theSuspense
table, referencing theId
column of theDocument
table.Purpose: Enforces referential integrity, ensuring that
DocumentId
inSuspense
always points to a valid document in theDocument
table.
3. Updating the SuspenseCodeId
in the Suspense
Table:
update dbo."Suspense" set "DocumentId" = (
select "Id" from dbo."Document" where "TransactionId" = (
Select "Id" from dbo."Transaction" where "AgentId" = (
Select "Id" from dbo."Agent" where "Name" = 'Migration Agent')
)
)
Action: Updates the
SuspenseCodeId
in theSuspense
table where theSuspenseCodeId
is not already associated with a validSuspenseCode
.Purpose: Ensures that all
SuspenseCodeId
values inSuspense
correspond to a validSuspenseCode
, using a fallback value when necessary.
4. Updating SuspenseCodeId
for Zero Values:
ALTER TABLE dbo."Suspense"
ADD CONSTRAINT "Document_Suspense_DocumentId_fkey" FOREIGN KEY ("DocumentId")
REFERENCES dbo."Document" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;
Action: Updates the
SuspenseCodeId
to a validSuspenseCode
where the value is0
.Purpose: Fixes cases where
SuspenseCodeId
is set to0
, replacing it with a validSuspenseCode
value.
5. Adding Foreign Key Constraint on SuspenseCodeId
in Suspense
Table:
ALTER TABLE IF EXISTS dbo."Suspense"
ADD CONSTRAINT "SuspenseCode_Suspense_SuspenseCodeId_fkey" FOREIGN KEY ("SuspenseCodeId")
REFERENCES dbo."SuspenseCode" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;
Action: Adds a foreign key constraint on the
SuspenseCodeId
column in theSuspense
table, referencing theId
column of theSuspenseCode
table.Purpose: Enforces referential integrity, ensuring that the
SuspenseCodeId
inSuspense
points to a validSuspenseCode
.
6. Updating CashierByUserId
in the Receipt
Table:
update dbo."Receipt" set "CashierByUserId"= null where "CashierByUserId"=0
Action: Sets
CashierByUserId
tonull
in theReceipt
table where it is currently set to0
.Purpose: Ensures that invalid values (such as
0
) are replaced withnull
for consistency.
7. Adding Foreign Key Constraint on CashierByUserId
in Receipt
Table:
ALTER TABLE IF EXISTS dbo."Receipt"
ADD CONSTRAINT "CashierByUser_CashierByUserId_fkey" FOREIGN KEY ("CashierByUserId")
REFERENCES dbo."User" ("Id") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;
Action: Adds a foreign key constraint on the
CashierByUserId
column in theReceipt
table, referencing theId
column of theUser
table.Purpose: Ensures that
CashierByUserId
in theReceipt
table refers to a valid user in theUser
table.
8. Dropping Index idx_receipt_agentid
:
DROP INDEX IF EXISTS dbo.idx_receipt_agentid;
Action: Drops the index
idx_receipt_agentid
if it exists.Purpose: Cleans up unused or obsolete indexes to improve database performance.
9. Updating DocumentId
in the Queue
Table:
update dbo."Queue" set "DocumentId"= null where "DocumentId" = 0;
Action: Sets
DocumentId
tonull
in theQueue
table where it is currently set to0
.Purpose: Fixes invalid
DocumentId
values in theQueue
table by replacing them withnull
.
10. Ensuring Valid DocumentId
References in the Queue
Table:
update dbo."Queue" set "DocumentId"= null WHERE "DocumentId" NOT IN (select "Id" from dbo."Document")
Action: Sets
DocumentId
tonull
in theQueue
table where theDocumentId
does not reference a validDocument
.Purpose: Ensures that
DocumentId
in theQueue
table always references an existing document in theDocument
table.
11. Adding Foreign Key Constraint on DocumentId
in Queue
Table:
ALTER TABLE IF EXISTS dbo."Queue" ADD CONSTRAINT "Document_Queue_DocumentId_fkey" FOREIGN KEY ("DocumentId") REFERENCES dbo."Document" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
Action: Adds a foreign key constraint on the
DocumentId
column in theQueue
table, referencing theId
column of theDocument
table.Purpose: Enforces referential integrity, ensuring that
DocumentId
in theQueue
table points to a validDocument
.