Informix

 View Only
  • 1.  integer to serial conversion

    Posted Sun December 29, 2019 10:42 AM
    Hi,

    I have several tables with integer key fields and I want to change to serial, is there a way to make the change propagating this to linked constraints?

    Thanks for any help,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------

    #Informix


  • 2.  RE: integer to serial conversion

    IBM Champion
    Posted Sun December 29, 2019 11:18 AM
    What version ? Only the latter releases - not sure when - can do this 

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: integer to serial conversion

    Posted Mon December 30, 2019 05:02 AM
    well, that's one of the problems, since we have client versions from 11.70 to 14.10 we are also defining a base version so we can make the change.
    What minimum version is required?
    Does alter table propagate changes through constraints?

    Regards,

    SP


    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------



  • 4.  RE: integer to serial conversion

    Posted Mon December 30, 2019 08:16 AM

    Sérgio,

    The feature Paul is referring to (I think) is implemented in 12.10.xC12W1+

    To be clear, you don't need to do the alter on the child table. The SERIAL will generate a new value when you INSERT (with a 0) on the parent table. When you INSERT on the child table you just use the parent's table matching value.

    What you need to keep in mind, and this is the tricky part, is that once you do the change on the parent table, all the child table's foregin keys will be dropped (if the above feature is not available).
    On those cases you need to recreate the foreign keys again, depending on the version you can use the "NOVALIDATE" option to speed it up (make sure no changes on the parent table can occur while you do this).

    Here's a (hopefully) full example:

    drop table if exists parent_tab;
    drop table if exists child_tab;
    create table parent_tab
    (
    col1 INTEGER,
    col2 INTEGER,

    PRIMARY KEY (col1)
    );

    create table child_tab
    (
    col1 INTEGER,
    col2 INTEGER,
    PRIMARY KEY (col1)
    );

    ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1;

    INSERT INTO parent_tab VALUES (1,1);
    INSERT INTO child_tab VALUES(1,1);

    -- USE on 12.10.xC12w1+
    --ALTER TABLE parent_tab MODIFY (col1 SERIAL primary key constraint pk_parent_tab KEEP ANY REFERENCING FOREIGN KEY);

    -- USE on 12.10.xC12-
    ALTER TABLE parent_tab MODIFY (col1 SERIAL primary key constraint pk_parent_tab);

    -- Just to illustrate the NOVALIDATE
    DELETE FROM parent_tab WHERE col1 = 1;

    -- USE on 11.70.xC7- (before 11.17.xC8)
    --ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1;


    -- Use on 11.70.xC8+ and 12.10.xC3 <= and 12.10.xC12
    ALTER TABLE child_tab ADD CONSTRAINT FOREIGN KEY (col2) REFERENCES parent_tab CONSTRAINT fk_child_tab_1 NOVALIDATE;

    Hope this helps. Please test it...

    Regards.



    ------------------------------
    FERNANDO NUNES
    ------------------------------



  • 5.  RE: integer to serial conversion

    Posted Mon December 30, 2019 10:09 AM
    Thanks Fernando,

    That was precisely my doubt, which is the minimum version. Because we have several versions we need to validate the minimum requirements.
    The example is excellent for testing.

    Best regards and Happy New Year,

    SP

    ------------------------------
    Sergio Peres
    AIRC
    Coimbra
    ------------------------------



  • 6.  RE: integer to serial conversion

    IBM Champion
    Posted Mon December 30, 2019 10:35 AM
    Fernando:

    This "KEEP ANY REFERENCING FOREIGN KEY" clause is not documented in the online docs for v14.10! FWIW.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 7.  RE: integer to serial conversion

    Posted Mon December 30, 2019 10:48 AM

    Art,
    I believe it's not documented anywhere (knowledge center). It's on a presentation for this year's IIUG conf (*SQL Features*).

    Another incredible case of good things being trashed by lack of documentation... I've tried to fight that war several times... Probably only a handful of customers know about this sort of things, and the vast majority never gets to know this. Meanwhile development employed resources to do good things and the product got better...

    I don't understand who's responsible for this (HCL vs IBM) but I also doubt it matters... We know who is hurt by ignoring new (and very nice) functionalities: the customers.

    Regards and Happy New Year



    ------------------------------
    FERNANDO NUNES
    ------------------------------