[jira] [Created] (TRAFODION-1281) LP Bug: 1466209 - Referential constraint column correspondence not recorded correctly in metadata

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-1281:
-------------------------------------

             Summary: LP Bug: 1466209 - Referential constraint column correspondence not recorded correctly in metadata
                 Key: TRAFODION-1281
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1281
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Hans Zeller
            Assignee: Hans Zeller
            Priority: Critical
             Fix For: 2.0-incubating


When we create an RI constraint, we specify two lists of columns, the foreign key and the key columns of the referenced table:

foreign key (a,b) references tgttable(x,y)

When this information is recorded in the metadata, it does not always maintain the correct correspondence between foreign key and actual key, as this example shows:

create table uni(x int not null,
                 y int not null,
                 z int,
                 primary key(x,y));
create table forkey(a int not null,
                 b int not null,
                 c int not null primary key);

alter table forkey add constraint ri1
foreign key (a,b) references uni(x,y);

showddl forkey;

alter table forkey drop constraint ri1;
alter table forkey add constraint ri1
foreign key (a,b) references uni(y,x);

showddl forkey;

insert into uni values (11,22,33);

insert into forkey values (22,11,33);
-- this is the correct data, but the insert fails

insert into forkey values (11,22,33);
-- incorrect data, but the insert succeeds



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)