What Happened to My Key?
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 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.