IBM Support

With NOT ENFORCED Primary Key if we have duplicate records in table it may lead to nondeterministic result on Query

Technical Blog Post


Abstract

With NOT ENFORCED Primary Key if we have duplicate records in table it may lead to nondeterministic result on Query

Body

Constraints that are enforced by the database manager when records are inserted or updated .
If an application has already verified information before inserting a record into the table,
 it might be more efficient to use informational constraints, rather than normal constraints.

NOT ENFORCED is informational constraints
Informational constraints tell the database manager what rules the data conforms to, but
the rules are not enforced by the database manager.

While using NOT ENFORCED constraints its expected user and Application will make sure we
should not enter duplicate records in the table as we are not asking Database manager for
force the constraints.

Though we have NOT ENFORCED constraints if we still inserted Duplicate records in the table.
Then this may lead to nondeterministic result as we have Primary Key defined but its not Enforced.

So we need to take case at application level not to insert duplicate rows.

From following test case show issue in detail

1)Create Table:

$ db2 "create table t1 (c1 varchar(10) NOT NULL, c2 varchar(10) NOT NULL, c3 varchar(10),c4 varchar(10))
> ORGANIZE BY COLUMN"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1(c1,c2,c3) values ('a','1111','hoho')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1(c1,c2,c3) values ('a','1111','hihi')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1(c1,c2,c3) values ('a','1111','hehe')"
DB20000I  The SQL command completed successfully.
$ db2 "insert into t1(c1,c2,c3) values ('a','1111','nini')"
DB20000I  The SQL command completed successfully.

2)Ran query in Question returns all 4 rows
$ db2 "select * from t1 where c1 = 'a' and c2 = '1111'"

C1         C2         C3         C4        
---------- ---------- ---------- ----------
a          1111       hoho       -         
a          1111       hihi       -         
a          1111       hehe       -         
a          1111       nini       -         

  4 record(s) selected.

3)Added NOT ENFORCED contraint and reran the Query,Return only 1st Qualifying row,This is working as designed

$ db2 "alter table t1 add constraint ix_pk1 primary key (c1,c2) not enforced"
DB20000I  The SQL command completed successfully.


$ db2 "select * from t1 where c1 = 'a' and c2 = '1111'"

C1         C2         C3         C4        
---------- ---------- ---------- ----------
a          1111       hoho       -         

  1 record(s) selected.


As with 'Not enforced' constraint on c1,c2 indicates the user will ensure (c1,c2) is unique.
If we still have duplicate entries in Table result will nondeterministic.

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm13286143