Referential constraint column correspondence not recorded correctly in metadata
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
In Progress
|
High
|
Hans Zeller |
Bug Description
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,
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
Changed in trafodion: | |
status: | New → In Progress |