Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The object 'DF.....' is dependent on column 'COL1' . But How? Expand / Collapse
Author
Message
Posted Wednesday, October 10, 2012 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:48 AM
Points: 160, Visits: 334
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 i got the error which is mention in the subject line.
Post #1371011
Posted Wednesday, October 10, 2012 9:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605, Visits: 27,645
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1371014
Posted Wednesday, October 10, 2012 9:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:48 AM
Points: 160, Visits: 334
Thanks Lowell........
Post #1371193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse