Action field in sysconstraints
-
Hi,
We are trying to find out how to identify / manipulate the 'CASCADE' property of a foreign key.
When checking the sysconstraints table, we found the 'action' field is set to 4096. BOL gives the all-meaning word 'reserved' as a comment.
Is there anyone who could tell us what field in the system tables determines the action a foreign key will execute on DELETE and on UPDATE? Is it stored in som specific and updatable field.
Would be of great help^
-
Have a look at OBJECTPROPERTY. There are two that might be interesting for you :
- CnstIsDeleteCascade
- CnstIsUpdateCascade
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks, Frank. We just foud these.
However, they don't allow SETTING the property, do they.
I know it is dangerous to mess with the systables, nonetheless.
Think we'll stick with dropping and creating the constraint, as we do now...
Thanks for you prompt help!!!
-
What are you doing actually? Dropping and recreating the triggers to change their fonctionnality or change their code while in production?
-
Actually, we are setting up test cases, which combine data having concurrent ids. Therefor, we need to reset ids for part of the data. This means we need to 'cascade update' over multiple foreign keys (often up to 40 to 50). We now have a procedure (used for releases) that, when necessary, drops all FKs from a table, after backing up the statements to create them. Then, the table data is backed up, table is dropped and recreated (with an additional field or the like) and the data is reloaded into the new table (adding default 0 or '' to the unrecognized fields). Finally, the foreign keys are restored.
We now amenden these procs so they could backup the create statement with the casecade property, then drop and recreate the key.
We were looking for a smoother way, directly affecting the sysconstraints table (field action), yet this seems hard, and undocumented...)
-
Have you read this: http://qa.sqlservercentral.com/articles/articlelink.asp?articleid=1885 Gert makes several interesting remarks on the system tables in the next version.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>We were looking for a smoother way, directly affecting the sysconstraints table (field action), yet this seems hard, and undocumented...)
As Frank and Remi says, this is for a good reason. These properties are probably stored as a bit flag inside some status mask, and working with them directly would be 1) very error prone and 2) not very smart, since it is explicitly advised against. As Gert says in the interview that Frank links to, in SQL Server 2005 you will not be able to modify the system tables (which are now actually read-only system views) in any way.
How about using DMO as a smoother way? I did not completely understand your requirements so I am not sure if it is better, but it does sound a little as though this could possibly be handled outside the server in a client app.
-
Well, maybe, but for us this is not an option, as our process is a consecutive chain of stored procedures, which are 'stand-alone'.
I think the way we deal with it now might turn out to be the smoothest way for us;-)
Especially if, as you say, you can't affect the system tables any more in SQL 2005...
Thanks for the help!
RODEO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply