Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Create File using SQL with Unique Key
has no ratings.
Published: 18 Jun 2015
Revised: 26 Jun 2015 - 969 days ago
Last viewed on: 19 Feb 2018 (2508 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Create File using SQL with Unique Key Published by: Bob Cozzi on 18 Jun 2015 view comments(4)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: starbuck5250
Premium member *
Comment on: Create File using SQL with Unique Key
Posted: 2 years 8 months 3 days 6 hours 35 minutes ago

Very cool, and nice examples!

Posted by: clbirk
Premium member *
Comment on: Create File using SQL with Unique Key
Posted: 2 years 7 months 28 days 8 hours 58 minutes ago

I guess I don't follow what you are trying to say here. If you have a table like item master, where the item# is to be unique, you simply make that the primary key and be done with it. Primary keys must be unique. I guess if you need an identity column for sequencing purposes, well... Don't understand why you are doing an "identity" on like an alpha numeric file.

Yes identity columns are nice, being auto incrementing, etc. but I guess I am old school when it comes to things like customer#'s (not order#, etc.) in that to avoid single and most double digit transposition errors, you use a number that is a mod11 check digit.

Also as I recall, a primary key is a constraint, where as a unique index is an index, but of course db2 uses unique indexes to implement primary keys.  I don't believe that a primary key can be on a nullable field.

chris

 

 

 

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: Create File using SQL with Unique Key
Posted: 2 years 7 months 28 days 4 hours 43 minutes ago

A primary key is a UNIQUE key.  From the SQL manual (7.1 page 933):

"The unique index is created as part of the system physical file, not a separate

system logical file. When a primary key is added, a CHECK constraint is

implicitly added to enforce the rule that the NULL value is not allowed in any

of the columns that make up the primary key."

The tables on my system that have primary keys show in DSPFD that the key is unique.

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Create File using SQL with Unique Key
Posted: 2 years 7 months 28 days 2 hours 44 minutes ago
Edited: Thu, 25 Jun, 2015 at 13:41:02 (970 days ago)

Brian and clbirk are correct. I am wrong.

The only difference: UNIQUE keys can be NULLable, PRIMARY cannot.

Apparently when I did my test, I used a legacy Physical File (*FILE PF-DTA) that had a Primary Key, but was created using DDS. The DDL also existed, but was not actually used to build the file.

Years ago I learned that the ultimate authority (regardless of what the documentation says) is what the system says, or does. Thus my information was for my original test case. So I created a new test just now, using one file with a PRIMARY key, and a 2nd with a UNIQUE key. Both returned "Duplicate key value specified." when I attempted the INSERT a 2nd value that had an identical key value to one already in the file.

Thanks for pushing me, I appreciate it.