[jira] [Created] (TRAFODION-521) LP Bug: 1354150 - Drop table fails to drop foreign key constraint

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

Alice Chen created TRAFODION-521:
------------------------------------

             Summary: LP Bug: 1354150 - Drop table fails to drop foreign key constraint
                 Key: TRAFODION-521
                 URL: https://issues.apache.org/jira/browse/TRAFODION-521
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Apache Trafodion
            Priority: Critical


Drop table fails to drop foreign key constraint properly.  When a table with a foreign key is dropped and recreated, the second creation returns error 1254 complaining about duplicate unique constraints.  As shown in the 2nd part of the output, the table creation woks fine if the foreign key reference is commented out.

This is seen on the 0806_0830 daily build installed on a workstation.

-------------------------------------
Here is the entire script to reproduce this problem:

create table m4
(
pk4a int unique not null not droppable,
pk4b int not null not droppable,
pk4c int not null not droppable,
v4 varchar(20),
primary key(pk4a,pk4b,pk4c)
);

create table m3
(
pk3a int unique not null not droppable,
pk3b int not null not droppable,
pk3c int not null not droppable,
fk34a int,
fk34b int,
fk34c int,
v3 varchar(20),
primary key(pk3a,pk3b,pk3c),
foreign key(fk34a,fk34b,fk34c) references m4);

drop table m4 cascade;
drop table m3 cascade;

create table m4
(
pk4a int unique not null not droppable,
pk4b int not null not droppable,
pk4c int not null not droppable,
v4 varchar(20),
primary key(pk4a,pk4b,pk4c)
);

create table m3
(
pk3a int unique not null not droppable,
pk3b int not null not droppable,
pk3c int not null not droppable,
fk34a int,
fk34b int,
fk34c int,
v3 varchar(20),
primary key(pk3a,pk3b,pk3c),
foreign key(fk34a,fk34b,fk34c) references m4);

-------------------------------------

Here is the execution output showing the error:

>>create table m4
+>(
+>pk4a int unique not null not droppable,
+>pk4b int not null not droppable,
+>pk4c int not null not droppable,
+>v4 varchar(20),
+>primary key(pk4a,pk4b,pk4c)
+>);

--- SQL operation complete.
>>
>>create table m3
+>(
+>pk3a int unique not null not droppable,
+>pk3b int not null not droppable,
+>pk3c int not null not droppable,
+>fk34a int,
+>fk34b int,
+>fk34c int,
+>v3 varchar(20),
+>primary key(pk3a,pk3b,pk3c),
+>foreign key(fk34a,fk34b,fk34c) references m4);

--- SQL operation complete.
>>
>>drop table m4 cascade;

--- SQL operation complete.
>>drop table m3 cascade;

--- SQL operation complete.
>>
>>create table m4
+>(
+>pk4a int unique not null not droppable,
+>pk4b int not null not droppable,
+>pk4c int not null not droppable,
+>v4 varchar(20),
+>primary key(pk4a,pk4b,pk4c)
+>);

*** ERROR[1254] Duplicate unique constraints are not allowed with same set of columns.

*** ERROR[1029] Object TRAFODION.SEABASE.M4 could not be created.

--- SQL operation failed with errors.
>>
>>create table m3
+>(
+>pk3a int unique not null not droppable,
+>pk3b int not null not droppable,
+>pk3c int not null not droppable,
+>fk34a int,
+>fk34b int,
+>fk34c int,
+>v3 varchar(20),
+>primary key(pk3a,pk3b,pk3c),
+>foreign key(fk34a,fk34b,fk34c) references m4);

*** ERROR[4082] Object TRAFODION.SEABASE.M4 does not exist or is inaccessible.

*** ERROR[4082] Object TRAFODION.SEABASE.M4 does not exist or is inaccessible.

*** ERROR[1029] Object TRAFODION.SEABASE.M3 could not be created.

--- SQL operation failed with errors.

-------------------------------------

Here is the execution output showing that the 2nd creation works fine if the foreign key reference is commented out:

>>create table m4
+>(
+>pk4a int unique not null not droppable,
+>pk4b int not null not droppable,
+>pk4c int not null not droppable,
+>v4 varchar(20),
+>primary key(pk4a,pk4b,pk4c)
+>);

--- SQL operation complete.
>>
>>create table m3
+>(
+>pk3a int unique not null not droppable,
+>pk3b int not null not droppable,
+>pk3c int not null not droppable,
+>fk34a int,
+>fk34b int,
+>fk34c int,
+>v3 varchar(20),
+>primary key(pk3a,pk3b,pk3c)
+>-- ,foreign key(fk34a,fk34b,fk34c) references m4
+>);

--- SQL operation complete.
>>
>>drop table m4 cascade;

--- SQL operation complete.
>>drop table m3 cascade;

--- SQL operation complete.
>>
>>create table m4
+>(
+>pk4a int unique not null not droppable,
+>pk4b int not null not droppable,
+>pk4c int not null not droppable,
+>v4 varchar(20),
+>primary key(pk4a,pk4b,pk4c)
+>);

--- SQL operation complete.
>>
>>create table m3
+>(
+>pk3a int unique not null not droppable,
+>pk3b int not null not droppable,
+>pk3c int not null not droppable,
+>fk34a int,
+>fk34b int,
+>fk34c int,
+>v3 varchar(20),
+>primary key(pk3a,pk3b,pk3c)
+>-- ,foreign key(fk34a,fk34b,fk34c) references m4
+>);

--- SQL operation complete.
Assigned to LaunchPad User Mike Hanlon



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