March 17, 2010 at 4:41 am
Hi ,
i am getting problem while executing this query
alter table AAA
alter column ID int constraint FK_ABABA references BBB(ID) on delete cascade
please can any one help me ,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near constraint'
March 17, 2010 at 5:08 am
I think that you need to drop the old constraint and add the new constraint with the cascading delete:
alter table AAA drop constraint OldConstraintName
alter table AAA add constraint FK_ABABA foreign key (id) references BBB(ID) on delete cascade
[\code]
I have to admit that personally I don’t like cascade delete. You can try deleting one wrong record and ending up deleting half the database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 17, 2010 at 5:24 am
Thanks for your reply ,
actually My question is:
I have a database with over 200 tables, but without DELETE CASCADE. Is it possible to add DELETE CASCADE to all foreign-key definitions throught the database (without having to open each FK and ...
March 17, 2010 at 5:53 am
kishore_gdk 24670 (3/17/2010)
actually My question is:I have a database with over 200 tables, but without DELETE CASCADE. Is it possible to add DELETE CASCADE to all foreign-key definitions throught the database (without having to open each FK and ...
Nope.
March 17, 2010 at 5:57 am
You could just generate a script to do it for you !
I would advise to incorporate a transaction scope per FK-constraint so you can drop it and add the new definition using the "with nocheck" option to speed things up.
I must have a sample somewhere over here. I'll add it to my post later on.
I've attached the sproc I used in SQL2000.
As always ....
TEST IT - TEST IT - TEST IT
How I used it (SQL2K) :
Install the sproc in the userddb.
Generate the exec commands as documented in the first lines of the sproc.
Execute the results (which actually run the sproc that will generate the statements to drop and re-create the fk constraints.)
There is an option @FKIndexes. If = 'Y' it will also create indexes that match the FK definition !
When using cascading deletes, you should double check your tables have the needed FK indexes !
As always ....
TEST IT - TEST IT - TEST IT
Play it safe and start with a FULL backup of your database !
Keep in mind I don't advise to use with nocheck outside of a transaction scope !!
Have your FK checked using:
DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query runs
Check them BEFORE your operation and afterward.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2010 at 6:29 am
ALZDBA (3/17/2010)
...add the new definition using the "with nocheck" option to speed things up.
But then the constraint will be marked as untrusted, and will not be considered by the optimizer.
The chances of generating a script that does everything in exactly the right order, avoids any circular references, and handles things like timestamp columns being present in the key...well. It is a good starting point, however.
Adding a deleting cascade unconditionally to all FK relationships does not strike me as the most awesome idea ever, in any case.
March 17, 2010 at 7:13 am
Paul White (3/17/2010)
ALZDBA (3/17/2010)
...add the new definition using the "with nocheck" option to speed things up.But then the constraint will be marked as untrusted, and will not be considered by the optimizer.
The chances of generating a script that does everything in exactly the right order, avoids any circular references, and handles things like timestamp columns being present in the key...well. It is a good starting point, however.
Adding a deleting cascade unconditionally to all FK relationships does not strike me as the most awesome idea ever, in any case.
Wouldn't the " DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS " get rid of the untrusted marker ?
I must admit we use FK but don't use cascading deletes just because of the impact !
You just cannot predict its effect on larger volumes or large parent - child - grand child - ... topologies at delete time.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2010 at 7:17 am
ALZDBA (3/17/2010)
Wouldn't the " DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS " get rid of the untrusted marker?
Certainly would. Of course, you would need a second script to loop around all the tables affected. Besides, if you are going to check the constraints anyway...well, you see where I am going with that!
I must admit we use FK but don't use cascading deletes just because of the impact! You just cannot predict its effect on larger volumes or large parent - child - grand child - ... topologies at delete time.
Agreed. They have their place, for sure, but I wouldn't go mad with them.
Paul
May 19, 2010 at 11:23 am
I personally don't recommend people enforcing Cascade Delete thru Constraints, unless you 100% sure you always want to clean up the children records when a DELETE is issued on parent records:
Here is the code that allows you do go thru the tree to do Cascade Delete, you only need to use it when you on purposely want to delete a tree:
http://bdmas.forumotion.com/code-and-scripts-f46/cascaded-delete-in-sql-server-t6.htm#7
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply