Professional Web Applications Themes

Changing Primary Index Question - Informix

GlacierYesterday I had to add a column to a table that had a 3-column primary index and this new column (a nullable column) had to become part of the primary key. The only way I could make it happen was to unload the table with null in that spot, drop the table, add the table, reload the table, add put back all other contraints. Just for my future reference, is there an easier way (under IDS 7.31) ? sending to informix-list...

  1. #1

    Default Changing Primary Index Question


    GlacierYesterday I had to add a column to a table that had a 3-column
    primary index
    and this new column (a nullable column) had to become part of the primary
    key.
    The only way I could make it happen was to unload the table with null in
    that spot,
    drop the table, add the table, reload the table, add put back all other
    contraints.

    Just for my future reference, is there an easier way (under IDS 7.31) ?

    sending to informix-list
    Bill Hamilton Guest

  2. #2

    Default Re: Changing Primary Index Question

    On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bhamfinsco.com>
    wrote:
    >
    >GlacierYesterday I had to add a column to a table that had a 3-column
    >primary index
    >and this new column (a nullable column) had to become part of the primary
    >key.
    >The only way I could make it happen was to unload the table with null in
    >that spot,
    >drop the table, add the table, reload the table, add put back all other
    >contraints.
    >
    >Just for my future reference, is there an easier way (under IDS 7.31) ?
    >
    As far as I know, the definition of a PK doesn't allow a nullable
    column . . . . am I wrong?
    John Carlson Guest

  3. #3

    Default Re: Changing Primary Index Question

    Nope I think you are right, at least with a single column index.

    John Carlson wrote:
    >
    > On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bhamfinsco.com>
    > wrote:
    >
    > >
    > >GlacierYesterday I had to add a column to a table that had a 3-column
    > >primary index
    > >and this new column (a nullable column) had to become part of the primary
    > >key.
    > >The only way I could make it happen was to unload the table with null in
    > >that spot,
    > >drop the table, add the table, reload the table, add put back all other
    > >contraints.
    > >
    > >Just for my future reference, is there an easier way (under IDS 7.31) ?
    > >
    >
    > As far as I know, the definition of a PK doesn't allow a nullable
    > column . . . . am I wrong?
    --
    Paul Watson #
    Oninit Ltd # Growing old is mandatory
    Tel: +44 1436 672201 # Growing up is optional
    Fax: +44 1436 678693 #
    Mob: +44 7818 003457 #
    [url]www.oninit.com[/url] #
    Paul Watson Guest

  4. #4

    Default RE: Changing Primary Index Question


    A column in a primary key cannot be null.

    1. Update that column so it is not null.
    2. Unload and delete rows with null.

    MW
    > -----Original Message-----
    > From: [email]owner-informix-listiiug.org[/email]
    > [mailto:owner-informix-listiiug.org]On Behalf Of Bill Hamilton
    > Sent: Thursday, 4 September 2003 3:12 a.m.
    > To: informix-list iiug
    > Subject: Changing Primary Index Question
    >
    >
    > GlacierYesterday I had to add a column to a table that had a 3-column
    > primary index
    > and this new column (a nullable column) had to become part of
    > the primary
    > key.
    > The only way I could make it happen was to unload the table
    > with null in
    > that spot,
    > drop the table, add the table, reload the table, add put back
    > all other
    > contraints.
    >
    > Just for my future reference, is there an easier way (under
    > IDS 7.31) ?
    >
    > sending to informix-list
    sending to informix-list
    Murray Wood \(IList\) Guest

  5. #5

    Default Re: Changing Primary Index Question


    With a single column index, you could have just one row that had a null in
    that column.
    With multiple columns, I believe you can have as many as you want, so long
    as the whole key is unique on each row.

    ----- Original Message -----
    From: "John Carlson" <john_carlsonwhsmithusa.com>
    To: <informix-listiiug.org>
    Sent: Wednesday, September 03, 2003 1:24 PM
    Subject: Re: Changing Primary Index Question

    > As far as I know, the definition of a PK doesn't allow a nullable
    > column . . . . am I wrong?
    sending to informix-list
    Bill Hamilton Guest

  6. #6

    Default RE: Changing Primary Index Question


    But one row with a null value on one of its
    primary key fields doesn't make sense
    because of the primary key's definition.


    Regards



    -----Mensaje original-----
    De: Bill Hamilton [mailto:bhamfinsco.com]
    Enviado el: Miércoles, 03 de Septiembre de 2003 02:50 p.m.
    Para: John Carlson; [email]informix-listiiug.org[/email]
    Asunto: Re: Changing Primary Index Question


    With a single column index, you could have just one row that had a null in
    that column. With multiple columns, I believe you can have as many as you
    want, so long as the whole key is unique on each row.

    ----- Original Message -----
    From: "John Carlson" <john_carlsonwhsmithusa.com>
    To: <informix-listiiug.org>
    Sent: Wednesday, September 03, 2003 1:24 PM
    Subject: Re: Changing Primary Index Question

    > As far as I know, the definition of a PK doesn't allow a nullable
    > column . . . . am I wrong?
    sending to informix-list

    sending to informix-list
    Francisco Roldan Guest

  7. #7

    Default Re: Changing Primary Index Question



    All:

    Yes .. Primary key field doesn't allow NULL value. That is the major
    difference UNIQUE constraint and PRIMARY KEY constraint.
    btw - UNIQUE index is different from UNIQUE constraint.

    Thank You
    Ramesh Vasudevan





    "John Carlson"
    <john_carlsonwhsm To: [email]informix-listiiug.org[/email]
    ithusa.com> cc:
    Sent by: Subject: Re: Changing Primary Index Question
    owner-informix-lis
    [email]tiiug.org[/email]


    09/03/03 02:24 PM
    Please respond to
    "John Carlson"






    On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bhamfinsco.com>
    wrote:
    >
    >GlacierYesterday I had to add a column to a table that had a 3-column
    >primary index
    >and this new column (a nullable column) had to become part of the primary
    >key.
    >The only way I could make it happen was to unload the table with null in
    >that spot,
    >drop the table, add the table, reload the table, add put back all other
    >contraints.
    >
    >Just for my future reference, is there an easier way (under IDS 7.31) ?
    >
    As far as I know, the definition of a PK doesn't allow a nullable
    column . . . . am I wrong?






    sending to informix-list
    ramesh.vasudevan@verizon.com Guest

  8. #8

    Default Re: Changing Primary Index Question

    On Wed, 03 Sep 2003 11:12:18 -0400, Bill Hamilton wrote:
    > GlacierYesterday I had to add a column to a table that had a 3-column primary
    > index
    > and this new column (a nullable column) had to become part of the primary key.
    > The only way I could make it happen was to unload the table with null in that
    > spot,
    > drop the table, add the table, reload the table, add put back all other
    > contraints.
    >
    > Just for my future reference, is there an easier way (under IDS 7.31) ?
    >
    > sending to informix-list
    1- BEGIN WORK;
    2- SET CONSTRAINTS ALL DEFERRED;
    3- ALTER TABLE ... DROP CONSTRAINT <primary constraint name>;
    4- ALTER TABLE ... ADD (newcol...);
    5- Populate newcol so it is not NULL;
    6- ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY (<old & new key cols>)...;
    7- Restablish FOREIGN keys on referring tables (they'll be dropped along with
    the primary key on this table).
    8- COMMIT WORK;

    Art S. Kagel
    Art S. Kagel Guest

  9. #9

    Default Re: Changing Primary Index Question


    You can add the column dynamically, but to make it a
    part of the index, you will have to recreate the
    index, since this "NULL" column is part of a
    multi-column primary key, you will get away with it,
    but will be out of luck if this is the only column in
    the index.
    --- [email]ramesh.vasudevanverizon.com[/email] wrote:
    >
    > All:
    >
    > Yes .. Primary key field doesn't allow NULL value.
    > That is the major
    > difference UNIQUE constraint and PRIMARY KEY
    > constraint.
    > btw - UNIQUE index is different from UNIQUE
    > constraint.
    >
    > Thank You
    > Ramesh Vasudevan
    >
    >
    >
    >
    >
    >
    >
    > "John Carlson"
    >
    >
    > <john_carlsonwhsm To:
    > [email]informix-listiiug.org[/email]
    >
    > ithusa.com> cc:
    >
    >
    > Sent by:
    > Subject: Re: Changing Primary Index Question
    >
    > owner-informix-lis
    >
    >
    > [email]tiiug.org[/email]
    >
    >
    >
    >
    >
    >
    >
    >
    > 09/03/03 02:24 PM
    >
    >
    > Please respond to
    >
    >
    > "John Carlson"
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton"
    > <bhamfinsco.com>
    > wrote:
    >
    > >
    > >GlacierYesterday I had to add a column to a table
    > that had a 3-column
    > >primary index
    > >and this new column (a nullable column) had to
    > become part of the primary
    > >key.
    > >The only way I could make it happen was to unload
    > the table with null in
    > >that spot,
    > >drop the table, add the table, reload the table,
    > add put back all other
    > >contraints.
    > >
    > >Just for my future reference, is there an easier
    > way (under IDS 7.31) ?
    > >
    >
    > As far as I know, the definition of a PK doesn't
    > allow a nullable
    > column . . . . am I wrong?
    >
    >
    >
    >
    >
    >
    > sending to informix-list

    __________________________________
    Do you Yahoo!?
    Yahoo! SiteBuilder - Free, easy-to-use web site design software
    [url]http://sitebuilder.[/url]
    sending to informix-list
    Abraham Kirubakaran Guest

Similar Threads

  1. default index for primary key of a table
    By Greg Stark in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 24th, 08:15 PM
  2. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  3. default index created for primary key
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: December 22nd, 07:30 PM
  4. Replies: 6
    Last Post: January 31st, 02:48 AM
  5. is primary key implicitly included in an index?
    By Zig Mandel in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139