Can we Insert duplicates in Primary Unique column ?

Poha Eater · New User Joined: 31 Aug 2016 Posts: 74 Location: India

Hi,

There are three columns in a table, ex : C1 | C2 | C3
All three columns are defined as PU (Primary unique) and i think these three columns all together creates a composite key.

The issue is one of our Batch cycle Support member is saying that he got a SQL abend for SQLSTATE - 21000 with duplicate value in a column C3 while fetching value from column C3 through a SELECT query in COBOL-DB2 program. So he edited that value in C3 in the table so that it will not be a duplicate and he resumed the batch cycle.

I was trying to find out if this is possible or not, i have encountered the below link where it is mentioned about using NOT ENFORCED for PU column if i have got that correctly. But i am still not able to grasp everything from this link so the concept is still not clear to me.

Can anyone please explain if we can insert duplicates in Primary Unique column using NOT ENFORCED ?

Link - www.ibm.com/support/pages/not-enforced-primary-key-if-we-have-duplicate-records-table-it-may-lead-nondeterministic-result-query

Rohit Umarjikar · Posted: Thu Dec 17, 2020 7:51 pm

If it has a unique index then you can't.

Phrzby Phil · Posted: Fri Dec 18, 2020 3:32 am

Poha - if you want to become an expert, then you should first of all rely on your own testing, rather than take someone else's (even an expert's) word for it.

I suggest you create a suite of test programs for every feature you question. Not only will you remember the answer better a year form now when a situation again arises, but you'll become the leader in your own group.

You can also pass them on to junior members of your group when they join.

Your question above seems ideally suited for a variety of tests with different combinations of options, etc.