Altering primary key in database

[former_member203861]

Hi all ,

I have some tables in my DataBase ( Sql Anywhere 11 ) , which i want to modify a column from smallint to an Integer .

Usually i do that by letting the user runs ( or doing it auto as the application opens )

execute immediate command.

the problem is the column the primarykey in the table .

There is a way doing that in run time ?

( the application runs over 400 users , each one with his database )

Thanks

Moshe

[Former Member · Accepted solution]

You'll need to do it like this:

- Drop Primary Key

- Alter column type

- Add Primary Key

These can all be done with execute immediate, and SA11 will let you check the col type from the sys.syscolumns. You'll need to know the original primary key. This can only be done if no-one else is using the database. Startup code would be an ideal place for it instead of an on demand button somewhere.

Example:

string ls_coltype, ls_exec

select sys.syscolumns.coltype into :ls_coltype from sys.syscolumns where cname='PKeyColName';

if ls_coltype="smallint" then

     ls_exec = 'alter table "TableNameHere" drop primary key'

     execute immediate :ls_exec;

     ls_exec = 'alter table "TableNameHere" modify '"KeyColName" integer'

     execute immediate :ls_exec;   

     ls_exec = 'alter table "TableNameHere" add primary key ("PKeyColName", .....)'

     execute immediate :ls_exec;

end if

Add error checking like you normally would.

[former_member203861 · Accepted solution]

Hi Brad ,

Thanks , working great

Moshe