May 23, 2007 at 12:39 pm
Can we drop a column from a table which is being referenced from another table(When cascade delete ON).
i do remember this can be done in ORACLE with following script:
alter table <<table> drop <column> cascade constraints;
I have tried in 2 ways in SQL Server.
1) When i tried to drop column using alter statement, it's throwing an error.
2)when drop the column from SQLEM(Right click table and select design), then it asks "There is relationship exists, do you want drop the column".
if we click on YES then it is droping column along with foreignkey constarint.
Can any one please tell me if there is way i can drop a column which is being referenced using Alter statement?
May 23, 2007 at 2:49 pm
You should be able to do it using something like this
ALTER TABLE yourtable DROP CONSTRAINT yourconstraint, COLUMN yourcolumn
you can also list any indexes you have this column tided up to.
If you want to see what the EM is doing start the SQL Profiler and look at the code that is sent to the Server when you perform the action you describe. You will see what and how Microsoft does when certain actions are taken from EM.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 23, 2007 at 3:08 pm
This way we are explicitly dropping FK constaraint before dropping the column..This is always possible. but if you see th example i have quoted in my first post, In oracle, we need not bother about the constraint name or to drop that, when cascade is ON. it would drops PK column along with the relation.
Anyway, Thanks for the post.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply