how to change primary key field to int?

[albertkhor]

i want to change my table field from primary key to int, how to do that?
is that using ALTER TABLE plus ALTER COLUMN?

 

[kulrom]

1st off, the primary key is not datatype but rather the primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server).

However you can try to change datatype as it follows but i am not sure about your idea (maybe you suppose to explain it) as If you don't have a key, you don't have a table. it requires every row to be uniquely identified by its primary key. Anyway try with this statement:

ALTER TABLE myTable ALTER [COLUMN] myField TYPE int

 

[albertkhor]

1st off, the primary key is not datatype but rather the primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server).

However you can try to change datatype as it follows but i am not sure about your idea (maybe you suppose to explain it) as If you don't have a key, you don't have a table. it requires every row to be uniquely identified by its primary key. Anyway try with this statement:

ALTER TABLE myTable ALTER [COLUMN] myField TYPE int

i have succesful change the AutoNumber to int but how to change back?
Below code is how i change the AutoNumber to int:
ALTER TABLE tblName ALTER COLUMN Name_ID int

i try below code but cannot work, syntax error in alter table:
ALTER TABLE tblName ALTER COLUMN Name_ID int IDETITY(50,1) PRIMARY KEY

 

[kulrom]

Are you using MSAccess as backend?

 

[albertkhor]

Are you using MSAccess as backend?

Yes you are right!

 

[albertkhor]

Kulrom,

look like after i change the AutoNumber to int after insert data i cannot change back to AutoNumber...

if i never change the field type, can i change the AutoNumber start from other number? e.g. in new database i set the AutoNumber start from 0, after 2 record insert now the AutoNumber become 2, can i change the AutoNumber start from 10? So is another new data insert the number will be start from 10 continue with 11,12,...
is that possible?

 

[kulrom]

Ups sorry i din't see the forum's name .. it is obviously that you are using MSAccess. Ok, i am not sure will this work for MSAccess file but this is how it suppose to be done for SQL Server.
Means, to add Primary Key to certain column you can use this statement:

ALTER TABLE tblName ADD PRIMARY KEY (Name_ID )

Just be aware that you need to make sure that certain field is defined as 'NOT NULL' actually in Access you don't have to worry about that.

Regards

 

[kulrom]

Hey at the time you posted ones again i was already replying ... however it is so confusing what are you trying to do with all these stuff. Can you explain briefly please ?

 

[albertkhor]

Kulrom thx for ur help~ i already fix the problem by using below code:
ALTER TABLE tblName ALTER COLUMN fieldName IDENTITY IDENTITY(999,1)

it can work nicely but i have to make sure the number i set to start is the bigger number in the table.

i do this is because i develop a system which can sync database between PC (use .sdf) and PDA (use .mdb). Both also have their own Primary Key (for PDA i start from 0, PC i start from 268435455). So when sync i know which data is create by which machine. Because of my system allow user to do restore, the AutoNumber may duplicate. So if user restore the database either PC and PDA, when sync, user will ask to select which database he/she want to refer. If choose PDA then PC database will delete and PDA will pass all the data to PC (by using TCP/IP). If system cannot found the database it will create new database and run the script i already set, so the Key is start from 268435455. Sound like no problem, but my system have 1 level link. Each data will carry the link key (which is Autonumber for the selected data). So the only way i found out is change my Autonumber to int first, so all the data from PDA will send to PC, after complete sync i change back the int to Autonumber.

Is hard to explain to u, bad explaination + poor english . Anywhere hope you can know what i want to tell u.

If you know what is my problem and have best way to soft my problem pls tell me . The hard thing how to let my database working without any error, because of my database using AutoNumber, system allow to restore, database need to sync, first level link.