Altering the table
Posted on 2006-04-13
Need help with alter table
I am trying to change the data type for one of the table in my table called "GroupDetails"
I have two columns 1) GroupNumber Int 2) Group SamllInt
and Nullable - "NO"
Since it has got constraint here is what I am doing.
1) Altering the table by droping the constraint
ALTER TABLE GroupDetails
DROP CONSTRAINT pk_GroupDetails
2) Altering the table to change the data type of one column (GroupNumber) to varchat(3)
3) I am creating the constraint again which I have dropped by step1 and I am using the following script and I
am getting the error
ALTER TABLE [dbo].["GroupDetails"] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
) WITH FILLFACTOR = 90 ON [PRIMARY]
IF @@ERROR <> 0
SELECT 'ERROR ALTERING THE TABLE. ROLLING BACK THE TRANSACTION'
Here is the error message I am getting
Server: Msg 8111, Level 16, State 1, Line 37
Cannot define PRIMARY KEY constraint on nullable column in table 'GroupDetails'.
And I have got confused by this message, so I ran "sp_help GroupDetails"
Here are the details
Index Name : pk_GroupDetails
Index Description : clustered, unique, primary key located on PRIMARY
Index Keys : GroupNumber, Group
Constraint_Type : PRIMARY KEY (clustered)
Constraint Name : pk_GroupDetails
ConstraintKeys : GroupNumber, Group
Please help me out by creating the constraint or index with the same name for my table.
Have a good day