[ https://issues.apache.org/jira/browse/TRAFODION-1426?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on TRAFODION-1426 started by Anoop Sharma. ----------------------------------------------- > null values in columns of referencing table incorrectly cause referential > constraint to fail > --------------------------------------------------------------------------------------------- > > Key: TRAFODION-1426 > URL: https://issues.apache.org/jira/browse/TRAFODION-1426 > Project: Apache Trafodion > Issue Type: Bug > Reporter: Anoop Sharma > Assignee: Anoop Sharma > > When creating an referential constraint on a table with existing rows > containing null values, those null values should not cause RI constraint > creation to fail. > For correct behavior, those null values need to be ignored and constraint > creation should succeed. > This issue doesnt show up if the constraint is created on an empty > table and null values are later added to the referencing table. > This example shows the behavior with and without data: > ***** constraint fails if null values already exist in the referencing table > ****** > >>create table t1 (a int, b int); > --- SQL operation complete. > >> > >>create table t2 (a int not null, b int not null, primary key(a,b)); > --- SQL operation complete. > >> > >>insert into t1 values (1,null), (null, null); > --- 2 row(s) inserted. > >> > >>alter table t1 add constraint t1c1 foreign key (a,b) references t2(a,b); > *** ERROR[1143] Validation of constraint TRAFODION.SEABASE.T1C1 failed; > incompatible data exists in referencing base table T1 and referenced base > table T2. To display the data that violates the constraint, please use the > following DML statement: select count(*) from "TRAFODION"."SEABASE"."T1" > where not (("A", "B") in (select "A", "B" from "TRAFODION"."SEABASE"."T2")) > or "A" is null or "B" is null ; > --- SQL operation failed with errors. > >> > >>delete from t1; > --- 2 row(s) deleted. > >> > >>insert into t1 values (1,null), (null, null); > --- 2 row(s) inserted. > >> > >>log; > >>select * from t1; > A B > ----------- ----------- > 1 ? > ? ? > --- 2 row(s) selected. > >> > **** constraint correctly created and evaluated on an empty table **** > Trafodion Conversational Interface 1.2.0 > (c) Copyright 2014 Hewlett-Packard Development Company, LP. > >>create table t1 (a int, b int); > --- SQL operation complete. > >>create table t2 (a int not null, b int not null, primary key(a,b)); > --- SQL operation complete. > >>alter table t1 add constraint t1c1 foreign key (a,b) references t2(a,b); > --- SQL operation complete. > >> > >>insert into t1 values (1,null), (null, null); > --- 2 row(s) inserted. > >> > ******************************************************************* > Fix it to change the expression from "or a is null or b is null" > to "and a is not null and b is not null" in the expression that is > created to validate foreign key constraint during creation. -- This message was sent by Atlassian JIRA (v6.3.4#6332)