When you are writing xUnit tests you are in certain ways trying to make sure that the test breaks when the code that is being tested changes the assumptions you made when writing the Test and Production code.
Similarly if you are relying on the database to throw a error when you put invalid data, then you should write a test around this assumption, so that when someone changes the database to not behave the way you assumed it to behave, the test you wrote will break and it will force the team to think about the change to the database that is being undertaken. If the change is really required, then the team would fix the test else rollback the change being made.
Here is a example.
public void testShouldNotCreateEmployeeWhenHireDateGreaterThanTerminationDate() {
employee.setEmployeeID(employeeId);
employee.setHireDate(hireDate);
employee.setTerminatedDate(terminatedDate);
boolean hadException = false;
try {
employeeGateway.insert(employee);
} catch (SQLException e) {
hadException = true;
}
assertTrue("Termination date before hiredate should not be allowed by the database", hadException);
}
On the Employee table, we have a Check constraint that checks if the TerminationDate is before HireDate. When a employee with hiredata greater than termination date is entered the database will throw a exception. This functionality provided by the database can be assumed by the application. If this database design changes the team will need to change all the affected code, hence its better to put a test around this assumption and fail the test suite when the database design changes.
The failed test will force the team to look at why the database design was the way it was designed and what other places in the code need to change if the database design changes. Obviously you can do this validation in the application before you persist the Employee object to the database. In situations where the database is also being accessed by other applications, its better to delegate the data quality constraints to the database, instead of relying on other apps to do the right thing.
Another way to do the above test would be as suggested by Sudhindra.
public void testShouldNotCreateEmployeeWhenHireDateGreaterThanTerminationDate() {
employee.setEmployeeID(employeeId);
employee.setHireDate(hireDate);
employee.setTerminatedDate(terminatedDate);
try {
employeeGateway.insert(employee);
fail("Termination date before hiredate should not be allowed by the database");
} catch (SQLException e) {
// expected SQLException
}
}