Re: adding primary keys to a newly created table

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Rob,

Problem is you can't have
CREATE OR REPLACE TABLE AS (SELECT <...>)
CONSTRAINT mypk PRIMARY KEY (<...>);

        .-,------------------------------------------.
        V                                            |
> --+-(----+-*column-definition*----------------------+-+--)-+----->
   |      +-LIKE--+-*table-name*-+--+--------------+-+      |
   |      |       '-*view-name*--'  '-*copy-options*-' |      |
   |      +-*unique-constraint*----------------------+      |
   |      +-*referential-constraint*-----------------+      |
   |      '-*check-constraint*-----------------------'      |
   +-LIKE--+-*table-name*-+--+--------------+---------------+
   |       '-*view-name*--'  '-*copy-options*-'               |
   +-*as-result-table*--+--------------+--------------------+
   |                  '-*copy-options*-'                    |
   '-*materialized-query-definition*------------------------'


So you can have (*column-definition) *including any constraints

or you can use AS result-table


And unfortunately, constraints are not included as one of the copy-options.


Charles




On Thu, Mar 25, 2021 at 10:31 AM Rob Berendt <rob@xxxxxxxxx> wrote:

> Yes.  They are called "constraints".  For common CL command outfiles it's
> as simple as ADDPFCST.  For SQL you look at the constraint clause on CREATE
> TABLE.
> Run SQL Scripts allow you to build your own templates.  Here is one
> template I have
> --  category:  My really cool examples
> --  description:  A pretty Create or Replace Table
> CREATE OR REPLACE TABLE MyBigTableName for system name BigTable
> (
>     column1 INTEGER NOT NULL,                       -- Desc column 1
>     column2 VARCHAR(100) ALLOCATE(20)       -- Desc column 2
>     );
>
>
> -- Here's another sample.  This table was a lookup table.  For example,
> what's the last journal sequence number we looked at?
> -- As a war on 5250 tools the "short names" are obscured.
> set current schema = 'GDIHQ';
> CREATE OR REPLACE TABLE Lookup_table for system name T00000001
> (
>     Lookup_item for column C000000001 VARCHAR(100) ALLOCATE(20)
>         not null constraint Lookup_table_primary_key PRIMARY KEY,   --
> Item to lookup
>     Lookup_value_character for column C000000002 VARCHAR(100) ALLOCATE(10)
> with default null,      -- Value of item (if character)
>     Lookup_value_integer for column C000000003 bigint  with default null,
>     -- Value of item (if integer)
>     Lookup_value_decimal for column C000000004 decimal (25, 5) with
> default null,      -- Value of item (if decimal)
>     Lookup_value_timestamp for column C000000005 timestamp with default
> null,   -- Value of item (if timestamp)
>     CONSTRAINT Lookup_table_has_a_value CHECK ( -- One, and only one, of
> the lookup result columns will have a value.
>     (Lookup_value_character is not null and lookup_value_integer is null
> and Lookup_value_decimal is null and Lookup_value_timestamp is null) or
>     (Lookup_value_character is null and lookup_value_integer is not null
> and lookup_value_decimal is null and Lookup_value_timestamp is null) or
>     (Lookup_value_character is null and lookup_value_integer is null and
> lookup_value_decimal is not null and Lookup_value_timestamp is null) or
>     (Lookup_value_character is null and lookup_value_integer is null and
> lookup_value_decimal is null and Lookup_value_timestamp is not null))
>     )
>     RCDFMT T00000001R
> ;
> insert into lookup_table (lookup_item, lookup_value_integer)
> values('JOURNAL_SEQUENCE_NUMBER', 1);
> insert into lookup_table (lookup_item, lookup_value_timestamp)
> values('USER_CHANGE_TIMESTAMP', CURRENT TIMESTAMP - 1000 years);
> select * from lookup_table;
>
> Being the <expletive deleted> I am I obfuscated the column names and stuff
> to discourage the use of 5250 functions to get file information.
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  7310 Innovation Blvd, Suite 104
>           Ft. Wayne, IN 46818
> Ship to:  7310 Innovation Blvd, Dock 9C
>           Ft. Wayne, IN 46818
> http://www.dekko.com
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Alan Shore via MIDRANGE-L
> Sent: Thursday, March 25, 2021 11:30 AM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Cc: Alan Shore <ashore@xxxxxxxx>
> Subject: adding primary keys to a newly created table
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
> Hi everyone
> We are on V7r3
> We are creating  number of extracts to transfer over to another system
> The extracts are created in the following manner (This is just a simple
> example)
>
> CREATE OR REPLACE TABLE LIBRARY/OUTFILE AS
>           (SELECT SOURCE,ORDNO,CUSNO,PART,SPEC,ORQTY,DUEDT
>                   FROM INFILE
>                   WHERE SOURCE = '04')
>           WITH DATA
>           ON REPLACE DELETE ROWS
>
> We have now been asked to ensure certain fields within these tables are
> unique
> For example, in the above SOURCE and ORDNO
> I was looking into PRIMARY KEYS for this
> Here are my questions that I am trying to google search on
> Is there a way of including the primary keys as part of the above create?
> What happens if a duplicate key is found? - will the process stop, or is
> there a way to code this so that duplicates are bypassed - but I can be
> informed that duplicates exist?
>
> My Google search has not yet come up with anything definitive that answers
> the above questions, but my search continues
>
> Alan Shore
> Solutions Architect
> IT Supply Chain Execution
>
> [cid:image003.png@01D7216A.4107F710]
>
> 60 Orville Drive
> Bohemia, NY 11716
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx
>
> 'If you're going through hell, keep going.'
> Winston Churchill
>
>
>
> The Nature’s Bounty Co. is now The Bountiful Company! Our email address
> will be changing from @nbty.com to @bountifulcompany.com. Please update
> your email address book so that important communications are not
> interrupted.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com