Ivan Vučica Ivan Vučica - 7 months ago 245
SQL Question

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

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

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