Re: Create table LIKE another including Primary key...

On 08-Jun-2016 15:05 -0500, Charles Wilt wrote:

Is there some way to use SQL to create a table LIKE another including
the primary key?

This doesn't work:

CREATE TABLE mytbl2
LIKE mytbl1
PRIMARY KEY (mykey)
;

The manual seems to imply it's possible with a copy option...
"The implicit definition does not include any other optional
attributes of the identified table or view. For example, the new
table does not automatically include primary keys, foreign keys, or
triggers. The new table has these and other optional attributes only
if the optional clauses are explicitly specified."

But I don't see any copy option that mentions primary keys...

CRTDUPOBJ and CPYF are not options as the table being copied has a
long name.

Of course I can ALTER TABLE to add the primary key, but it'd be nice
to know if there was a way to include the primary key from the
get-go.

The LIKE clause has two distinct usages, depending on where are, or are not, parentheses included in the CREATE TABLE statement; i.e. there are two variants for syntax, for the use of the "LIKE <view|table-name> <copy-options>" clause. Use the following syntax, rather than that which is shown in the OP:

CREATE TABLE mytbl2
( LIKE mytbl1
, PRIMARY KEY (mykey)
)

or perhaps more desirable, to assign more names:

CREATE TABLE myMuchLongerNamedTable2
FOR SYSTEM NAME mytbl2
( LIKE mytbl1
, CONSTRAINT myMuchLongerNamedTable2_PK
PRIMARY KEY (mykey)
)

Doc reference to clarify the syntax:
IBM i -> IBM i 7.1 -> Database -> Reference -> SQL reference ->
Statements -> CREATE TABLE
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzhctabl.htm]

And here I responded to a similar question, but initially for use of the as-result-table syntax, offering variants of dynamic compound statements to achieve something similar [but of course, intending to effect a copy of the file with some\all data, but including an example much the same as the above using a parenthetical LIKE].