RE: Defining a Primary Key on a "Create Table As" Sentence…

Elvis,

Thanks for your answer. I have checked the manual and tried all the possible combinations to no avail, so I suppose this is not possible (at least under V5R3). It is a pity because, although the end result is the same, it is not (at least for me) as elegant as including the sentence inside the CREATE TABLE statement would be...


Thanks again,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


--- On Fri, 8/14/09, midrange-l-request@xxxxxxxxxxxx <midrange-l-request@xxxxxxxxxxxx> wrote:

RE: Defining a Primary Key on a "Create Table As" SentenceâFriday, August 14, 2009 1:51 AM
From: "Elvis Budimlic" <@centerfieldtechnology.com>
To: "'Midrange Systems Technical Discussion'"

I wondered the same thing a few months back. I came up with something that came close:

CREATE TABLE New_Table (LIKE Reference_Table, PRIMARY KEY (field1))

Of course, this won't work for you, since you are not selecting all columns from the reference table.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Defining a Primary Key on a "Create Table As" Sentenceâ

Hi,

(Using V5R3)
Just curious, when you define a SQL table based on another table, as in:

----------
CREATE TABLE New_Table AS(
Select field1, field2 FROM Reference_Table
)WITH NO DATA;
----------

How do you define a primary key? Until now, I have always used an ALTER TABLE sentence (in the same script) for adding a primary key, but I was wondering if there would another way to define it (as in writing it inside the CREATE TABLE statement).

Thanks,

Luis.