The first obvious question is why is the primary key being created anyways if your just dropping it. Though its likely due to upgrading the database for 5.4 from an earlier version. If the goal is to drop the 'primary key constraint', the code should execute this instead:

ALTER TABLE activemq_acks drop constraint activemq_acks_pkey;

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

Thanks for the detailed log information.
Yes, the use case is auto upgrading an existing datastore.
Can you validate that the rest of the statements work on postgresql by making the changed to the xml configuration using a spring property update of the form. This can also provide a workaround.

Think we can pull out the alter table statements to have them easily overridden by the postgresql adapter.
{code}

Postgres adapter overrides the alter statement to resolve. new attribute on statements to expose the variability and acksPkName attribute on posgres adapter in case generated name of pk constraint changes.

@Kevin, do you know what a sensible default name for the PK in SQLServer is?
As there is no name provided when the constraint is created the name auto-generated by sql server, seems to be of the form "PK..."

You are right. "ACTIVEMQ_ACKS" has a composite primary key and the contraint has the form like this: PK__ACTIVEMQ_ACKS__6DC22B62. This primary key constraint is auto-generated by SQL Server and also everytime the contraint is different.
This script works "ALTER TABLE [dbo].[ACTIVEMQ_ACKS] DROP CONSTRAINT [PK__ACTIVEMQ_ACKS__6DC22B62]" and I have tried "ALTER TABLE [dbo].[ACTIVEMQ_ACKS] DROP CONSTRAINT [PK__ACTIVEMQ_ACKS]" but it doesn't work.

We could use "select name from sysobjects where xtype = 'PK' and parent_obj = object_id('ACTIVEMQ_ACKS')" to get the contraint name. It may need to use dynamic SQL to drop the primary key from SQL Server.

Could we define the primary key constraint name when creating ACTIVEMQ_ACKS? Then we could have a fixed constraint name and this name could be used in ALTER SQL.
For instance, when creating the table we use the following SQL:

We could have the unique CONSTRAINT name as PK_ACTIVEMQ_ACKS. Then we could drop the constraint like this:ALTER TABLE ACTIVEMQ_ACKS DROP CONSTRAINT PK_ACTIVEMQ_ACKS.
This solution works for SQL server and could work for Oracle as well. I am not sure if this could work for other DBs.

It seems for MySQL we should use this SQL to drop primary key.
ALTER TABLE ACTIVEMQ_ACKS DROP PRIMARY KEY

--Kevin

was (Author: kaiqinsun):
Hi Gary,

Could we define the primary key constraint name when creating ACTIVEMQ_ACKS? Then we could have a fixed constraint name and this name could be used in ALTER SQL.

I think we may have to go with the dynamic sql to find the pk, the problem is an upgrade from a schema where the pk name was not provided when the constraint was created. Adding the pk name is probably a good idea though for the future.