H2 database: referring to a table in root schema from a foreign key constraint

Question

Given a table in root schema:

CREATE TABLE user (
    username VARCHAR(50),
    password VARCHAR(50));

and a table in Quiz schema:

CREATE TABLE Quiz.Results (
    username VARCHAR(50),
    points INT, 
    FOREIGN KEY (username) REFERENCES user(username));

I'm unable to actually create the foreign key, because the database claims the table user does not actually exist. Neither can I subsequently add the foreign key:

ALTER TABLE QUIZ.RESULTS
    ADD FOREIGN KEY (username) REFERENCES user (username) 

Both tables are, of course, stored in the same database.

Since this is just a piece of homework, I'm more than happy to simply skip adding a foreign key. But I'm curious if this is indeed a limitation in H2, a bug, or if it works as intended.

Can I somehow refer to table user outside the quiz schema?

Solution

You would need to explicitly set the schema name if you refer to a table in a different schema. The default schema name for H2 is public. Example:

CREATE TABLE user (
    username VARCHAR(50),
    password VARCHAR(50));
create schema quiz;
CREATE TABLE Quiz.Results (
    username VARCHAR(50),
    points INT, 
    FOREIGN KEY (username) 
    REFERENCES public.user(username));

To create the foreign key constraint later, use:

ALTER TABLE QUIZ.RESULTS
    ADD FOREIGN KEY (username) 
    REFERENCES public.user(username) ;

Comments

[Ivan Vučica]

Knowing the default schema (what I called the 'root schema') name helped. Thanks!

[Craig Otis]

Can I ask why you're adding the FOREIGN KEY (username) twice? It seems you add it once in the table definition for Quiz.Results, then a second time in your ALTER TABLE statement.

[Thomas Mueller]

@CraigOtis You are right, it should be either one or the other. I changed the answer.