RE: [EXTERNAL] Re: adding primary keys to a newly created table -- MIDRANGE-L

Thanks for your reply Charles
The more Im looking into this – the more I believe primary keys are not the way to go
Maybe a separate SQL obtaining counts of records – grouped by the fields that are considered primary fields and if there are any such records – send an e-mail with the duplicate keys and delete records from the file that have duplicate keys, but the later relative record number (I hope this last piece makes some sense)

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D72172.D93DBFD0]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

‘If you're going through hell, keep going.’
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 25, 2021 12:27 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: adding primary keys to a newly created table

In one statement? No.

You could run an ALTER TABLE after the extract...but in that case, if you
replaced the table that already had a PK constraint, then you'd have to
handle the error thrown when trying to ADD a pk constraint.

Charles

On Thu, Mar 25, 2021 at 9:30 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>> wrote:

Hi everyone
We are on V7r3
We are creating number of extracts to transfer over to another system
The extracts are created in the following manner (This is just a simple
example)

CREATE OR REPLACE TABLE LIBRARY/OUTFILE AS
(SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT
FROM INFILE
WHERE SOURCE = '04')
WITH DATA
ON REPLACE DELETE ROWS

We have now been asked to ensure certain fields within these tables are
unique
For example, in the above SOURCE and ORDNO
I was looking into PRIMARY KEYS for this
Here are my questions that I am trying to google search on
Is there a way of including the primary keys as part of the above create?
What happens if a duplicate key is found? - will the process stop, or is
there a way to code this so that duplicates are bypassed - but I can be
informed that duplicates exist?

My Google search has not yet come up with anything definitive that answers
the above questions, but my search continues

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image003.png@01D7216A.4107F710]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>

'If you're going through hell, keep going.'
Winston Churchill

The Nature’s Bounty Co. is now The Bountiful Company! Our email address
will be changing from @nbty.com to @bountifulcompany.com. Please update
your email address book so that important communications are not
interrupted.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com<https://amazon.midrange.com>

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>

The Nature’s Bounty Co. is now The Bountiful Company! Our email address will be changing from @nbty.com to @bountifulcompany.com. Please update your email address book so that important communications are not interrupted.