Derby Foreign Key Constraint

What's the problem with this statement that gives me the following exception? s.addBatch('CREATE TABLE category ( id SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY \n' +

Solution 1:

There are many errors in the sql statement!

• No primary key is defined for the category.
• The type of id in category and cat_id in task don't match.
• As PM77-1 has noted the spaces aren't respected.

Anyway the working code is the following:

s.addBatch("CREATE TABLE category ( id  INT NOT NULL GENERATED ALWAYS AS IDENTITY \n"+"\t(START WITH 0, INCREMENT BY 1), title  VARCHAR(100), "+"\tCONSTRAINT category_pk_id PRIMARY KEY (id))\n" );
s.addBatch("CREATE TABLE task (id SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY \n"+"\t(START WITH 0, INCREMENT BY 1), title VARCHAR(100), cat_id INT, visible BOOLEAN, "+"deprecated BOOLEAN,"+"\t CONSTRAINT task_pk_id PRIMARY KEY (id),"+"\t CONSTRAINT fk_cat_id FOREIGN KEY (cat_id)\n"+"\t REFERENCES category(id))");

But there will still be an error in the code that these operations aren't atomic and if one happens and the next goes wrong the first isn't rolled back. It is not unsolved.