How do I find out whether a PK is referenced by any FK?

I need to perform regular maintenance on a table who is referenced through F
K from many
other tables.
Instead of attempting to bluntly delete the rows and let SQL report RI error
s , I would
like to first check whether the row is currently being referenced without ha
ving to check
each referencing table.
How can I accomplish that?
thanks.Do you mean whether an FK exists at all, or whether any relevant data exists
in the FK tables? Note that if the FK is defined as nullable then the mere
presence of rows in the FK table does not mean that those rows depend on any
rows in the PK table.
"Gaetan" <someone@.somewhere.com> wrote in message
news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>I need to perform regular maintenance on a table who is referenced through
>FK from many
> other tables.
> Instead of attempting to bluntly delete the rows and let SQL report RI
> errors , I would
> like to first check whether the row is currently being referenced without
> having to check
> each referencing table.
> How can I accomplish that?
> thanks.|||The FK constraints are defined in the other tables. I want to determine whet
her any
relevant data exists in the referencing tables. NULL values are not allowed
in the FK
values.
On Tue, 8 Nov 2005 12:31:39 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Do you mean whether an FK exists at all, or whether any relevant data exist
s
>in the FK tables? Note that if the FK is defined as nullable then the mere
>presence of rows in the FK table does not mean that those rows depend on an
y
>rows in the PK table.
>
>
>"Gaetan" <someone@.somewhere.com> wrote in message
> news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>|||Hello, Gaetan
If your primary key has is composed of a single column and the number
of referencing tables is not too big (less than 20...30 tables), you
can try to dynamically construct an SQL statement to check that tables,
based on the informations from the system tables (or the
information_schema views, if you preffer).
The code might be something like this:
DECLARE @.TableName sysname
DECLARE @.PKColumn sysname
DECLARE @.Condition nvarchar(100)
DECLARE @.SQL nvarchar(4000)
SET @.TableName = 'SomeTable'
SET @.Condition = 'SomeColumn=123'
SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
+'SELECT '''+ft.name+''' WHERE EXISTS ('
+'SELECT * FROM '+QUOTENAME(ft.name)
+' WHERE '+QUOTENAME(fc.name)+' IN ('
+'SELECT '+QUOTENAME(pc.name)
+' FROM '+QUOTENAME(@.TableName)
+ISNULL('WHERE '+@.Condition,'')+'))'
FROM sysreferences r
INNER JOIN sysobjects fk ON r.constid=fk.id
INNER JOIN sysobjects ft ON r.fkeyid=ft.id
INNER JOIN sysobjects pt ON r.rkeyid=pt.id
INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
WHERE fk.xtype='F' AND pt.name=@.TableName
PRINT @.SQL
EXEC(@.SQL)
IF @.@.ROWCOUNT>0 BEGIN
PRINT 'There are some rows in other tables referencing our row(s)'
END
Be carefull what you put in the @.Condition variable, because this code
is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
nvarchar string (4000 characters) may be easily exceeded if you have
many foreign keys for referencing your table.
Razvan|||On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:

>I need to perform regular maintenance on a table who is referenced through
FK from many
>other tables.
>Instead of attempting to bluntly delete the rows and let SQL report RI erro
rs , I would
>like to first check whether the row is currently being referenced without h
aving to check
>each referencing table.
>How can I accomplish that?
Hi Gaetan,
You can't. The only way to know if a row is referenced or not is to
check all referencing tables.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wonderful,
This is exactly what I was looking for. I will have less than 15 tables refe
rencing a main
table so there is no problem with the string size. Injection will not proble
m since there
will be no user input.
Thanks again Razvan.
On 8 Nov 2005 12:57:16 -0800, "Razvan Socol" <rsocol@.gmail.com> wrote:

>Hello, Gaetan
>If your primary key has is composed of a single column and the number
>of referencing tables is not too big (less than 20...30 tables), you
>can try to dynamically construct an SQL statement to check that tables,
>based on the informations from the system tables (or the
>information_schema views, if you preffer).
>The code might be something like this:
>DECLARE @.TableName sysname
>DECLARE @.PKColumn sysname
>DECLARE @.Condition nvarchar(100)
>DECLARE @.SQL nvarchar(4000)
>SET @.TableName = 'SomeTable'
>SET @.Condition = 'SomeColumn=123'
>SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
> +'SELECT '''+ft.name+''' WHERE EXISTS ('
> +'SELECT * FROM '+QUOTENAME(ft.name)
> +' WHERE '+QUOTENAME(fc.name)+' IN ('
> +'SELECT '+QUOTENAME(pc.name)
> +' FROM '+QUOTENAME(@.TableName)
> +ISNULL('WHERE '+@.Condition,'')+'))'
>FROM sysreferences r
>INNER JOIN sysobjects fk ON r.constid=fk.id
>INNER JOIN sysobjects ft ON r.fkeyid=ft.id
>INNER JOIN sysobjects pt ON r.rkeyid=pt.id
>INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
>INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
>WHERE fk.xtype='F' AND pt.name=@.TableName
>PRINT @.SQL
>EXEC(@.SQL)
>IF @.@.ROWCOUNT>0 BEGIN
> PRINT 'There are some rows in other tables referencing our row(s)'
>END
>Be carefull what you put in the @.Condition variable, because this code
>is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
>nvarchar string (4000 characters) may be easily exceeded if you have
>many foreign keys for referencing your table.
>Razvan|||Understood.
Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
row and let SQL report an error and handle it in the code or run SQL stateme
nt(s) to scan
each referencing tables!
Do you have an idea which approach is faster? The majority of the cases I wi
ll have less
than 5 referencing tables.
Regards, Gaetan.
On Tue, 08 Nov 2005 22:16:20 +0100, Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_
fo> wrote:

>On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:
>
>Hi Gaetan,
>You can't. The only way to know if a row is referenced or not is to
>check all referencing tables.
>Best, Hugo|||> Performance wise, I wonder whether if it is preferable to attempt to
> delete a referenced
> row and let SQL report an error and handle it in the code or run SQL
> statement(s) to scan
> each referencing tables!
> Do you have an idea which approach is faster? The majority of the cases I
> will have less
> than 5 referencing tables.
Well, what is the end game here?
I'm not sure I understand the purpose. If there are no referencing rows,
allow them to just delete everything from the PK table. If there are
referencing rows, then what?
A|||On Tue, 08 Nov 2005 21:48:01 GMT, Gaetan wrote:

>Understood.
>Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
>row and let SQL report an error and handle it in the code or run SQL statem
ent(s) to scan
>each referencing tables!
>Do you have an idea which approach is faster? The majority of the cases I w
ill have less
>than 5 referencing tables.
>Regards, Gaetan.
Hi Gaetan,
That depends.
If most cases will not be referenced, deleting without checking will be
fastest. If you do check, you're duplicating the work (since SQL Server
will have to re-run the check as part of the DELETE command).
On the other hand, if many cases will fail, checking first might be
faster. First, because you can control the order in which tables are
checked (making sure the table most likely to be the offender is checked
first). And second, because you eliminate the need for a ROLLBACK
operation (which is often slower than the original data manipulation).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Nov 2005 16:57:41 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Well, what is the end game here?
>I'm not sure I understand the purpose. If there are no referencing rows,
>allow them to just delete everything from the PK table. If there are
>referencing rows, then what?
>A
>
The goal is simple; I have a core table that need to be refreshed at some in
tervals. That
core table is refreshed (insert / update / delete) from data elements locate
d somewhere
else.
Inserts and updates of the core table are easy ... just do it. However, for
the deletes,
other tables may still be referencing rows of the core table. Performance is
not
imperative but since I have yet to write the code, I would rather do it the
way that makes
most sense without going heels over head, hence the original question; Shoul
d I just
delete the core table row and catch the RI errors or scan the referencing ta
bles and skip
the delete operation when a core table row is still being referenced. I must
not delete
the row when it is still referenced ... I have to wait until yet another pro
cess deletes
the rows from the referencing tables.
Gaetan.