Imagine you have some code that works with data from a table that stores customer orders. Unfortunately your code has a bug – instead of using CustomerId for something it uses OrderId instead (both are fields on the table). What’s worse, your tests pass and the test coverage looks fine. What’s up?
I’ll assume that your tests create test data in some way. It’s very easy to default to creating each kind of thing – order, customer, product etc. – with ids that start with the same value, something like 0 or 1. So your code will read the OrderId, use it as a CustomerId and it will find something when it shouldn’t. Your code is working by accident, not by design. In production, it’s much less likely that this will work.
So it would really help if the different ids look different from each other, to force this issue to become visible (by making your tests fail). If the ids are created manually, then define a different base value per table in a variable in the file that creates the test data, and turn the existing ids into offsets from this base value.
Go from code like this:
insert into order(orderId, customerId, orderRef) values (1, 1, "apple"), (1, 2, "banana"), (2, 1, "carrot")
To code like this
DECLARE @BaseOrderId int, @BaseCustomerId int SELECT @BaseOrderId = 20000 SELECT @BaseCustomerId = 70000 insert into order(orderId, customerId, orderRef) values (@BaseOrderId + 1, @BaseCustomerId + 1,"apple"), (@BaseOrderId + 1, @BaseCustomerId + 2, "banana"), (@BaseOrderId + 2, @BaseCustomerId + 1, "carrot")
My preference is to have ids that are auto-incremented for me. In this case, you can set the value that the auto-incrementing starts from to a different value per table:
DBCC CHECKIDENT ('order', RESEED, 20000); GO DBCC CHECKIDENT ('customer', RESEED, 70000); GO
These examples use SQL Server. As of Oracle 12c it’s available on Oracle, and resetting the auto-incrementing is like this:
ALTER TABLE ORDER MODIFY(ID Generated as Identity (START WITH 20000));