October 10, 2012 at 9:35 am
Hi,
I was playing with alter table. When i come across this suituation. I tried to figure it out but . Well let me tell you what had i done.
First i added a new column as :
Alter Table Demo ADD col1 Int NOT NULL DEFAULT 1
Then i added a constraint on this column as
Alter Table Demo Add Constraint PK_demo PrimaryKey(col1,col2)
Then i played with the data and decided to drop this constraint and bring the table to its original structure. So i proceeded with. I first thought of drooping the constraint which i had created which i did sucessfully as
Alter Table Demo Drop Constraint PK_Demo
After that i though of dropping the added column as
Alter table Demo drop column col1
but :doze: i got the error which is mention in the subject line.
October 10, 2012 at 9:44 am
the part of your script here:
DEFAULT 1
Alter Table Demo ADD col1 Int NOT NULL DEFAULT 1
creates a default constraint ont eh column...so you can't drop teh column until you drop teh constraint on that column.
sp-help demo will show you all the constraints, and then you can do an explicit drop like this:
ALTER TABLE demo DROP CONSTRAINT DF__DEMO__2F126280
to make it easier on yourself, you can add the default constraint with a specific name :
Alter Table Demo ADD col1 Int NOT NULL
ALTER TABLE Demo ADD CONSTRIANT DF_Demo_col1_default_value DEFAULT(1) FOR col1
Lowell
October 10, 2012 at 9:38 pm
Thanks Lowell........
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy