Hi and thanks for your answer.
I already figured out that PUBLIC.T1 works.
Obviously I should have been more clear when explaining the problem.
My goal is not to once set up some tables in the DB and then work on
them manually, but to get the framework to work generically with H2
without causing exceptions.
At the moment it seems to me that H2 behaves inconsistently and I
wonder if this is intentional, because it provokes strange errors.
Maybe a little more detail where the problems lies:
Let's say there's a simple class representing a DB table
class Table{
String schema;
String name;
// constructor etc.
}
(strongly simplified, of course)
The instances representing T1 and T2 would be
Table t1 = new Table(null, "T1");
Table t2 = new Table("test", "T2");
Creating them (generically) in the DB would generate some DDLs with
"CREATE TABLE T1 ..." and "CREATE TABLE test.T2 ...".
All fine so far. No schema means default schema. As everyone might
expect.
But now when using (null / "T1") as a reference to T1 in a FK
definition for T2, providing no schema all of a sudden doesn't mean
default schema any more, but "currently being created table's schema"?
This means you can't consistently use the default schema in a generic
way at all but to be on the safe side, you have to handle H2
effectively as if it doesn't support the default schema.
This is very inconsistent behaviour (and very sad).
Of course I understand the rationale behind it to make DDLs more
"easier" to write by making the DB "cleverly" complete the current
schema at certain points like FK definition when no schema is
supplied.
Problem with this is: This is simplifying something at some point at
the cost of making it broken in the general case (which is a good
example for "quick&dirty")
Or in terms of the quote I heard once, i think from cliff click or so,
"if you want to make something clever and don't do it right, it can
very fast turn to stupid".
So is it intentional that providing no schema means [default schema]
"most of the time" and [current object's schema] "sometimes"?
If so, I have to treat H2 as if it doesn't support the default schema
at all to keep up a clean and consistent program structure.
Any chance of getting a confirmation for the inconsistent behaviour?
*vorsichtigschau* ;)
On Oct 19, 8:48 am, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> Rami is on the right track. H2 assumes the referenced table is in the same
> schema. Try:
>
> drop table test.t2;
> drop table t1;
> create schema test;
> CREATE TABLE T1 (
> col1a INT, col1b INT,
> primary key(col1a, col1b)
> );
> CREATE TABLE test.T2 (
> col2a INT, col2b INT,
> FOREIGN KEY (col2a , col2b)
> REFERENCES public.T1(col1a, col1b)
> );
>
> Regards,
> Thomas
>
>
>
> On Tuesday, October 18, 2011, Rami Ojares wrote:
>
> > CREATE TABLE test.T2 (
> >> col2a INT, col2b INT,
> >> FOREIGN KEY (col2a , col2b) REFERENCES T1(col1a, col1b)
> >> )
>
> >> The error message is:
> >> Table "T1" not found
>
> > The reason for the error is that you are not in test schema (you are
> > probably in the default PUBLIC schema).
> > And thus when you point to table T1 H2 interprets that you mean PUBLIC.T1
> > because you do not qualify
> > the reference with schema.
> > Try
> > CREATE TABLE test.T2 (
> > col2a INT, col2b INT,
> > FOREIGN KEY (col2a , col2b) REFERENCES test.T1(col1a, col1b)
> > )
>
> > - rami
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "H2 Database" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group athttp://groups.google.com/**
> > group/h2-database?hl=en <http://groups.google.com/group/h2-database?hl=en>
> > .
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
