[jira] [Created] (TRAFODION-933) LP Bug: 1413676 - Alter table add foreign key constraint ignores ‘not enforced’

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

Alice Chen created TRAFODION-933:
------------------------------------

             Summary: LP Bug: 1413676 - Alter table add foreign key constraint ignores ‘not enforced’
                 Key: TRAFODION-933
                 URL: https://issues.apache.org/jira/browse/TRAFODION-933
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical
             Fix For: 1.0 (pre-incubation)


As shown in the first part of the execution output, if a ‘not enforced’ foreign key is created as part of the create table statement, the DDL returns warning 1313.  The foreign key is not enforced, as specified, during the next insert statement.

But if the ‘not enforced’ foreign key is added by a ‘alter table add constraint’ statement.  The ‘not enforced’ attribute is ignored and the alter table statement returns error 1143, instead of warning 1313.

The 2 behaviors are inconsistent.  The ‘alter table add constraint’ should also honor the ‘not enforced’ attribute for a foreign key and return warning 1313, instead of error 1143.

This is seen on the v0121 build installed on a workstation.

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

Here is the entire script to reproduce this problem:

create schema mytest;
set schema mytest;

create table mytab1 (
a int not null not droppable primary key,
b int
);

insert into mytab1 values (1,1),(2,2);
select * from mytab1;

create table mytab2 (
c int not null not droppable primary key,
d int references mytab1(a) not enforced
);

insert into mytab2 values (1,1),(2,2),(3,3);
select * from mytab2;

drop schema mytest cascade;

create schema mytest;

create table mytab1 (
a int not null not droppable primary key,
b int
);

insert into mytab1 values (1,1),(2,2);
select * from mytab1;

create table mytab2 (
c int not null not droppable primary key,
d int
);

insert into mytab2 values (1,1),(2,2),(3,3);
select * from mytab2;

alter table mytab2 add constraint fk_not_enforced foreign key(d) references mytab1(a) not enforced;

drop schema mytest cascade;

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

Here is the execution output:

>>obey mytest.sql;
>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table mytab1 (
+>a int not null not droppable primary key,
+>b int
+>);

--- SQL operation complete.
>>
>>insert into mytab1 values (1,1),(2,2);

--- 2 row(s) inserted.
>>select * from mytab1;

A            B
-----------  -----------

          1            1
          2            2

--- 2 row(s) selected.
>>
>>create table mytab2 (
+>c int not null not droppable primary key,
+>d int references mytab1(a) not enforced
+>);

*** WARNING[1313] The referential integrity constraint TRAFODION.MYTEST.MYTAB2_932363272_9479 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>
>>insert into mytab2 values (1,1),(2,2),(3,3);

--- 3 row(s) inserted.
>>select * from mytab2;

C            D
-----------  -----------

          1            1
          2            2
          3            3

--- 3 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.
>>
>>create schema mytest;

--- SQL operation complete.
>>
>>create table mytab1 (
+>a int not null not droppable primary key,
+>b int
+>);

--- SQL operation complete.
>>
>>insert into mytab1 values (1,1),(2,2);

--- 2 row(s) inserted.
>>select * from mytab1;

A            B
-----------  -----------

          1            1
          2            2

--- 2 row(s) selected.
>>
>>create table mytab2 (
+>c int not null not droppable primary key,
+>d int
+>);

--- SQL operation complete.
>>
>>insert into mytab2 values (1,1),(2,2),(3,3);

--- 3 row(s) inserted.
>>select * from mytab2;

C            D
-----------  -----------

          1            1
          2            2
          3            3

--- 3 row(s) selected.
>>
>>alter table mytab2 add constraint fk_not_enforced foreign key(d) references mytab1(a) not enforced;

*** ERROR[1143] Validation of constraint TRAFODION.MYTEST.FK_NOT_ENFORCED failed; incompatible data exists in referencing base table MYTAB2 and referenced base table MYTAB1.  To display the data that violates the constraint, please use the following DML statement: TBD

--- SQL operation failed with errors.
>>
>>drop schema mytest cascade;

--- SQL operation complete.



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