Can I change the data type of my primary key on a temporal table?

Question

In SQL Server 2016 Express, I initially made a table with a primary key that used the int datatype. Now, further into the project, I've realized that I need this column to be a string datatype, but when I try this:

alter table ExpAwbs
drop constraint PK_ExpAwbs;

alter table ExpAwbs
alter column idExpAwbs varchar(12);

alter table ExpAwbs
add constraint PK_ExpAwbs primary key (idExpAwbs);

I get this

Msg 4902, Level 16, State 1, Line 1
Cannot find the object "ExpAwbs" because it does not exist or you do not have permissions.

Is it possible to do what I'm trying to do on a temporal table, or do I need to remake the table?

Are you in the correct database where that table resides? – SS_DBA May 08 '17 at 18:58 — SS_DBA, May 08 '17 at 18:58

Ilyes · Accepted Answer · 2017-05-08T19:13:30.603

4

Try this :

Use YourDatabaseName
Go

alter table ExpAwbs
drop constraint PK_ExpAwbs;

alter table ExpAwbs
alter column idExpAwbs varchar(12);

alter table ExpAwbs
add constraint PK_ExpAwbs primary key (idExpAwbs);

If this not working that mean that the user do not have permissions, So you have to change the user or Grant the permission for that user.

edited May 08 '17 at 19:13

answered May 08 '17 at 19:07

Ilyes

14,640
4
29
55

That's odd. This worked, but the database server only has one user, and that's the admin user. But, I got a different error now that I cannot drop the primary key constraint from a system-versioned temporal table. I assume this means that what I'm trying to do can't be done. – Josh Eblin May 08 '17 at 19:30
Actually, turning of system versioning allowed me to drop the primary key constraint. – Josh Eblin May 08 '17 at 21:14

score 0 · Answer 2 · answered May 08 '17 at 19:07

As @wei_dba mentioned, it most certainly seems to be a permission issue. To prove that, I recreated your table, added some data to it and tried it - it worked. If you're unable to do so, clearly the issue lies within the area of permissions; you may need to have someone run that query for you.
While reviewing your query, I see another issue with a quick fix. You may want to replace the following:

alter table ExpAwbs  
alter column idExpAwbs varchar(12);

with

alter table ExpAwbs  
alter column idExpAwbs varchar(12) not null;

Reasoning being that you need to make sure that the PK is non-nullable, something which you haven't put into your query.

Can I change the data type of my primary key on a temporal table?

2 Answers2