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

Thanks Chuck.

I might play around with that. Though what I'd really like is a
create table mytbl2 like mytbl
including primary keys

That way I wouldn't have to look up the existing primary key and specify it.

I suppose I could build my own stored procedure to do it...

Charles



On Mon, Jun 20, 2016 at 5:37 PM, CRPence <crpbottle@xxxxxxxxx> wrote:

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].

--
Regards, Chuck


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.