Drop Primary Key

[Stefan Marhauer]

Hy group,

i'm developing a compact framework application with VS2003 and using SQL
Server CE2.0

Now i have to change some database structure in my running application.
First i have used a field "fldID" in my table as "int IDENTITY (0,1)" ->auto
increment field. This field is declared as primary key in my table.
Now I have to change the primary key field to a ROWGIUD column. I've added a
new column using uniqueidentifier and ROWGUIDCOL. This also works fine. Now
i want to drop the primary key from the "fldID"-column to set a new primary
key on the GUID-Column.
As described in the documentation of SQLServerCE i used the command
"ALTER TABLE table_name DROP PRIMARY KEY"
But this don't work it always throws an exception "There was an error
parsing the query" and unknown keyword says: "primary"

Hope you could help me. I don't want to copy all data into a new table. :-(

regards
Stefan

 

[Graham McKechnie]

Stefan,

CE can be picky re syntax - check CE books online Alter Table - I think you
may need the key word CONSTRAINT.

Graham

 

[Stefan Marhauer]

Hy Graham,

i've used the CE Books Online Documentation.

I also tried the CONSTRAINT. But i think there it needs the constraint_name
which i don't have. I've created the Primary Key using statemant:
"CREATE TABLE table_name (fldID int IDENTITY (0,1) PRIMARY KEY,
nextcol.....)
If I now use it including for ex. the column_name "ALTER TABLE DROP
CONSTRAINT fldID" it says error 25060 (reference not found). So I think it
needs the name of the contraint.

Stefan

 

[Stefan Marhauer]

Hy again,

seems not to be realized as dsecribed in the documentation but I've now used
another solution.

First get the name of the constraint out of the system table MSysConstraints
by TABLE_NAME and COLUMN_NAME.

And then use "ALTER TABLE table_name DROP CONTRAINT const_name".

This works fine. A little bit complicated, but the work is done. ;-)

greets
Stefan

 

[Graham McKechnie]

Stefan,

Glad you got it - I'll remember that one.

Graham