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?

Answer 1

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) ;