Foreign key operation Insertion causes problems during device testing phase

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 pre-populated during schema setup, so that later, when we create a record in the table job

, we can refer to it and not get a foreign key error. To add a record to a table job

, we just need to use a simple one insert command

:

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

      

        
        
        
      

    

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

The only problem is the unit testing stage. I am testing the data access layer and at the beginning of the test the database will be cleared and re-populated with the original data. But the problem is that every time a repopulation is performed, the values statusid

and jobid

will increase. So, no more insertion into the unit test as before, because it statusid

changes all the time.

I am thinking about changing mine insert statement

to be more test friendly .. any ideas?

Edit: This should be a database / ORM agnostic question. Because I am working with SQL Server, MYSQL, MS Access and Propel, NHibernate, etc. So I want a general solution that can be applied to all cases.

Edit 2: Inserting the value of the id field doesn't work. because when you populate the table (via ORM) you cannot specify the id field value, at least Propel will not allow that.