Primary key

Hi, Is it possible to add a column to the primary key whithout unloading, droping, Re-creating then loading the table?. Tanks for helping. Regards.

Yes and no. last time I checked, you cannot 'add' a column to an index period. However, you can create a new index, or drop and re-create the index in question. You do not have to reload the table. Indices are separate from data - so you don't have to worry about reloading your data, however the index is a btree+ - all of the values for the index are in a tree, it would be most interesting to attempt to alter such a tree. Hence drop and recreate or just create a new one - mind you each additional index brings with it a performance hit when it comes time to write the data - each index has to be written as well. cheers j. ----- Original Message ----- From: "Hamid HABZI " <HHABZI@bmcebank.co.ma> To: <ids@iiug.org> Sent: Monday, March 03, 2003 10:33 AM Subject: Primary key [553] > Hi, > Is it possible to add a column to the primary key whithout unloading, > droping, Re-creating then loading the table?. > Tanks for helping. > Regards. > > >

Yes. Below is an outline of the necessary steps. 1) Drop the primary key constraint. 2) Drop the unique index, which underlies the primary key constraint (if it exists). 3) Create a new unique index for the primary key. 4) Create a new primary key constraint. 5) Run "UPDATE STATISTICS" for the new primary key. a) UPDATE STATISTICS HIGH for the column which heads the index. b) UPDATE STATISTICS LOW for all columns in the index. Rick -----Original Message----- From: Hamid HABZI [mailto:HHABZI@bmcebank.co.ma] Sent: Monday, March 03, 2003 7:33 AM To: ids@iiug.org Subject: Primary key [553] Hi, Is it possible to add a column to the primary key whithout unloading, droping, Re-creating then loading the table?. Tanks for helping. Regards.

Hamid The Primary Key is a constraint on the table and can be removed in situ (but needs exclusive access to the table) using the alter table syntax. You can then use alter table again to add a new primary key constraint. I would suggest you create your own unique index on the primary key columns (rather than let the engine create and name its own index) so that it is easily identified and you can determine the placement and fragmentation strategy (if you need to). Keith -> -----Original Message----- -> From: Hamid HABZI [mailto:HHABZI@bmcebank.co.ma] -> Sent: Monday, March 03, 2003 3:33 PM -> To: ids@iiug.org -> Subject: Primary key [553] -> -> -> Hi, -> Is it possible to add a column to the primary key whithout unloading, -> droping, Re-creating then loading the table?. -> Tanks for helping. -> Regards. -> -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

You can drop and recreate the primary key: Alter table <tabname> drop constraint <prim_key_constr>; alter tabel <tabname> add constraint primary key (<colx>....) The needed prim_key_constraint name can be found in dbaccess->table->info. On Mon, 3 Mar 2003 10:33:26 -0500 (EST) "Hamid HABZI " <HHABZI@bmcebank.co.ma> wrote: > Hi, > Is it possible to add a column to the primary key whithout unloading, > droping, Re-creating then loading the table?. > Tanks for helping. > Regards. > > -- Mit freundlichen Grüßen Gerd Kaluzinski \\\\\\\\|// (o o) --------------------------------------------------ooO-(_)-Ooo--- Gerd Kaluzinski mailto:gerd.kaluzinski@bytec.de Manager Consulting mailto:support@bytec.de Informix Certified Senior System Engineer http://www.bytec.de BYTEC GmbH Telefon: 07541-585-1019 Hermann-Metzger-Str. 7 Fax : 07541-585-2019 88045 Friedrichshafen Ooo. -------------------------------------------------.ooO----( )--- ( ) (_/ \\\\_)

You will need to drop the primary key and re-add the primary key ( with an alter table statement ). I would suggest adding a unique index on the primary key fields prior to altering the table to add the primary key. Thiswill give you the ability to name the primary key ( the engine is smart enough to know to use an existing unique index for the primary key constraint.) Then when it comes time for table re-extenting or performance issues you have the option to cluster the index ( a problem another thread addressed today ). George -----Original Message----- From: Hamid HABZI [mailto:HHABZI@bmcebank.co.ma] Sent: Monday, March 03, 2003 8:33 AM To: ids@iiug.org Subject: Primary key [553] Hi, Is it possible to add a column to the primary key whithout unloading, droping, Re-creating then loading the table?. Tanks for helping. Regards.

Yes -- RTFM. ALTER TABLE ADD (newcolumn INTEGER DEFAULT 0 NOT NULL), DROP CONSTRAINT pk_constraint, ADD CONSTRAINT PRIMARY KEY (oldpkcol1, oldpkcol2, newcolumn)CONSTRAINT pk_constraint; You might need to do some surgery on the zeroes in the new column in the example. If you don't provide a default and do have data in the database, you won't be able to do it in one statement because the new column will be assigned nulls in the absence of a default, and you can't create a primary key on a column containing nulls. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Hamid HABZI " | | | <HHABZI@bmcebank.| | | co.ma> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 03/03/2003 07:33 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Primary key [553] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Hi, Is it possible to add a column to the primary key whithout unloading, droping, Re-creating then loading the table?. Tanks for helping. Regards.

LTX - possibly - but then you've got problems, period, on reorganizing your table. You should consider adding more logical logs and/or getting more disk space. To add a serial column is much harder if the table already contains data. That requires adding the column as an INTEGER, possibly with a default of 0, possibly allowing nulls instead. Then you have to do an update which sets the soon-to-be-SERIAL column to a set of unique values - that's tough to do in pure SQL. Then you can alter the column, converting it to SERIAL NOT NULL and add the primary key constraint - having dropped the previous one. That's sufficiently hard that I would try to avoid doing all that; I'd prefer to be able to create a new empty table with the extra column, then do INSERT INTO NewTable(SerialColumn, OtherColumn1, ...) SELECT 0, * FROM OldTable. This would be followed by creating the indexes (don't do that beforehand - it slows the insertion process to no advantage) and constraints, and then you rename OldTable to ExtraOldTable and rename NewTable to OldTable. Clearly, this uses double the amount of disk - but it reorganizes the table, and you can ensure the extent sizes are correct, and so on. Note that you can't order the data before it is inserted - which is a pain; you will get whatever pseudo-random sequence (probably not very random, but probably not the order you had in mind either) the server chooses. If you don't have double the disk space, you're back with the first option - assuming your server supports IPA (in-place alters) for the addition of a column. If it doesn't, then you're snookered anyway -- buy some more disk (it is cheap, isn't it). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | Manel Falcó | | | <manel@semic.es> | | | | | | 03/07/2003 10:43 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: Jonathan Leffler/Menlo Park/IBM@IBMUS | | cc: <ids@iiug.org> | | Subject: RE: Primary key [570] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Any long transaction problem ? and what about if I want to add a SERIAL column ? Manel -----Mensaje original----- De: Jonathan Le.... [mailto:jleffler@us.ibm.com] Enviado el: lunes, 03 de marzo de 2003 19:34 Para: ids@iiug.org Asunto: Re: Primary key [570] Yes -- RTFM. ALTER TABLE ADD (newcolumn INTEGER DEFAULT 0 NOT NULL), DROP CONSTRAINT pk_constraint, ADD CONSTRAINT PRIMARY KEY (oldpkcol1, oldpkcol2, newcolumn)CONSTRAINT pk_constraint; You might need to do some surgery on the zeroes in the new column in the example. If you don't provide a default and do have data in the database, you won't be able to do it in one statement because the new column will be assigned nulls in the absence of a default, and you can't create a primary key on a column containing nulls. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Hamid HABZI " | | | <HHABZI@bmcebank.| | | co.ma> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 03/03/2003 07:33 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Primary key [553] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Hi, Is it possible to add a column to the primary key whithout unloading, droping, Re-creating then loading the table?. Tanks for helping. Regards.