What Happened to My Key?

Published: June 25, 2008

Hey, Ted:

We use SQL's CREATE TABLE command to make each user his own copy of a file. Although the original file is keyed, the copy is not. Do you have any ideas on how to keep the key?

--Mary Jo

Mary Jo provided the following commands by way of example:

create table Temp as
  (select * from PATTERN) with no data 

rename table Temp to FileForJoeSmith

So, what's Mary Jo to do?

The first thought I had was to create the index by hand, like this:

create unique index FileForJoeSmithIndex1
   on FileForJoeSmith (SomeField)

But that was not a solution to the problem, as native programs needed to access the file randomly by key. (But she could have accessed the file from native programs by going through the logical file behind the index.)

She wrote back with the solution.

alter table FileForJoeSmith
  add constraint FileForJoeSmithPK
  primary key (SomeField)

That did the trick. Whereas CREATE TABLE created a sequential file, ALTER TABLE converted it into a keyed file.

This looks like another one of those cases where the native interfaces (in this case, CRTDUPOBJ) outshine SQL, as I mentioned two weeks ago.

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot