Alter table add foreign key constraint ignores ‘not enforced’

[Weishiun Tsai]

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.

[Anoop Sharma (anoop-sharma)]

Is this a new test or a regression?

[Weishiun Tsai (wei-shiun-tsai)]

It's a set of old tests that we just got around to porting to Trafodion. Not sure what the behavior was like in Trafodion 0.9.0. I am assuming that it is a day 1 problem in Trafodion ever since foreign keys were supported.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v1.0.0 rc2 build, this problem has been fixed:

>>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_878656715_1456 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;

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

--- SQL operation complete.
>>select * from mytab2;

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

          1 1
          2 2
          3 3

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

--- SQL operation complete.