Change TinyInt identity into SmallInt

Hi,
I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
change the column to a SmallInt.
I tried this:
drop index behaviorprop.PK_BehaviorProp
alter table behaviorprop alter column BP_ID smallint
but I get
An explicit DROP INDEX is not allowed on index
'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
constraint enforcement.
How can I remove the primary key constraint and add it again after the
change?
Thanks in advance,
Stijn Verrept.You need to drop the constraint instead...
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Hi
create table test (c1 tinyint identity(1,1) not null)
insert into test default values
alter table test alter column c1 int
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Tony Rogerson wrote:

> You need to drop the constraint instead...
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key
> ( bp_id )
> commit tran
Thanks for your reply, but then I get:
'PK_Behaviourprop' is not a constraint.
and when I look in enterprise manager at constraints there aren't any
there, only the primary key (in the indexes tab).
Kind regards,
Stijn Verrept.|||Oh, I did not care about PRIMARY KEY constraint , see Tony's answer
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ucjy$rMeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Hi
> create table test (c1 tinyint identity(1,1) not null)
> insert into test default values
> alter table test alter column c1 int
>
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
>|||Need your version, use PRINT @.@.VERSION, the CREATE TABLE script including
indexes and any constraints.
And, the exact error message (cut and paste).
many thanks
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:SI-dnbH15pMVBvTZRVnyvQ@.scarlet.biz...
> Tony Rogerson wrote:
>
> Thanks for your reply, but then I get:
> 'PK_Behaviourprop' is not a constraint.
> and when I look in enterprise manager at constraints there aren't any
> there, only the primary key (in the indexes tab).
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Need your version, use PRINT @.@.VERSION, the CREATE TABLE script
> including indexes and any constraints.
> And, the exact error message (cut and paste).
> many thanks
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Error message:
Server: Msg 3728, Level 16, State 1, Line 2
'PK_Behaviourprop' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors.
The create table script is at: http://www.entrysoft.com/script.sql it
will create temptest database and will display the error when you try
to run
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
It must be something simple.
Thanks for taking the time!
Kind regards,
Stijn Verrept.|||Not really answering the question but this script will work.... Good (new)
sql 2005 management studio and the create change script option on modify
table ;)
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Users
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Seniors
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BehaviorProp
(
BP_ID smallint NOT NULL IDENTITY (1, 1),
BP_SNID int NOT NULL,
BP_Stamp smalldatetime NOT NULL,
BP_Score1 tinyint NOT NULL,
BP_Score2 tinyint NOT NULL,
BP_Score3 tinyint NOT NULL,
BP_Score4 tinyint NOT NULL,
BP_Score5 tinyint NOT NULL,
BP_Score6 tinyint NOT NULL,
BP_Desc1 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc2 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc3 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc4 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc5 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc6 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_USID smallint NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp ON
GO
IF EXISTS(SELECT * FROM dbo.BehaviorProp)
EXEC('INSERT INTO dbo.Tmp_BehaviorProp (BP_ID, BP_SNID, BP_Stamp,
BP_Score1, BP_Score2, BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1,
BP_Desc2, BP_Desc3, BP_Desc4, BP_Desc5, BP_Desc6, BP_USID)
SELECT CONVERT(smallint, BP_ID), BP_SNID, BP_Stamp, BP_Score1, BP_Score2,
BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1, BP_Desc2, BP_Desc3,
BP_Desc4, BP_Desc5, BP_Desc6, BP_USID FROM dbo.BehaviorProp WITH (HOLDLOCK
TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp OFF
GO
DROP TABLE dbo.BehaviorProp
GO
EXECUTE sp_rename N'dbo.Tmp_BehaviorProp', N'BehaviorProp', 'OBJECT'
GO
ALTER TABLE dbo.BehaviorProp ADD CONSTRAINT
PK_BehaviorProp PRIMARY KEY CLUSTERED
(
BP_ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Seniors FOREIGN KEY
(
BP_SNID
) REFERENCES dbo.Seniors
(
SN_ID
) ON DELETE CASCADE
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Users FOREIGN KEY
(
BP_USID
) REFERENCES dbo.Users
(
US_ID
) ON DELETE CASCADE
GO
COMMIT
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:Ke-dnSqDzbrrO_TZRVny2w@.scarlet.biz...
> Tony Rogerson wrote:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> Error message:
> Server: Msg 3728, Level 16, State 1, Line 2
> 'PK_Behaviourprop' is not a constraint.
> Server: Msg 3727, Level 16, State 1, Line 2
> Could not drop constraint. See previous errors.
> The create table script is at: http://www.entrysoft.com/script.sql it
> will create temptest database and will display the error when you try
> to run
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key (
> bp_id )
> commit tran
>
> It must be something simple.
> Thanks for taking the time!
>
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Not really answering the question but this script will work.... Good
> (new) sql 2005 management studio and the create change script option
> on modify table ;)
This works indeed! Thanks!
Kind regards,
Stijn Verrept.