DBUnit - integrity constraint violation: foreign key no action; SYS_FK_10556

Question

I am currently coding test with dbunit(using hsqldb). However, I have a huge Problem at initializing the db:

here`s the code:

/**
     * Init before a test starts
     */
    @Before
    public void initialise() {
        IDataSet dataSetRating = null;
        IDataSet dataSetMovie = null;

        log.info("enter init test");

        try {
            con = datasource.getConnection();
            icon = new DatabaseConnection(con);
            File rating = new File("./src/test/resources/startDatabaseRating.xml");         
            dataSetRating = new FlatXmlDataSetBuilder().build(rating);
            DatabaseOperation.CLEAN_INSERT.execute(icon, dataSetRating);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e);
            System.exit(-1);
        }
    }

my create statement looks like that:

CREATE TABLE Rating
(
    rid INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mid INTEGER FOREIGN KEY REFERENCES Movie(movieId), 
    rating INTEGER NOT NULL, 
);

and my startDatabaseRating.xml looks like that:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <Rating rid="0" mid="0" rating="1" />
    <Rating rid="1" mid="0" rating="2" />
    <Rating rid="2" mid="0" rating="3" />
    <Rating rid="3" mid="0" rating="4" />

    <Movie movieid="0" title="Movie1" moviePath="C" />
    <Movie movieid="1" title="Movie2" moviePath="D" />
</dataset> 

When I run the tests I get:

java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: foreign key no action; SYS_FK_10556 table: RATING

Why do I get this exception, cause there is still a dataset in the *.xml file. How to fix this problem?

UPDATE:

CREATE TABLE Movie
(
    movieId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    moviePath VARCHAR(500) NOT NULL
);

Answer 1

When inserting a rating, the movie that is referenced must already exist in the movie table. So you are inserting the rows in the wrong order.

You need to first insert the movies, then the ratings.

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <Movie movieid="0" title="Movie1" moviePath="C" />
    <Movie movieid="1" title="Movie2" moviePath="D" />

    <Rating rid="0" mid="0" rating="1" />
    <Rating rid="1" mid="0" rating="2" />
    <Rating rid="2" mid="0" rating="3" />
    <Rating rid="3" mid="0" rating="4" />
</dataset>