H2 and FOREIGN KEY

Question

I ran into a problem:

I was going to create 2 tables in H2. When I tried to create the second table, an error occured:

column C_TASK not found

Here is the code:

CREATE TABLE S_TASK (C_TASK INT PRIMARY KEY, N_TASK VARCHAR(255), POINT_COUNT INT);

CREATE TABLE S_ACHIEVEMENT(C_ACHIEVEMENT INT PRIMARY KEY, N_REWARD VARCHAR(255), PICTURE VARCHAR(255), FOREIGN KEY (C_TASK) REFERENCES S_TASK(C_TASK), EXEC_COUNT INT);

Solution

Did Not Define Linking Field

You are defining a relationship (a foreign key) for a column that does not exist in table S_ACHIEVEMENT. A foreign key a link between a column on a child table (the "many" table) and a column on the parent table (the "one" table). Your code says you wish to link from a field "C_TASK" on "S_ACHIEVEMENT" but there is no "C_TASK" field on that table.

Example

Take for example customers (parent table) who have 0 or more invoices (child table), and every invoice must be owned by exactly one customer. Similarly, the invoice table is in turn a parent to the line items table.

You would have tables and columns:

• customer_
  • name_
  • phone_
  • uuid_ (primary key)
• invoice_
  • invoice_number_
  • date_of_invoice_
  • uuid_ (primary key)
  • fk_customer_uuid_ (foreign key)
• line_item_
  • item_number_
  • product_sold_
  • cost_
  • uuid_ (primary key)
  • fk_invoice_uuid_ (foreign key)

On the invoice_ table you would define:

ALTER TABLE invoice_ ADD FOREIGN KEY ( fk_customer_uuid_ ) REFERENCES customer_( uuid_ ) ;

and

ALTER TABLE line_item_ ADD FOREIGN KEY ( fk_invoice_uuid_ ) REFERENCES invoice_( uuid_ ) ;

Those columns marked "(foreign key)" is what you forgot to define on your child table.

See this posting, H2 Alter Table Add Foreign Key, for another example.