References to other tables

[Gili]

The changelog for version 1.4.179 Beta reads:

• There was a way to prevent a database from being re-opened, by creating a column constraint that references a table with a higher id. This is now detected, and creating the table is prohibited. In future versions of H2, most likely creating references to other tables will no longer be supported because of such problems.

Can you please elaborate? What does it mean for a constraint to reference a table with a higher id? And what do you mean by "in future versions [..] creating references to other tables with no longer be supported"? I assume you're not talking about removing foreign keys? :)

Thanks,

Gili

[Noel Grandin]

On 2014-07-04 05:49 AM, Gili wrote:
>
> Can you please elaborate? What does it mean for a constraint to reference a table with a higher id? And what do you mean
> by "in future versions [..] creating references to other tables with no longer be supported"? I assume you're not
> talking about removing foreign keys? :)

No, this is about constraints referencing tables that are only created later.
So table1 has a check constraint which references table2.
But table2 is created after table1.
Which causes problems during our metadata initialisation at database startup.

[cowwoc]

How is this even possible? When I try creating foreign keys to tables that are only created later,the CREATE TABLE statement fails ("table X does not exist"). Can you provide an example of problematic code?

Also, here is what the changelog actually says: "There was a way to prevent a database from being re-opened, by creating a column constraint that references a table with a higher id. This is now detected, and creating the table is prohibited. In future versions of H2, most likely creating references to other tables will no longer be supported because of such problems."

Consider clarifying the bolded text. Specifically:

• It's not clear what a "table with a higher id" means. If you mean tables that are created later, write that.
• It's not clear that "creating references to other tables will no longer be supported" means limiting forward references exclusively. The sentence sounds as if you plan to remove *all* references.
  

Thanks,
Gili

[Noel Grandin]

On 2014-07-04 08:51 AM, cowwoc wrote:
>
> How is this even possible? When I try creating foreign keys to tables that are only created later,the CREATE TABLE
> statement fails ("table X does not exist"). Can you provide an example of problematic code?
>

We allow general SQL statements in our CHECK constraints, and we don't validate them immediately.

[Thomas Mueller]

Hi,

To find the changed source code, you can go to https://code.google.com/p/h2database/source/list . This case is https://code.google.com/p/h2database/source/detail?r=5707 - test case is here: https://code.google.com/p/h2database/source/diff?spec=svn5707&r=5707&format=side&path=/trunk/h2/src/test/org/h2/test/db/TestCases.java

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[cowwoc]

Makes sense. Please clarify this in the changelog.

Thanks,
Gili

[cowwoc]

How do you plan to prevent such references in the future? Will you validate CHECK constraints at creation time somehow or are you saying you're going to disallow CHECK to reference external tables at all? I hope it's not the latter because I've used it to enforce some interesting use-cases. For example:

CREATE TABLE department_to_operator (department_id BIGINT NOT NULL, operator_id BIGINT NOT NULL,
    PRIMARY KEY (department_id, operator_id),
    FOREIGN KEY(department_id) REFERENCES department(id) ON DELETE CASCADE,
    FOREIGN KEY(operator_id) REFERENCES operator(id) ON DELETE CASCADE,
    CHECK EXISTS (SELECT * FROM department, operator WHERE department.id = department_id AND
        operator.id = operator_id AND department.company_id = operator.company_id));

The CHECK makes sure that the inserted department and operator are associated with the same company.

Gili

On 04/07/2014 3:28 AM, Thomas Mueller wrote:

Hi,

To find the changed source code, you can go to https://code.google.com/p/h2database/source/list . This case is https://code.google.com/p/h2database/source/detail?r=5707 - test case is here: https://code.google.com/p/h2database/source/diff?spec=svn5707&r=5707&format=side&path=/trunk/h2/src/test/org/h2/test/db/TestCases.java

Regards,

Thomas

On Fri, Jul 4, 2014 at 9:12 AM, Noel Grandin <noelg...@gmail.com> wrote:

On 2014-07-04 08:51 AM, cowwoc wrote:

How is this even possible? When I try creating foreign keys to tables that are only created later,the CREATE TABLE
statement fails ("table X does not exist"). Can you provide an example of problematic code?

We allow general SQL statements in our CHECK constraints, and we don't validate them immediately.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/W_gVs24v6eA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.