| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ForeignKeysNonSpsTest |
| |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to you under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| |
| */ |
| |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.CallableStatement; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Test of foreign key constraints. Converted from the old harness test |
| * lang/fk_nonSPS.sql |
| */ |
| public final class ForeignKeysNonSpsTest extends BaseJDBCTestCase { |
| |
| private static final int WAIT_TIMEOUT_DURATION = 4; |
| |
| /** |
| * Public constructor required for running test as standalone JUnit. |
| * @param name test name |
| */ |
| public ForeignKeysNonSpsTest (String name) { |
| super(name); |
| } |
| |
| /** |
| * JUnit handle |
| * @return this JUnit test |
| */ |
| public static Test suite() { |
| return TestConfiguration.defaultSuite(ForeignKeysNonSpsTest.class); |
| } |
| |
| public void testForeignKeys() throws Exception { |
| ResultSet rs; |
| PreparedStatement pSt; |
| String [][] expRS; |
| final Statement st = createStatement(); |
| final int initialCardSysDepends = numberOfRowsInSysdepends(st); |
| |
| st.executeUpdate( |
| "CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC " |
| + "RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME " |
| + "'org.apache.derbyTesting.functionTests.util.T_Access" |
| + ".waitForPostCommitToFinish' PARAMETER STYLE JAVA"); |
| |
| st.executeUpdate( |
| " create table p (c1 char(1), y int not null, c2 " |
| + "char(1), x int not null, constraint pk primary key (x,y))"); |
| |
| st.executeUpdate( |
| " create table f (x int not null, s smallint, y int " |
| + "not null, constraint fk foreign key (x,y) references p)"); |
| |
| st.executeUpdate("insert into p values ('1',1,'1',1)"); |
| |
| // should pass, foreign key constraint satisfied |
| st.executeUpdate( |
| "insert into f " |
| + "values " |
| + " (1,1,1)," |
| + " (1,1,1)," |
| + " (1,1,1), " |
| + " (1,1,1)," |
| + " (1, 0, 1)," |
| + " (1,1,1)," |
| + " (1,0,1)," |
| + " (1, 0, 1)"); |
| |
| // should FAIL, foreign key constraint violated |
| assertUpdateCount(st, 8, "delete from f"); |
| |
| assertStatementError("23503", st, |
| " insert into f " |
| + "values " |
| + " (1,1,1)," |
| + " (1,1,1)," |
| + " (1,1,1), " |
| + " (1,1,1)," |
| + " (1, 1, 1)," |
| + " (2,1,666)," |
| + " (1,1,0)," |
| + " (0, 1, 0)"); |
| |
| st.executeUpdate("drop table f"); |
| |
| waitForPostCommit(); |
| |
| // make sure boundary conditions are ok, null insert set |
| st.executeUpdate( |
| "create table f (c1 char(1), y int, c2 char(1), x " |
| + "int, constraint fk foreign key (x,y) references p)"); |
| |
| st.executeUpdate("insert into f select * from p where 1 = 2"); |
| |
| st.executeUpdate("drop table f"); |
| st.executeUpdate("drop table p"); |
| |
| waitForPostCommit(); |
| |
| // self referencing |
| st.executeUpdate( |
| "create table s (x int not null primary key, y int " |
| + "references s, z int references s)"); |
| |
| // ok |
| st.executeUpdate( |
| "insert into s " |
| + "values " |
| + " (1,1,1)," |
| + " (2,1,1)," |
| + " (10,2,1), " |
| + " (11,1,2)," |
| + " (12,4,4)," |
| + " (4,1,1)," |
| + " (13,null,null)," |
| + " (14,1,2)," |
| + " (15,null, 1)"); |
| |
| assertUpdateCount(st, 9, "delete from s"); |
| |
| // bad |
| assertStatementError("23503", st, |
| "insert into s " |
| + "values " |
| + " (1,1,1)," |
| + " (2,1,1)," |
| + " (10,2,1), " |
| + " (11,1,2)," |
| + " (12,4,4)," |
| + " (4,1,1)," |
| + " (13,null,null)," |
| + " (14,1,2)," |
| + " (15,666, 1)"); |
| |
| // now a test for depenencies. the insert will create new |
| // index conglomerate numbers, so we want to test that a |
| // statement with a constraint check that is dependent on |
| // the conglomerate number that is being changed is invalidated |
| st.executeUpdate( |
| "create table x (x int not null, y int, constraint " |
| + "pk primary key (x))"); |
| |
| st.executeUpdate( |
| " create table y (x int , y int, constraint fk " |
| + "foreign key (x) references x)"); |
| |
| final PreparedStatement pStIx = prepareStatement( |
| "insert into x values" + |
| "(0,0)," + |
| "(1,1)," + |
| "(2,2)"); |
| |
| final PreparedStatement pStIx2 = prepareStatement( |
| "insert into x values" + |
| "(3,3),"+ |
| "(4,4)"); |
| |
| final PreparedStatement pStIy = prepareStatement( |
| "insert into y values" + |
| "(0,0)," + |
| "(1,1)," + |
| "(2,2)"); |
| |
| final PreparedStatement dy = prepareStatement( |
| "delete from y where x = 1"); |
| |
| final PreparedStatement dx = prepareStatement( |
| "delete from x where x = 1"); |
| |
| assertUpdateCount(pStIx, 3); |
| |
| setAutoCommit(false); |
| |
| commit(); |
| |
| // ok |
| assertUpdateCount(dy, 0); |
| assertUpdateCount(dx, 1); |
| |
| // will fail, no key 1 in x |
| assertStatementError("23503", pStIy); |
| |
| rollback(); |
| |
| assertUpdateCount(pStIy, 3); |
| assertUpdateCount(dy, 1); |
| assertUpdateCount(dx, 1); |
| |
| pStIx.close(); |
| pStIx2.close(); |
| pStIy.close(); |
| dy.close(); |
| dx.close(); |
| |
| st.executeUpdate("drop table y"); |
| st.executeUpdate("drop table x"); |
| st.executeUpdate("drop table s"); |
| |
| setAutoCommit(true); |
| |
| waitForPostCommit(); |
| |
| // ** insert fkddl.sql simple syntax checks column constraint |
| st.executeUpdate( |
| "create table p1 (x int not null, constraint pk1 " |
| + "primary key(x))"); |
| |
| st.executeUpdate( |
| " create table u1 (x int not null unique)"); |
| |
| // table constraint |
| st.executeUpdate( |
| "create table p2 (x int not null, y dec(5,2) not " |
| + "null, constraint pk2 primary key (x,y))"); |
| |
| st.executeUpdate( |
| " create table u2 (x int not null, y dec(5,2) not " |
| + "null, constraint uk2 unique (x,y))"); |
| |
| st.executeUpdate( |
| " create table p3 (x char(10) not null, constraint " |
| + "pk3 primary key (x))"); |
| |
| // for future use |
| st.executeUpdate("create schema otherschema"); |
| |
| st.executeUpdate( |
| " create table otherschema.p1 (x int not null primary key)"); |
| |
| // Negative test cases for foreign key TABLE constraints |
| // negative: fk table, no table |
| assertStatementError("X0Y46", st, |
| "create table f (x int, constraint fk foreign key " |
| + "(x) references notthere)"); |
| |
| // negative: fk table, bad column |
| assertStatementError("X0Y44", st, |
| "create table f (x int, constraint fk foreign key " |
| + "(x) references p1(notthere))"); |
| |
| // negative: fk table, no constraint |
| assertStatementError("X0Y44", st, |
| "create table f (x int, constraint fk foreign key " |
| + "(x) references p2(y))"); |
| |
| // negative: fk table, wrong type |
| assertStatementError("X0Y44", st, |
| "create table f (x smallint, constraint fk foreign " |
| + "key (x) references p1(x))"); |
| |
| // negative: cannot reference a system table |
| assertStatementError("42Y08", st, |
| "create table f (x char(36), constraint fk foreign " |
| + "key (x) references sys.sysforeignkeys(constraintid))"); |
| |
| // negative: bad schema |
| assertStatementError("42Y07", st, |
| "create table f (x char(36), constraint fk foreign " |
| + "key (x) references badschema.x)"); |
| |
| // negative: bad column list |
| assertStatementError("42X93", st, |
| "create table f (x dec(5,2), y int, constraint fk " |
| + "foreign key (x,z) references p2(x,y))"); |
| |
| // negative: wrong number of columns |
| assertStatementError("X0Y44", st, |
| "create table f (x dec(5,2), y int, constraint fk " |
| + "foreign key (x) references p2(x,y))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (x dec(5,2), y int, constraint fk " |
| + "foreign key (x,y) references p2(x))"); |
| |
| // Negative test cases for foreign key COLUMN constraints |
| // negative: fk column, no table |
| assertStatementError("X0Y46", st, |
| "create table f (x int references notthere)"); |
| |
| // negative: fk column, bad column |
| assertStatementError("X0Y44", st, |
| "create table f (x int references p1(notthere))"); |
| |
| // negative: fk column, no constraint |
| assertStatementError("X0Y44", st, |
| "create table f (x int references p2(y))"); |
| |
| // negative: fk column, wrong type |
| assertStatementError("X0Y44", st, |
| "create table f (x smallint references p1(x))"); |
| |
| // negative: cannot reference a system table |
| assertStatementError("42Y08", st, |
| "create table f (x char(36) references " |
| + "sys.sysforeignkeys(constraintid))"); |
| |
| // negative: bad schema |
| assertStatementError("42Y07", st, |
| "create table f (x char(36) references badschema.x)"); |
| |
| // Some type checks. Types must match exactly ok |
| st.executeUpdate( |
| "create table f (d dec(5,2), i int, constraint fk " |
| + "foreign key (i,d) references p2(x,y))"); |
| |
| st.executeUpdate( |
| " drop table f"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate( |
| " create table f (i int, d dec(5,2), constraint fk " |
| + "foreign key (i,d) references p2(x,y))"); |
| |
| st.executeUpdate("drop table f"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate( |
| " create table f (d dec(5,2), i int, constraint fk " |
| + "foreign key (i,d) references u2(x,y))"); |
| |
| st.executeUpdate("drop table f"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate( |
| " create table f (i int, d dec(5,2), constraint fk " |
| + "foreign key (i,d) references u2(x,y))"); |
| |
| st.executeUpdate("drop table f"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate( |
| " create table f (c char(10) references p3(x))"); |
| |
| st.executeUpdate("drop table f"); |
| |
| waitForPostCommit(); |
| |
| // type mismatch |
| assertStatementError("X0Y44", st, |
| "create table f (i int, d dec(5,1), constraint fk " |
| + "foreign key (i,d) references p2(x,y))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (i int, d dec(4,2), constraint fk " |
| + "foreign key (i,d) references p2(x,y))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (i int, d dec(4,2), constraint fk " |
| + "foreign key (i,d) references p2(x,y))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (i int, d numeric(5,2), constraint " |
| + "fk foreign key (i,d) references p2(x,y))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (c char(11) references p3(x))"); |
| |
| assertStatementError("X0Y44", st, |
| " create table f (c varchar(10) references p3(x))"); |
| |
| // wrong order |
| assertStatementError("X0Y44", st, |
| "create table f (d dec(5,2), i int, constraint fk " |
| + "foreign key (d,i) references p2(x,y))"); |
| |
| // check system tables |
| st.executeUpdate( |
| "create table f (x int, constraint fk foreign key " |
| + "(x) references p1)"); |
| |
| rs = st.executeQuery( |
| " select constraintname, referencecount " |
| + " from sys.sysconstraints c, sys.sysforeignkeys fk" |
| + " where fk.keyconstraintid = c.constraintid order by " |
| + "constraintname"); |
| |
| expRS = new String [][]{{"PK1", "1"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| st.executeUpdate( |
| " create table f2 (x int, constraint fk2 foreign key " |
| + "(x) references p1(x))"); |
| |
| st.executeUpdate( |
| " create table f3 (x int, constraint fk3 foreign key " |
| + "(x) references p1(x))"); |
| |
| st.executeUpdate( |
| " create table f4 (x int, constraint fk4 foreign key " |
| + "(x) references p1(x))"); |
| |
| rs = st.executeQuery( |
| " select distinct constraintname, referencecount " |
| + " from sys.sysconstraints c, sys.sysforeignkeys fk" |
| + " where fk.keyconstraintid = c.constraintid order by " |
| + "constraintname"); |
| |
| expRS = new String [][]{{"PK1", "4"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery( |
| " select constraintname " |
| + " from sys.sysconstraints c, sys.sysforeignkeys fk" |
| + " where fk.constraintid = c.constraintid" |
| + " order by 1"); |
| |
| expRS = new String [][] |
| { |
| {"FK"}, |
| {"FK2"}, |
| {"FK3"}, |
| {"FK4"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // we should not be able to drop the primary key |
| assertStatementError("X0Y25", st, |
| "alter table p1 drop constraint pk1"); |
| |
| assertStatementError("X0Y25", st, "drop table p1"); |
| |
| waitForPostCommit(); |
| |
| // now lets drop the foreign keys and try again |
| st.executeUpdate("drop table f2"); |
| |
| st.executeUpdate("drop table f3"); |
| |
| st.executeUpdate("drop table f4"); |
| |
| waitForPostCommit(); |
| |
| rs = st.executeQuery( |
| " select constraintname, referencecount " |
| + " from sys.sysconstraints c, sys.sysforeignkeys fk" |
| + " where fk.keyconstraintid = c.constraintid order by " |
| + "constraintname"); |
| |
| expRS = new String [][]{{"PK1", "1"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| st.executeUpdate(" alter table f drop constraint fk"); |
| |
| waitForPostCommit(); |
| |
| // ok |
| st.executeUpdate("alter table p1 drop constraint pk1"); |
| |
| waitForPostCommit(); |
| |
| // we shouldn't be able to add an fk on p1 now |
| assertStatementError("X0Y41", st, |
| "alter table f add constraint fk foreign key (x) " |
| + "references p1"); |
| |
| // add the constraint and try again |
| st.executeUpdate( |
| "alter table p1 add constraint pk1 primary key (x)"); |
| |
| st.executeUpdate( |
| " create table f2 (x int, constraint fk2 foreign key " |
| + "(x) references p1(x))"); |
| |
| st.executeUpdate( |
| " create table f3 (x int, constraint fk3 foreign key " |
| + "(x) references p1(x))"); |
| |
| st.executeUpdate( |
| " create table f4 (x int, constraint fk4 foreign key " |
| + "(x) references p1(x))"); |
| |
| // drop constraint |
| st.executeUpdate("alter table f4 drop constraint fk4"); |
| st.executeUpdate("alter table f3 drop constraint fk3"); |
| st.executeUpdate("alter table f2 drop constraint fk2"); |
| st.executeUpdate("alter table p1 drop constraint pk1"); |
| |
| waitForPostCommit(); |
| |
| // all fks are gone, right? |
| rs = st.executeQuery( |
| "select constraintname " |
| + " from sys.sysconstraints c, sys.sysforeignkeys fk" |
| + " where fk.constraintid = c.constraintid order by " |
| + "constraintname"); |
| |
| JDBC.assertDrainResults(rs, 0); |
| |
| // cleanup what we have done so far |
| st.executeUpdate("drop table p1"); |
| st.executeUpdate("drop table p2"); |
| st.executeUpdate("drop table u1"); |
| st.executeUpdate("drop table u2"); |
| st.executeUpdate("drop table otherschema.p1"); |
| st.executeUpdate("drop schema otherschema restrict"); |
| |
| waitForPostCommit(); |
| |
| // will return dependencies for SPS metadata queries now |
| // created by default database is created. |
| st.executeUpdate( |
| "create table default_sysdepends_count(a int)"); |
| |
| st.executeUpdate( |
| " insert into default_sysdepends_count select " |
| + "count(*) from sys.sysdepends"); |
| |
| rs = st.executeQuery( |
| " select * from default_sysdepends_count"); |
| |
| expRS = new String [][]{{Integer.toString(initialCardSysDepends)}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // now we are going to do some self referencing tests. |
| st.executeUpdate( |
| "create table selfref (p char(10) not null primary key, " |
| + " f char(10) references selfref)"); |
| |
| st.executeUpdate("drop table selfref"); |
| |
| waitForPostCommit(); |
| |
| // ok |
| st.executeUpdate( |
| "create table selfref (p char(10) not null, " |
| + " f char(10) references selfref, " |
| + " constraint pk primary key (p))"); |
| |
| st.executeUpdate("drop table selfref"); |
| |
| waitForPostCommit(); |
| |
| // ok |
| st.executeUpdate( |
| "create table selfref (p char(10) not null, f char(10), " |
| + " constraint f foreign key (f) references selfref(p), " |
| + " constraint pk primary key (p))"); |
| |
| // should fail |
| assertStatementError("X0Y25", st, |
| "alter table selfref drop constraint pk"); |
| |
| waitForPostCommit(); |
| |
| // ok |
| st.executeUpdate( |
| "alter table selfref drop constraint f"); |
| |
| st.executeUpdate( |
| " alter table selfref drop constraint pk"); |
| |
| st.executeUpdate("drop table selfref"); |
| |
| waitForPostCommit(); |
| |
| // what if a pk references another pk? should just drop |
| // the direct references (nothing special, really) |
| st.executeUpdate( |
| "create table pr1(x int not null, " |
| + " constraint pkr1 primary key (x))"); |
| |
| st.executeUpdate( |
| " create table pr2(x int not null, " |
| + " constraint pkr2 primary key(x), " |
| + " constraint fpkr2 foreign key (x) references pr1)"); |
| |
| st.executeUpdate( |
| " create table pr3(x int not null, " |
| + " constraint pkr3 primary key(x), " |
| + " constraint fpkr3 foreign key (x) references pr2)"); |
| |
| rs = st.executeQuery( |
| " select constraintname, referencecount from " |
| + "sys.sysconstraints order by constraintname"); |
| |
| expRS = new String [][] |
| { |
| {"FPKR2", "0"}, |
| {"FPKR3", "0"}, |
| {"PK3", "0"}, |
| {"PKR1", "1"}, |
| {"PKR2", "1"}, |
| {"PKR3", "0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // now drop constraint pkr1 |
| st.executeUpdate( |
| "alter table pr2 drop constraint fpkr2"); |
| |
| st.executeUpdate( |
| " alter table pr1 drop constraint pkr1"); |
| |
| waitForPostCommit(); |
| |
| // pkr1 and pfkr2 are gone |
| rs = st.executeQuery( |
| "select constraintname, referencecount from " |
| + "sys.sysconstraints order by constraintname"); |
| |
| expRS = new String [][] |
| { |
| {"FPKR3", "0"}, |
| {"PK3", "0"}, |
| {"PKR2", "1"}, |
| {"PKR3", "0"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // cleanup |
| st.executeUpdate( |
| "drop table pr3"); |
| |
| st.executeUpdate("drop table pr2"); |
| st.executeUpdate("drop table pr1"); |
| |
| waitForPostCommit(); |
| |
| // should return 0, confirm no unexpected dependencies |
| // verify that all rows in sys.sysdepends got dropped |
| // apart from sps dependencies |
| st.executeUpdate( |
| "create table default_sysdepends_count2(a int)"); |
| |
| st.executeUpdate( |
| " insert into default_sysdepends_count2 select " |
| + "count(*) from sys.sysdepends"); |
| |
| rs = st.executeQuery( |
| " select default_sysdepends_count2.a - " |
| + "default_sysdepends_count.a" |
| + " from default_sysdepends_count2, default_sysdepends_count"); |
| |
| expRS = new String [][]{{"0"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // dependencies and spses |
| st.executeUpdate( |
| "create table x (x int not null primary key, y int, " |
| + "constraint xfk foreign key (y) references x)"); |
| |
| st.executeUpdate( |
| " create table y (x int, constraint yfk foreign key " |
| + "(x) references x)"); |
| |
| final PreparedStatement ss = prepareStatement( |
| "select * from x"); |
| |
| final PreparedStatement si = prepareStatement( |
| "insert into x values (1,1)"); |
| |
| final PreparedStatement su = prepareStatement( |
| "update x set x = x+1, y=y+1"); |
| |
| st.executeUpdate( |
| " alter table x drop constraint xfk"); |
| |
| waitForPostCommit(); |
| |
| setAutoCommit(false); |
| |
| // drop the referenced fk, should force su to be |
| // recompiled since it no longer has to check the foreign |
| // key table |
| st.executeUpdate( |
| "alter table y drop constraint yfk"); |
| |
| commit(); |
| waitForPostCommit(); |
| |
| st.executeUpdate("drop table y"); |
| |
| commit(); |
| |
| waitForPostCommit(); |
| |
| // ok |
| st.executeUpdate("drop table x"); |
| |
| ss.close(); |
| si.close(); |
| su.close(); |
| |
| st.executeUpdate("drop table f3"); |
| st.executeUpdate("drop table f2"); |
| st.executeUpdate("drop table f"); |
| |
| commit(); |
| waitForPostCommit(); |
| |
| // verify that all rows in sys.sysdepends got dropped |
| // apart from sps dependencies Since, with beetle 5352; we |
| // create metadata SPS for network server at database |
| // bootup time so the dependencies for SPS are there. |
| st.executeUpdate( |
| "create table default_sysdepends_count3(a int)"); |
| |
| st.executeUpdate( |
| " insert into default_sysdepends_count3 select " |
| + "count(*) from sys.sysdepends"); |
| |
| rs = st.executeQuery( |
| " select default_sysdepends_count3.a - " |
| + "default_sysdepends_count.a" |
| + " from default_sysdepends_count3, default_sysdepends_count"); |
| |
| expRS = new String [][]{{"0"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // ** insert fkdml.sql |
| setAutoCommit(true); |
| |
| // DML and foreign keys |
| assertStatementError("42Y55", st, "drop table s"); |
| assertStatementError("42Y55", st, "drop table f3"); |
| assertStatementError("42Y55", st, "drop table f2"); |
| assertStatementError("42Y55", st, "drop table f"); |
| assertStatementError("42Y55", st, "drop table p"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate( |
| " create table p (x int not null, y int not null, " |
| + "constraint pk primary key (x,y))"); |
| |
| st.executeUpdate( |
| " create table f (x int, y int, constraint fk " |
| + "foreign key (x,y) references p)"); |
| |
| st.executeUpdate( |
| " insert into p values (1,1)"); |
| |
| // ok |
| st.executeUpdate("insert into f values (1,1)"); |
| |
| // fail |
| assertStatementError("23503", st, "insert into f values (2,1)"); |
| assertStatementError("23503", st, " insert into f values (1,2)"); |
| |
| // nulls are ok |
| st.executeUpdate("insert into f values (1,null)"); |
| st.executeUpdate("insert into f values (null,null)"); |
| st.executeUpdate("insert into f values (1,null)"); |
| |
| // update on pk, fail |
| assertStatementError("23503", st, "update p set x = 2"); |
| assertStatementError("23503", st, "update p set y = 2"); |
| assertStatementError("23503", st, "update p set x = 1, y = 2"); |
| assertStatementError("23503", st, "update p set x = 2, y = 1"); |
| assertStatementError("23503", st, "update p set x = 2, y = 2"); |
| |
| // ok |
| assertUpdateCount(st, 1, "update p set x = 1, y = 1"); |
| |
| // delete pk, fail |
| assertStatementError("23503", st, "delete from p"); |
| |
| // delete fk, ok |
| assertUpdateCount(st, 4, "delete from f"); |
| |
| st.executeUpdate("insert into f values (1,1)"); |
| |
| // update fk, fail |
| assertStatementError("23503", st, "update f set x = 2"); |
| assertStatementError("23503", st, "update f set y = 2"); |
| assertStatementError("23503", st, "update f set x = 1, y = 2"); |
| assertStatementError("23503", st, "update f set x = 2, y = 1"); |
| |
| // update fk, ok |
| assertUpdateCount(st, 1, "update f set x = 1, y = 1"); |
| |
| // nulls ok |
| assertUpdateCount(st, 1, "update f set x = null, y = 1"); |
| assertUpdateCount(st, 1, "update f set x = 1, y = null"); |
| assertUpdateCount(st, 1, "update f set x = null, y = null"); |
| assertUpdateCount(st, 1, "delete from f"); |
| |
| st.executeUpdate("insert into f values (1,1)"); |
| st.executeUpdate("insert into p values (2,2)"); |
| |
| // ok |
| assertUpdateCount(st, 1, "update f set x = x+1, y = y+1"); |
| |
| rs = st.executeQuery("select * from f"); |
| |
| expRS = new String [][]{{"2", "2"}}; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| rs = st.executeQuery("select * from p"); |
| |
| expRS = new String [][] |
| { |
| {"1", "1"}, |
| {"2", "2"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // ok |
| assertUpdateCount(st, 2, "update p set x = x+1, y = y+1"); |
| |
| // fail |
| assertStatementError("23503", st, "update p set x = x+1, y = y+1"); |
| |
| // BOUNDARY CONDITIONS |
| assertUpdateCount(st, 1, "delete from f"); |
| assertUpdateCount(st, 2, "delete from p"); |
| |
| st.executeUpdate("insert into f select * from f"); |
| |
| assertUpdateCount(st, 0, "delete from p where x = 9999"); |
| assertUpdateCount(st, 0, "update p set x = x+1, y=y+1 where x = 999"); |
| |
| st.executeUpdate("insert into p values (1,1)"); |
| st.executeUpdate("insert into f values (1,1)"); |
| |
| assertUpdateCount(st, 0, "update p set x = x+1, y=y+1 where x = 999"); |
| assertUpdateCount(st, 0, "delete from p where x = 9999"); |
| |
| st.executeUpdate("insert into f select * from f"); |
| |
| // test a CURSOR |
| assertUpdateCount(st, 2, "delete from f"); |
| assertUpdateCount(st, 1, "delete from p"); |
| |
| st.executeUpdate("insert into p values (1,1)"); |
| st.executeUpdate("insert into f values (1,1)"); |
| |
| setAutoCommit(false); |
| |
| final Statement uS = createStatement( |
| ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); |
| ResultSet r = uS.executeQuery("select * from p for update of x"); |
| r.next(); |
| assertEquals(r.getString(1), "1"); |
| assertEquals(r.getString(2), "1"); |
| |
| try { |
| // UPDATE on table 'P' caused a violation of foreign |
| // key constraint 'FK' for key (1,1). |
| r.updateInt("X", 666); |
| } catch (SQLException e) { |
| assertSQLState("23503", e); |
| } |
| |
| r.close(); |
| |
| r = uS.executeQuery("select * from f for update of x"); |
| r.next(); |
| assertEquals(r.getString(1), "1"); |
| assertEquals(r.getString(2), "1"); |
| |
| try { |
| // UPDATE on table 'F' caused a violation of foreign |
| // key constraint 'FK' for key (666,1). |
| r.updateInt("X", 666); |
| } catch (SQLException e) { |
| assertSQLState("23503", e); |
| } |
| |
| r.close(); |
| |
| commit(); |
| setAutoCommit(true); |
| |
| assertUpdateCount(st, 1, "delete from f"); |
| assertUpdateCount(st, 1, "delete from p"); |
| |
| st.executeUpdate("insert into p values (0,0), (1,1), (2,2), (3,3), (4,4)"); |
| st.executeUpdate("insert into f values (1,1)"); |
| |
| // lets add some additional foreign keys to the mix |
| st.executeUpdate( |
| "create table f2 (x int, y int, constraint fk2 " |
| + "foreign key (x,y) references p)"); |
| |
| st.executeUpdate("insert into f2 values (2,2)"); |
| |
| st.executeUpdate( |
| " create table f3 (x int, y int, constraint fk3 " |
| + "foreign key (x,y) references p)"); |
| |
| st.executeUpdate("insert into f3 values (3,3)"); |
| |
| // ok |
| assertUpdateCount(st, 5, "update p set x = x+1, y = y+1"); |
| |
| // error, fk1 |
| assertStatementError("23503", st, "update p set x = x+1"); |
| assertStatementError("23503", st, "update p set y = y+1"); |
| assertStatementError("23503", st, "update p set x = x+1, y = y+1"); |
| |
| // fail of fk3 |
| assertStatementError("23503", st, "update p set y = 666 where y = 3"); |
| |
| // fail of fk2 |
| assertStatementError("23503", st, "update p set x = 666 where x = 2"); |
| |
| // cleanup |
| st.executeUpdate("drop table f"); |
| st.executeUpdate("drop table f2"); |
| st.executeUpdate("drop table f3"); |
| st.executeUpdate("drop table p"); |
| |
| waitForPostCommit(); |
| |
| // SELF REFERENCING |
| st.executeUpdate( |
| "create table s (x int not null primary key, y int " |
| + "references s, z int references s)"); |
| |
| // ok |
| st.executeUpdate("insert into s values (1,null,null)"); |
| |
| // ok |
| assertUpdateCount(st, 1, "update s set y = 1"); |
| |
| // fail |
| assertStatementError("23503", st, "update s set z = 2"); |
| |
| // ok |
| assertUpdateCount(st, 1, "update s set z = 1"); |
| |
| // ok |
| st.executeUpdate("insert into s values (2, 1, 1)"); |
| |
| // ok |
| assertUpdateCount(st, 1, "update s set x = 666 where x = 2"); |
| |
| // ok |
| assertUpdateCount(st, 2, "update s set x = x+1, y = y+1, z = z+1"); |
| assertUpdateCount(st, 2, "delete from s"); |
| |
| // ok |
| st.executeUpdate("insert into s values (1,null,null)"); |
| st.executeUpdate("insert into s values (2,null,null)"); |
| assertUpdateCount(st, 1, "update s set y = 2 where x = 1"); |
| assertUpdateCount(st, 1, "update s set z = 1 where x = 2"); |
| |
| rs = st.executeQuery("select * from s"); |
| |
| expRS = new String [][] |
| { |
| {"1", "2", null}, |
| {"2", null, "1"} |
| }; |
| |
| JDBC.assertFullResultSet(rs, expRS, true); |
| |
| // fail |
| assertStatementError("23503", st, "update s set x = 0 where x = 1"); |
| |
| // Now we are going to do a short but sweet check to make |
| // sure we are actually hitting the correct columns |
| st.executeUpdate( |
| "create table p (c1 char(1), y int not null, c2 " |
| + "char(1), x int not null, constraint pk primary key (x,y))"); |
| |
| st.executeUpdate( |
| " create table f (x int, s smallint, y int, " |
| + "constraint fk foreign key (x,y) references p)"); |
| |
| st.executeUpdate( |
| " insert into p values ('1',1,'1',1)"); |
| |
| // ok |
| st.executeUpdate("insert into f values (1,1,1)"); |
| st.executeUpdate("insert into p values ('0',0,'0',0)"); |
| |
| // ok |
| assertUpdateCount(st, 2, "update p set x = x+1, y=y+1"); |
| |
| // fail |
| assertStatementError("23503", st, "delete from p where y = 1"); |
| assertStatementError("23503", st, "insert into f values (1,1,4)"); |
| |
| assertUpdateCount(st, 1, "delete from f"); |
| assertUpdateCount(st, 2, "delete from p"); |
| |
| // Lets make sure we don't interact poorly with 'normal' |
| // deferred dml |
| st.executeUpdate("insert into p values ('1',1,'1',1)"); |
| st.executeUpdate("insert into f values (1,1,1)"); |
| st.executeUpdate("insert into p values ('0',0,'0',0)"); |
| |
| // ok |
| assertUpdateCount(st, 2, |
| "update p set x = x+1, y=y+1 where x < (select " |
| + "max(x)+10000 from p)"); |
| |
| // fail |
| assertStatementError("23503", st, |
| "delete from p where y = 1 and y in (select y from p)"); |
| |
| // inserts |
| st.executeUpdate( |
| "create table f2 (x int, t smallint, y int)"); |
| |
| st.executeUpdate("insert into f2 values (1,1,4)"); |
| |
| // fail |
| assertStatementError("23503", st,"insert into f select * from f2"); |
| |
| // ok |
| st.executeUpdate("insert into f2 values (1,1,1)"); |
| st.executeUpdate("insert into f select * from f2 where y = 1"); |
| |
| st.executeUpdate("drop table f2"); |
| st.executeUpdate("drop table f"); |
| st.executeUpdate("drop table p"); |
| |
| waitForPostCommit(); |
| |
| // PREPARED STATEMENTS |
| assertStatementError("42Y55", st, "drop table f"); |
| assertStatementError("42Y55", st, "drop table p"); |
| |
| //the reason for this wait call is to wait unitil system |
| // tables row deletesare completed other wise we will get |
| // different order fk checksthat will lead different error |
| // messages depending on when post commit thread runs |
| waitForPostCommit(); |
| |
| pSt = prepareStatement( |
| "create table p (w int not null primary key, x int " |
| + "references p, y int not null, z int not null, " |
| + "constraint uyz unique (y,z))"); |
| |
| assertUpdateCount(pSt, 0); |
| |
| |
| pSt = prepareStatement( |
| "create table f (w int references p, x int, y int, z " |
| + "int, constraint fk foreign key (y,z) references p (y,z))"); |
| |
| assertUpdateCount(pSt, 0); |
| |
| |
| pSt = prepareStatement( |
| "alter table f drop constraint fk"); |
| |
| assertUpdateCount(pSt, 0); |
| |
| |
| //the reason for this wait call is to wait unitil system |
| // tables row deletesare completed other wise we will get |
| // different order fk checks |
| waitForPostCommit(); |
| |
| pSt = prepareStatement( |
| "alter table f add constraint fk foreign key (y,z) " |
| + "references p (y,z)"); |
| |
| assertUpdateCount(pSt, 0); |
| |
| |
| PreparedStatement sf = prepareStatement( |
| "insert into f values (1,1,1,1)"); |
| |
| PreparedStatement sp = prepareStatement( |
| "insert into p values (1,1,1,1)"); |
| |
| // fail |
| assertStatementError("23503", sf); |
| |
| // ok |
| assertUpdateCount(sp, 1); |
| assertUpdateCount(sf, 1); |
| |
| st.executeUpdate(" insert into p values (2,2,2,2)"); |
| |
| |
| pSt = prepareStatement( |
| "update f set w=w+1, x = x+1, y=y+1, z=z+1"); |
| |
| // ok |
| assertUpdateCount(pSt, 1); |
| |
| pSt = prepareStatement("update p set w=w+1, x = x+1, y=y+1, z=z+1"); |
| |
| // ok |
| assertUpdateCount(pSt, 2); |
| |
| pSt = prepareStatement("delete from p where x =1"); |
| |
| // ok |
| assertUpdateCount(pSt, 0); |
| |
| st.executeUpdate("drop table f"); |
| st.executeUpdate("drop table p"); |
| |
| waitForPostCommit(); |
| |
| st.executeUpdate("drop procedure WAIT_FOR_POST_COMMIT"); |
| |
| rollback(); |
| st.close(); |
| } |
| |
| /** |
| * Get a count of number of rows in SYS.SYSDEPENDS |
| */ |
| private int numberOfRowsInSysdepends(Statement st) throws SQLException { |
| final ResultSet rs = |
| st.executeQuery("SELECT COUNT(*) FROM SYS.SYSDEPENDS"); |
| rs.next(); |
| final int result = rs.getInt(1); |
| rs.close(); |
| return result; |
| } |
| |
| private void waitForPostCommit() throws SQLException { |
| final CallableStatement s = |
| prepareCall("CALL WAIT_FOR_POST_COMMIT()"); |
| assertUpdateCount(s, 0); |
| s.close(); |
| } |
| } |
