Finding Foreign Keys Configured with Cascade Functionality
-
I have just stumbled across the fact that some--not all--of the tables in the various databases here are configured with foreign keys with cascading updates and/or deletes. Is there a quick way of finding out the full extent of the problem -- i.e, which FKs have what if any cascades and which don't?
(I figure it's something to do with bitmasks in the sysObjects table, but I'm not even sure how to phrase the question so as to get a meaningful answer out of Google, let alone MSDN.)
Philip
-
The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view supplies this information.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Ideal. Thanks! (I know I should, but I rarely ever think about--let alone use--the INFORMATION_SCHEMA objects.)
Philip
-
That's often the easy way out... unless you like to search for a few hours to find the relevant information
. -
I recently put together a script to generate rebuilding all the foreign keys for a database. I will explore using the INFORMATION_SCHEMA objects to do same if I can. Here is the script that I have now:
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Adds for All Foreign Keys in Database
print '-- Add Foreign Keys'
print ''
DECLARE @fkName varchar(800), @tabName varchar(800), @refName varchar(800)
DECLARE @stat int, @fkCol varchar(8000), @refCol varchar(8000)
DECLARE @pline varchar(8000)
DECLARE fkCursor CURSOR FOR
select distinct object_name(constid), object_name(fkeyid),
object_name(rkeyid), status
from sysforeignkeys k
JOIN sysobjects c ON k.constid = c.id
order by object_name(fkeyid)
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @stat
WHILE @@FETCH_STATUS = 0
BEGIN
select @fkCol = NULL
SELECT @fkCol = ISNULL(@fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @refCol = NULL
SELECT @refCol = ISNULL(@refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @pline = 'ALTER TABLE [dbo].[' + @tabName +
'] ADD CONSTRAINT [' + @fkName + ']' +
CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @fkCol + ') REFERENCES [dbo].[' + @refName +
'] (' + @refCol + ')'
if @stat & 4096 = 4096
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @stat & 8192 = 8192
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
select @pline = @pline + CHAR(13) + CHAR(10) + 'go'
print @pline
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @stat
END
CLOSE fkCursor
DEALLOCATE fkCursor
GO
-
For the purpose of future functionality I would use the ObjectProperty function with the CnstIsUpdateCascade or CnstIsDeleteCascade argument to determine cascade properties.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
dcpeterson,
Thanks for the tip about ObjectProperty for Cascade Update & Delete. It really cleans up the code. Much better than using bitwise operator.
Steve
-
I only needed to check over what was in there (for now, at least), and ended up with the following query to list what, where how, and so forth. (I never new objectProperty even existed. BOL needs more "See Alos" links!)
Philip
SELECT
soC.Name ChildTable
,soF.Name ForeignKey
,sfk.keyno fkOrder
,scC.Name ChildColumn
,soP.Name ParentTable
,scP.Name ParentColumn
,case objectProperty(sfk.constid, 'CnstIsDeleteCascade') when 1 then 'YES' else '' end CascadingDelete
,case objectProperty(sfk.constid, 'CnstIsUpdateCascade') when 1 then 'YES' else '' end CascadingUpdate
,case objectProperty(sfk.constid, 'CnstIsDisabled') when 1 then 'YES' else '' end Disabled
,case objectProperty(sfk.constid, 'CnstIsNotTrusted') when 1 then 'YES' else '' end BuiltWithNocheck
from sysForeignKeys sfk
inner join sysObjects soC -- Child
on soC.id = sfk.fkeyid
inner join sysColumns scC
on scC.id = soC.id
and scC.colid = sfk.fkey
inner join sysObjects soP -- Parent
on soP.id = sfk.rkeyid
inner join sysColumns scP
on scP.id = soP.id
and scP.colid = sfk.rkey
inner join sysObjects soF
on soF.id = sfk.constid
order by ChildTable, ForeignKey, ChildColumn
-
I never (k)new objectProperty even existed. BOL needs more "See Alos" links!
There is ServerProperty and DatabaseProperty as well. I think the SQL Server community as a whole is far too used to querying directly from the system tables so these functions don't get used much.
Microsoft is mostly to blame for this situation because they failed to prevent it and in turn failed to provide system functions that were robust enough to eliminate the need. If you are one of those folks whose first thought is to snoop through the system tables, 2005 will begin to change that... I suspect it will be a rude awakening for some!
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Just to mention, I started off following the MS dictum to "never use the system tables". I did a lot through the various sp_help... and similar procedures (INSERT...EXECUTE...), but generally ignored the informationschma objects because the table and column names did not correlate with anything in BOL. As you might think, administrative code and routines dependent on these routines gets convoluted very quickly (and is not helped by the assorted bugs and "features" lurking in some of those routines!)
Like many others, I started using the system tables when I needed to accomplish some very critical work and the available built-in routines didn't and couldn't address [such as identifying blocking connections holding resources for greater than 3 minutes and sending email once when first detected.] It's far too hard to go back, so I guess I'm doomed, at least as SQL 2000 (and 7.0) are concerned.
From what little I've seen of 2005, it looks like they've fixed the situation by first locking away the system tables (or their 2005 analog), and then exposing everything you might possible want (or so I hope!) via various system views and functions. A whole new learning curve to tackle if and when I get such a system, but it should be cool.
(Thought: how do the various third-party software applications do it? Direct system table access, or otherwise? ...but then, they've got partnerships going with MS, and so probably get advance warning on system table changes.)
Philip
-
My comments weren't meant as a direct criticism of you or anyone else, except Microsoft. I have been far too dependent on direct access to the system tables just like you and many others. It has been out of necessity because like you mentioned MS said "don't do it" but they failed to provide a set of system functions that was robust enough to eliminate the need. The information_schema views were a feeble effort in that direction. They are neither comprehensive nor consistent.
2005 has addressed this issue, but I for one, have not played with it enough to know if they really solved the problem or merely cut off access. I'm referring specifically to system table updateability here.
As an aside, the system tables in 7 and 2000 betray some shockingly poor database design and as a result are far too difficult to query.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I was just comparing notes and lambasting Microsoft some. And if one of the brave few who points out just what emperor XML is actually wearing wants to criticise me, I'll listen!
Philip
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply