/
Client Database Migration Data script

Client Database Migration Data script

Owned by Luis Leiva (Unlicensed)
Last updated: Nov 11, 2024
4 min read

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 correct DocumentId based on the related Transaction and Agent.

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 the Suspense table, referencing the Id column of the Document table.

  • Purpose: Enforces referential integrity, ensuring that DocumentId in Suspense always points to a valid document in the Document 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 the Suspense table where the SuspenseCodeId is not already associated with a valid SuspenseCode.

  • Purpose: Ensures that all SuspenseCodeId values in Suspense correspond to a valid SuspenseCode, 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 valid SuspenseCode where the value is 0.

  • Purpose: Fixes cases where SuspenseCodeId is set to 0, replacing it with a valid SuspenseCode 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 the Suspense table, referencing the Id column of the SuspenseCode table.

  • Purpose: Enforces referential integrity, ensuring that the SuspenseCodeId in Suspense points to a valid SuspenseCode.

6. Updating CashierByUserId in the Receipt Table:

update dbo."Receipt" set "CashierByUserId"= null where "CashierByUserId"=0

  • Action: Sets CashierByUserId to null in the Receipt table where it is currently set to 0.

  • Purpose: Ensures that invalid values (such as 0) are replaced with null 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 the Receipt table, referencing the Id column of the User table.

  • Purpose: Ensures that CashierByUserId in the Receipt table refers to a valid user in the User 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 to null in the Queue table where it is currently set to 0.

  • Purpose: Fixes invalid DocumentId values in the Queue table by replacing them with null.

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 to null in the Queue table where the DocumentId does not reference a valid Document.

  • Purpose: Ensures that DocumentId in the Queue table always references an existing document in the Document 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 the Queue table, referencing the Id column of the Document table.

  • Purpose: Enforces referential integrity, ensuring that DocumentId in the Queue table points to a valid Document.

Add label

Related content