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 - 732 days ago
Last viewed on: 27 Jun 2017 (1864 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)

I've been using SQL to create files for years now. I've used IDENTITY columns (love them!) as well as the PRIMARY KEY clause to create a S/38-like keyed physical file. But I never bothered until recently, to worry about the UNIQUE keyword in DDS when creating physical files. I have used it, but so rarely that it has never come up, until recently. [Without the UNIQUE keyword in DDS, files are created with non-unqiue access paths. This is what I was working with.]

I needed to create a table with a unique key. Why unique? Because I wanted the SQL INSERT command to fail if the user attempted to add a new record to the file with a key that already existed in the file. For example, when two identical INSERT statements are issued; the first would succeed, the second sould fail.

There is a new MERGE statement that works on the very latest releases (latest TR's) of the OS. The MERGE statement works more like the old RPG I/O "Test if record exists, if so, update it, otherwise write it to the file". What I need is something that just works system-wide, with worrying about CHAIN/UPDATE/WRITE or MERGE being used properly; I need UNIQUE primary keys.

Most of you are familiar with creating a file using SQL DDL, if not here's a quick example:

CREATE TABLE QGPL/README (
  textData varchar(50) CCSID 37 NOT NULL DEFAULT '',
  crtdate DATE NOT NULL DEFAULT CURRENT_DATE
 );

This creates a 2-column table (a file with two fields) named README. The two fields are TEXTDATA and CRTDATE. The CRTDATE field is automatically populated with the date the record is added to the file, while TEXTDATA is presumably provided by the user.

If I wanted to add a key to this file so I could, for example, remove certain rows of data directly, I could use some sort of application generated index, or preferably I would use an IDENTITY column as follows:

CREATE TABLE QGPL/README (
 LINENO INTEGER PRIMARY KEY 
           GENERATED ALWAYS AS IDENTITY 
        (START WITH 31, INCREMENT BY 1), 
 textData varchar(50) CCSID 37 NOT NULL DEFAULT '',
 crtdate DATE NOT NULL DEFAULT CURRENT_DATE
 );

The field LINENO (Line Number) has been added. The "GENERAETED ALWAYS AS IDENTITY" causes the database to automatically set the value of this field, incrementing it by 1 upon an INSERT operation. Note that I've started LINENO at 31 just for fun. I use IDENTITY columns all the time.

But now back to the original reason for this post. I need to create a file with a UNIQUE Primary Key. Note that in the README file, the IDENTITY column includes the "PRIMARY KEY" clause. This creates an index over the file by LINENO. The system automatically generates the nexxt LINENO value by incrementing by 1. This insures you always have the next LINENO and a unique key value with which to access the individual rows (records) in that file. But what if I have another field, like Customer Number or Vendor Code that needs to also be unique in the file?

For customer numbers, you can use either a SEQUENCE object or an IDENTITY column as I've done here. But for text-based values that are also user-supplied (not auto-generated by the database), so you have to code things a bit differently.

In this scenario, I have a file named ITEMS containing a 5-character ITEM "number" that is a alphanumeric. I also want an IDENTITY column, just to make it really easy to delete specific records from the file. To make this happen, the table would need to look something like this:

CREATE TABLE QGPL/ITEMS (
 CID INTEGER GENERATED ALWAYS AS IDENTITY,
 ITEM CHAR(5) CCSID 37 NOT NULL, 
 TITLE VARCHAR(50) CCSID 37 NOT NULL DEFAULT '',
 UNIQUE(ITEM) 
 );

The CID field is the identity column and the database takes care of populating it with the next available value. The ITEM field is a 5-character field whose value must be unique within the file. The UNIQUE(ITEM) keyword forces that to occur. Unlike Primary key fields, UNIQUE key fields may be NULLable. So the NOT NULL required by PRIMARY KEY, doesn't apply to UNIQUE keys. The last field, TITLE, is user-supplied information that can be anything.

To add records to the ITEMS file, the INSERT statement (or RPG WRITE opcode) may be used.

INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('RPG', 'The Modern RPG Language');

Great! Now we have 1 record in the file. Let's add two more:

INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('SQL', 'The Modern SQL Language');
INSERT INTO QGPL/ITEMS (ITEM,TITLE) VALUES('RPG', 'Writing Subfiles with RPG');

Oh no! The last INSERT contains the same value for ITEM as the first INSERT operation. What happens? The system will issue an SQL error code, and that 2nd record, it will go away--it is not added to the file.

This is the advantage of keyed tables vs non-keyed tables.

Pretty cool, huh?

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 9 days 11 hours 15 minutes ago

Very cool, and nice examples!

Posted by: clbirk
Premium member *
Comment on: Create File using SQL with Unique Key
Posted: 2 years 3 days 13 hours 38 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 3 days 9 hours 23 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 3 days 7 hours 24 minutes ago
Edited: Thu, 25 Jun, 2015 at 13:41:02 (733 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.