MERGE allows inserting of rows which violate foreign key constraints

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Run the following (with H2 console):
CREATE TABLE IF NOT EXISTS themes (theme_name VARCHAR(256) PRIMARY KEY);

CREATE TABLE IF NOT EXISTS application_instances (
    application_instance_id BIGINT PRIMARY KEY,
    theme_name VARCHAR(256),
    FOREIGN KEY(theme_name) REFERENCES themes(theme_name));

2. Observe that the 'themes' table is empty.

3. Attempt to insert a row in the table referencing 'themes', with an
invalid value for the foreign key field, using INSERT keyword.

INSERT INTO APPLICATION_INSTANCES (application_instance_id, theme_name)
    VALUES (1, 'theme that does not exist: 1');

4. Observe that this correctly fails with a constraint violation.

Referential integrity constraint violation: "CONSTRAINT_3D:
PUBLIC.APPLICATION_INSTANCES FOREIGN KEY(THEME_NAME) REFERENCES
PUBLIC.THEMES(THEME_NAME)";

5. Attempt an equivalent insert, also with an invalid foreign key, this
time using MERGE keyword.

MERGE INTO APPLICATION_INSTANCES (application_instance_id, theme_name)
    VALUES (2, 'theme that does not exist: 2');

6. This should raise a similar constraint violation - but it does not;
instead it inserts the row in application_instances with the supplied
invalid item in the foreign key field.

Update count: 1

What is the expected output?

MERGE inserts should still check foreign key constraints

What do you see instead?

MERGE allows inserting of rows which violate foreign key constraints

What version of the product are you using?

1.2.133

On what operating system, file system, and virtual machine?

Ubuntu 9.10, ext3.
java -version
java version "1.6.0_18"
Java(TM) SE Runtime Environment (build 1.6.0_18-b07)
Java HotSpot(TM) 64-Bit Server VM (build 16.0-b13, mixed mode)

Do you know a workaround?

No.

How important/urgent is the problem for you?

Blocker - data consistency bug, prevents us using this version. Will remain
with the 1.1.117 release we previously used.

In your view, is this a defect or a feature request?

Defect.

Original issue reported on code.google.com by memor...@gmail.com on 14 Apr 2010 at 9:57

[GoogleCodeExporter]

Thanks! I will try to fix in in the next release.

Original comment by thomas.t...@gmail.com on 20 Apr 2010 at 6:51

• Changed state: Started

[GoogleCodeExporter]

Fixed in version 1.2.134

Original comment by thomas.t...@gmail.com on 23 Apr 2010 at 5:26

• Changed state: Fixed