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.
UID
ibm13286143