Operation involving foreign key Insertion causes problems during Unit Testing Stage

Question

I have the following schema:

create table statuslookup
{
  statusid int primary key,
  statusname string unique,
  description string
}

create table job
{
  jobid int primary key,
  jobname string unique,
  status_id foreign key reference statuslookup(statusid)
}

The table statuslookup will, of course, be prepopulated during the schema setup so that later we when we create an entry in table job, we can refer to it and not getting a foreign key error. To add an entry in table job, we just have to use a simple insert command:

job = new job();
job.jobname="mytest";
job.status_id=2;

So far so good.. and it works in production environment!

The only problem is during unit testing stage. I test the Data Access Layer and at the beginning of the test, the database will be clear off and repopulated with initial data. But the problem is that every time the repopulation is done, the statusid and jobid will be incremented. So in unit test one can no longer insert like before, because the statusid is changing all the time.

I am thinking about changing my insert statement so that it's more unit-testing friendly.. any ideas?

Edit: This should be a database/ ORM agnostic question. Because I work with SQL server, MYSQL, MS Access and Propel, NHibernate etc. So I want to general solution that can be applied to every case.

Edit 2: Inserting the identity field value doesn't seem to work. because when you populate a table (via ORM), you can't specify the identity field value, at least Propel won't allow that.

Answer 1

Does your database support inserting into the identity field? That is the most obvious answer (to populate the status table with known ID's in the unit test).