Drop primary key
Dan DeHaan on IDS 7.2 wanted to drop a primary key on flights(flt_id) to rebuild its index, but 'alter table drop primary key' gave error 201. Gregory Williamson suggested dropping the unique index; Nilesh Ozarkar gave ALTER TABLE DROP CONSTRAINT with the constraint name. Jonathan Leffler said to omit the key name from the syntax and noted 7.2 is obsolete.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Running IDS 7.2 on HPUX11. I need to rebuild an index that is shared by a primary key. The table is very large and I would rather not drop and load the table. The table name is 'flights' and the primary key is on a column named 'flt_id'. I use the syntax 'alter table flights drop primary key flt_id' (and variations) only to get the reply '201: A syntax error has occured'. How do I drop the primary key?
Dan, Check the description of the table (\\\\d tablename in the psql tool). You will see that there is a unique index associated with the column(s) in question -- drop that index and I think you've removed part of what makes a "primary" (the other being a NON NULL restriction, I believe). HTH, Greg Williamson DBA GlobeXplorer LLC -----Original Message----- From: DAN DEHAAN [mailto:ddehaan@skywest.com] Sent: Thu 1/20/2005 1:47 PM To: ids@iiug.org Cc: Subject: Drop primary key [4064] Running IDS 7.2 on HPUX11. I need to rebuild an index that is shared by a primary key. The table is very large and I would rather not drop and load the table. The table name is 'flights' and the primary key is on a column named 'flt_id'. I use the syntax 'alter table flights drop primary key flt_id' (and variations) only to get the reply '201: A syntax error has occured'. How do I drop the primary key?
ALTER TABLE <table-name> DROP CONSTRAINT <primary-key or unique constraint-name> Regards, Nilesh "DAN DEHAAN" <ddehaan@skywest.com> Sent by: forum.subscriber@iiug.org 01/20/2005 03:47 PM To ids@iiug.org cc Subject Drop primary key [4064] Running IDS 7.2 on HPUX11. I need to rebuild an index that is shared by a primary key. The table is very large and I would rather not drop and load the table. The table name is 'flights' and the primary key is on a column named 'flt_id'. I use the syntax 'alter table flights drop primary key flt_id' (and variations) only to get the reply '201: A syntax error has occured'. How do I drop the primary key?
"DAN DEHAAN" <ddehaan@skywest.com> wrote on 01/20/2005 01:47:56 PM: > Running IDS 7.2 on HPUX11. > > I need to rebuild an index that is shared by a primary key. The > table is very large and I would rather not drop and load the table. > The table name is 'flights' and the primary key is on a column named > 'flt_id'. I use the syntax 'alter table flights drop primary key > flt_id' (and variations) only to get the reply '201: A syntax error > has occured'. > > How do I drop the primary key? Dear Dan, I think I saw this answered on the comp.databases.informix news group - the answer is omitting the key name from the syntax you used. I don't know whether anyone else pointed out that IDS 7.2 is obsolete - you should be on at least 7.31.UDx (for x >=7), or even IDS 9.40. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 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!"