Changing Primary Index Question
Bill Hamilton had to unload, drop, recreate and reload a table to add a nullable column to a 3-column primary key on IDS 7.31. John Carlson and Paul Watson doubted a PK may contain a nullable column. Art S. Kagel gave a procedure: defer constraints, drop the PK, add the column, populate it with non-NULLs, re-add the PK, restore foreign keys, commit.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
On Wed, 3 Sep 2003 10:12:18 -0500, "Bill Hamilton" <bham@finsco.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?
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" <bham@finsco.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 # www.oninit.com #
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