Re: [Hsqldb-user] Strange Primary key difference issue between 1.8.0.1 and 1.8.0.4

[Carl G. <go...@in...>]

Ah, so simple! Thanks Fred, that works like a charm. The 
SYSTEM_CROSSREFERENCE table doesn't seem to suffer from the same mix-up, so
my FK manipulations work fine.

Thanks,
Carl

fredt wrote:

>Try:
>
>ALTER TABLE T DROP PRIMARY KEY
>
>Fred
>----- Original Message ----- 
>From: "Carl Gould" <go...@in...>
>To: <hsq...@li...>
>Sent: 27 April 2006 01:16
>Subject: Re: [Hsqldb-user] Strange Primary key difference issue between 
>1.8.0.1 and 1.8.0.4
>
>
>Fred,
>
>That would have been a good idea, but its too late now. This application
>has many deployed instances, and as we release new versions of our
>software, the schema is automatically upgraded if necessary.
>
>This particular upgrade (which was implemented months ago, and only
>stopped working when we upgraded to 1.8.0.4) involves changing the
>datatype of a column which is in a table's primary key, so we have to
>first remove the PK and referencing FKs, add a new column, massage the
>data over to the new column, remove the old column, rename the new
>column, and then add the keys back in.
>
>And since we didn't explicitly name our PKs, we use the information
>schema to detect the PK and FK names during the upgrade procedure.
>
>Thanks for your help,
>Carl
>
>fredt wrote:
>
>  
>
>>This is obviously a bug. Have you tried to specify a PK name in your table
>>definition, so that it doe not begin with "SYS" ? e.g CREATE TABLE T( C 
>>CHAR
>>, D CHAR, CONSTRAINT MYPK PRIMARY KEY(C));
>>
>>Fred
>>
>>----- Original Message ----- 
>>From: "Carl Gould" <go...@in...>
>>To: <hsq...@li...>
>>Sent: 26 April 2006 20:59
>>Subject: [Hsqldb-user] Strange Primary key difference issue between 1.8.0.1
>>and 1.8.0.4
>>
>>
>>Hello,
>>
>>I have a database created in 1.8.0.1. When I issue the following query:
>>
>>SELECT * FROM information_schema.system_primarykeys ORDER BY table_name
>>
>>It tells me that the PK_NAME for a table of mine (called "projectprops")
>>is SYS_PK_94.
>>It then lets me drop that pk using:
>>
>>ALTER TABLE projectprops DROP CONSTRAINT SYS_PK_94
>>
>>If I were to open that same database in 1.8.0.4 (before the DROP) and
>>look at the system_primarykeys table,
>>it tells me that the PK_NAME for the same table is called SYS_IDX_104.
>>If I try to drop that - it tells me that "Constraint not found
>>SYS_IDX_104 in table: PROJECTPROPS in statement [...]"
>>Out of curiosity  I tried in 1.8.0.4 to drop SYS_PK_104 and SYS_PK_94,
>>neither of which existed.
>>
>>Any ideas? This is causing some automatic schema upgrade logic of ours
>>to fail.
>>
>>Carl Gould
>>
>>
>>
>>    
>>
>
>
>-------------------------------------------------------
>Using Tomcat but need to do more? Need to support web services, security?
>Get stuff done quickly with pre-integrated technology to make your job 
>easier
>Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>_______________________________________________
>Hsqldb-user mailing list
>Hsq...@li...
>https://lists.sourceforge.net/lists/listinfo/hsqldb-user 
>
>
>
>-------------------------------------------------------
>Using Tomcat but need to do more? Need to support web services, security?
>Get stuff done quickly with pre-integrated technology to make your job easier
>Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>_______________________________________________
>Hsqldb-user mailing list
>Hsq...@li...
>https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>  
>