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

Eric,

Thanks for your answer. My problem wasn't really in how to define the constraint, but how to do it inside a CREATE TABLE sentence where the fields are referenced from another file (using SELECT ... FROM). I've checked the manuals and tried a lot of different sentences, but it just seems this not possible.

It is not a big deal, just curious about the syntax.

Thanks,


Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


--- On Thu, 8/13/09, midrange-l-request@xxxxxxxxxxxx <midrange-l-request@xxxxxxxxxxxx> wrote:

RE: Defining a Primary Key on a "Create Table As" SentenceâThursday, August 13, 2009 6:34 PM
From: "DeLong, Eric" <@Sallybeauty.com>

Here's a snip of a DDL Create table...

CREATE TABLE INPTCSCFGR (
WHS_ID FOR COLUMN TCSWHS#
CHAR(3)
CCSID 37
NOT NULL DEFAULT '' ,
WHS_UTC_OFFSET FOR COLUMN TCSWHSUTCO
INTEGER
NOT NULL DEFAULT 0 ,
SVR_PORT FOR COLUMN TCSSVRPORT
INTEGER
NOT NULL DEFAULT 0 ,
SVR_STATUS FOR COLUMN TCSSVRSTS
CHAR(1)
CCSID 37
NOT NULL DEFAULT '' ,
...
...
CLT_SMALL_CARRIER_LANES FOR COLUMN TCSSPCLNS
DECIMAL(2,0)
NOT NULL DEFAULT 0,

CONSTRAINT INPTCSCFG_TCSWHS#_00001 PRIMARY KEY( WHS_ID ) ) ;

RENAME TABLE INPTCSCFGR TO INPTCSCFG;

LABEL ON TABLE INPTCSCFG
IS 'Socket Server - TCS to WMS Config/Status Database' ;

LABEL ON COLUMN INPTCSCFG
( WHS_ID IS 'Whs#' ,
WHS_UTC_OFFSET IS 'Whs UTC Offset' ,
SVR_PORT IS 'TCP/IP Port (Server)' ,
SVR_STATUS IS 'TCP/IP Status (Server)' ,
...
...

Hth,
-eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Thursday, August 13, 2009 2:58 PM
To: Midrange-L
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.

---
Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions