Change SQL Server primary key using C#

Question

I need to change a column type in a SQL Server table.

I need do it using a script or C#.

The original datatype is integer and the new type is varchar(50).

I can do it using this SQL script:

ALTER TABLE confezionamento ALTER COLUMN partnumber varchar(50)

but the problem is that this column is in a set of primary keys.

When I try do execute, an error occurred because PK_CONFEZIONAMENTO is using the column.

How I can do it without access to SQL Server Management Studio?

you should remove the c# keywords from this question, as this is sql server related only — Michael Sander
– Michael Sander, Commented Apr 25, 2015 at 18:25
While I very much agree with marc_s, I have worked with many projects where the PK is a string rather than utilizing a UNIQUE constraint on the field (in this case part number) and using an INT PK for foreign keys with much faster queries. These decisions for whatever reason often come down from people who don't understand databases. — Martin Noreke
– Martin Noreke, Commented Apr 25, 2015 at 18:27

Martin Noreke · Accepted Answer · 2015-04-25 18:23:33Z

4

You will need to run SQL Commands to do the following steps:

Drop primary key constraint
Alter primary key
Add primary key constraint

If there are any foreign keys or indexes, you may have to deal with them as well.

The commands you are looking for will be similar to these:

Alter table confezionamento drop constraint PK_CONFEZIONAMENTO
ALTER TABLE confezionamento ALTER COLUMN partnumber varchar(50) 
ALTER TABLE confezionamento ADD CONSTRAINT PK_CONFEZIONAMENTO PRIMARY KEY (partnumber)

These commands can all be sent from C# using ADO.Net or similar methods.

answered Apr 25, 2015 at 18:23

Martin Noreke

4,1661 gold badge24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

... and to drop the primary key constraint, you first need to find and drop all referencing foreign key constraints, too.

Collectives™ on Stack Overflow

Change SQL Server primary key using C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related