Examples with Constraint org.h2.constraint.Constraint used on opensource projects

Example 31 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestMVTableEngine method testReferentialIntegrity.

private void testReferentialIntegrity() throws Exception {
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    conn = getConnection(getTestName() + ";MV_STORE=TRUE");
    stat = conn.createStatement();
    stat.execute("create table test(id int, parent int " + "references test(id) on delete cascade)");
    stat.execute("insert into test values(0, 0)");
    stat.execute("delete from test");
    stat.execute("drop table test");
    stat.execute("create table parent(id int, name varchar)");
    stat.execute("create table child(id int, parentid int, " + "foreign key(parentid) references parent(id))");
    stat.execute("insert into parent values(1, 'mary'), (2, 'john')");
    stat.execute("insert into child values(10, 1), (11, 1), (20, 2), (21, 2)");
    stat.execute("update parent set name = 'marc' where id = 1");
    stat.execute("merge into parent key(id) values(1, 'marcy')");
    stat.execute("drop table parent, child");
    stat.execute("create table test(id identity, parent bigint, " + "foreign key(parent) references(id))");
    stat.execute("insert into test values(0, 0), (1, NULL), " + "(2, 1), (3, 3), (4, 3)");
    stat.execute("drop table test");
    stat.execute("create table parent(id int)");
    stat.execute("create table child(pid int)");
    stat.execute("insert into parent values(1)");
    stat.execute("insert into child values(2)");
    try {
        stat.execute("alter table child add constraint cp " + "foreign key(pid) references parent(id)");
        fail();
    } catch (SQLException e) {
        assertEquals(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, e.getErrorCode());
    }
    stat.execute("update child set pid=1");
    stat.execute("drop table child, parent");
    stat.execute("create table parent(id int)");
    stat.execute("create table child(pid int)");
    stat.execute("insert into parent values(1)");
    stat.execute("insert into child values(2)");
    try {
        stat.execute("alter table child add constraint cp " + "foreign key(pid) references parent(id)");
        fail();
    } catch (SQLException e) {
        assertEquals(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, e.getErrorCode());
    }
    stat.execute("drop table child, parent");
    stat.execute("create table test(id identity, parent bigint, " + "foreign key(parent) references(id))");
    stat.execute("insert into test values(0, 0), (1, NULL), " + "(2, 1), (3, 3), (4, 3)");
    stat.execute("drop table test");
    stat.execute("create table parent(id int, x int)");
    stat.execute("insert into parent values(1, 2)");
    stat.execute("create table child(id int references parent(id)) as select 1");
    conn.close();
}

Example 32 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestIndex method testRenamePrimaryKey.

private void testRenamePrimaryKey() throws SQLException {
    if (config.memory) {
        return;
    }
    reconnect();
    stat.execute("create table test(id int not null)");
    stat.execute("alter table test add constraint x primary key(id)");
    ResultSet rs;
    rs = conn.getMetaData().getIndexInfo(null, null, "TEST", true, false);
    rs.next();
    String old = rs.getString("INDEX_NAME");
    stat.execute("alter index " + old + " rename to y");
    rs = conn.getMetaData().getIndexInfo(null, null, "TEST", true, false);
    rs.next();
    assertEquals("Y", rs.getString("INDEX_NAME"));
    reconnect();
    rs = conn.getMetaData().getIndexInfo(null, null, "TEST", true, false);
    rs.next();
    assertEquals("Y", rs.getString("INDEX_NAME"));
    stat.execute("drop table test");
}

Example 33 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestTriggersConstraints method testConstraints.

private void testConstraints() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("create table test(id int primary key, parent int)");
    stat.execute("alter table test add constraint test_parent_id " + "foreign key(parent) references test (id) on delete cascade");
    stat.execute("insert into test select x, x/2 from system_range(0, 100)");
    stat.execute("delete from test");
    assertSingleValue(stat, "select count(*) from test", 0);
    stat.execute("drop table test");
    conn.close();
}

Example 34 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestTriggersConstraints method testMultiPartForeignKeys.

/**
 * Regression test: we had a bug where the AlterTableAddConstraint class
 * used to sometimes pick the wrong unique index for a foreign key.
 */
private void testMultiPartForeignKeys() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST1");
    stat.execute("DROP TABLE IF EXISTS TEST2");
    stat.execute("create table test1(id int primary key, col1 int)");
    stat.execute("alter table test1 add constraint unique_test1 " + "unique (id,col1)");
    stat.execute("create table test2(id int primary key, col1 int)");
    stat.execute("alter table test2 add constraint fk_test2 " + "foreign key(id,col1) references test1 (id,col1)");
    stat.execute("insert into test1 values (1,1)");
    stat.execute("insert into test1 values (2,2)");
    stat.execute("insert into test1 values (3,3)");
    stat.execute("insert into test2 values (1,1)");
    assertThrows(23506, stat).execute("insert into test2 values (2,1)");
    assertSingleValue(stat, "select count(*) from test1", 3);
    assertSingleValue(stat, "select count(*) from test2", 1);
    stat.execute("drop table test1");
    stat.execute("drop table test2");
    conn.close();
}