How can I change PRIMARY KEY using code?

Hi,
Assume I have a PRIMARY KEY on one column.
How can I change PRIMARY KEY onto a new column using code?
Do I have to recreate the table?
Thanks.Hi
Here is the code to solve your problem
ALTER TABLE <TABLE_NAME>
DROP CONSTRAINT <Constraint_Name>
ALTER TABLE <TABLE_NAME>
ADD CONSTRAINT <Constraint_Name> PRIMARY KEY (<Column> )
best Regards,
Chandra
---
"§Chrissi§" wrote:

> Hi,
> Assume I have a PRIMARY KEY on one column.
> How can I change PRIMARY KEY onto a new column using code?
> Do I have to recreate the table?
> Thanks

How can I change my Primary Key

Hello,
I have transactional replication in place with @. 450 articles in the main
publication.
I need to modify the primary key to include an additional field.
How can I do this best?
Thanks.
~Lb
you can't without dropping your subscriptions and publications.
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:OoJcCdAMFHA.1096@.tk2msftngp13.phx.gbl...
> Hello,
> I have transactional replication in place with @. 450 articles in the main
> publication.
> I need to modify the primary key to include an additional field.
> How can I do this best?
> Thanks.
> ~Lb
>
|||Thank you...
Can I remove the table from the publication easily and then make the
changes... and finally create a new publication with just this table?
|||yes with transactional replication you can do with by using sp_droparticle.
You will have to drop the subscription(s) before you do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:%23c3blpAMFHA.3632@.TK2MSFTNGP10.phx.gbl...
> Thank you...
> Can I remove the table from the publication easily and then make the
> changes... and finally create a new publication with just this table?
>
sql

How can I automate the column titled as ID NUmber in my database, in VS 2005?

I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type

*i am using VS 2005 and VB language

Go into design view for the table

toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True

|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||

Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.

Maybe someone else can chime in here, how to do it some other way

|||

To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.

|||

i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?

|||

You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.

|||Remember, also - tinyint is defined like this:

tinyint

0 to 255

So - I'd recommend at least using INT

One last thing also - you said that your column is titled "ID Number"

My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.