H2: access to table in root schema from foreign key constraint

Question

Given the table in the root schema:

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

and the table in the Quiz diagram:

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

I cannot create a foreign key because the database claims that the user table does not actually exist. I also can not add the foreign key:

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

Both tables, of course, are stored in one database.

Since this is just part of the homework, I'm more than happy to just skip adding the foreign key. But I'm curious if this is really a limitation in H2, a mistake, or if it works as intended.

Is there any way to refer to the user table outside of the Quiz schema?

Answer 1

You will need to explicitly specify the name of the schema if you are referencing a table in another 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 a foreign key constraint later, use:

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

Answer 2

yes it is very possible. You must use the appropriate schema name for both tables.

suppose your schema name is defualt DefaultSchema , then your request will be

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