3

Should I write a test case for every Foreign key in the database?

Our team is conducting a large database migration project using Java APIs, for eg. inputting CustomerSales.

And we have ProductId, VendorId, StoreId, MailCarrierId, etc.

So we are writing test cases for every foreign key column, ensuring if foreign key exists or incorrect. This will lead to 8+ foreign key cases. Its like we are retesting if Microsoft SQL Server knows to enforce its database keys constraints and foreign keys.

Is that the correct methodology? If so, We will have hundreds of foreign key cases for every table we migrate, not just CustomerSales.

Improve this question
5
  • How similar is each Foreign key in structure? Could you just write a method or function that can handle any Foreign Key you want to test?
    – DrewTheTester
    Sep 21, 2021 at 18:22
  • a foreign key is written as similar, ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
    – mattsmith5
    Sep 21, 2021 at 18:33
  • 1
    Can you back up a step? What are you actually trying to test? That the two databases are logically equal? Or that the database conversion program itself works?
    – Kevin McKenzie
    Sep 28, 2021 at 20:04
  • hi @KevinMcKenzie we are migrating from 1 database schema design in Oracle to a total new relational design in MSSQL
    – mattsmith5
    Sep 28, 2021 at 22:03
  • Right, but again, what are you trying to test? The conversion process? That the two databases are logically equal? Or that the front end tool(s) still work after the migration?
    – Kevin McKenzie
    Sep 29, 2021 at 15:06

1 Answer 1

Reset to default
2
+50

I would not use test cases for a database conversion project.

The task at-hand is database validation to see if foreign key constrinsta have been enforced. This can be developed reagrdles of the conversion. I would use database and dba tools and programs.

What to do about missing or duplicate records will depend on your target database - same vendor? same size? etc

Be careful not to get database conversion and database cleanup mixed up until you are sure you want to. Conversely, be carefuly not to put off cleanup to post-conversion - and then never do it which is common in my experiuence.

Improve this answer
2
  • not sure what the differences between these terms are, database conversion , database cleanup, post-conversion
    – mattsmith5
    Sep 21, 2021 at 19:02
  • cleanup: for every foreign key column, ensuring if foreign key exists or incorrect.
    – Michael Durrant
    Sep 21, 2021 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.