Primary Key Ref Count?

I was wondering, is there any way to get something like a reference count,
or even just an "InUse" yes/no kind of field, for primary keys?
For example, i have a MARKETING_TEXT table, which simply has an ID and TEXT
columns. ID is the primary key. What i want to be able to do is find out,
for a given ID, if there are any children records. But i dont want to do it
using a SELECT COUNT(1) method, because it is a table that is shared among a
bunch of child tables. CATS, PRODS, CAT_LINKS, etc. Plus if a new table gets
added, then it is a maintenance issue to make sure to add a SEL COUNT for
that one.
What i am thinking for, is to be able to determine PK usage before letting a
user do a delete, rather than having them try to delete, and coming back
with an "Unable to delete, FK reference........" error message from the
db.
Any ideas?
Thanks in advance,
Arthur Dent.There is no automatic method to pre-check foreign key violations. You
could code something in an INSTEAD OF trigger or a stored proc but that
would still require you to reference all the related tables. You
wouldn't have to use COUNT though. EXISTS is usually more efficient:
EXISTS
(SELECT *
FROM T1
WHERE ref = ...)
OR EXISTS
(SELECT *
FROM T2
WHERE ref = ...)
OR EXISTS
(SELECT *
FROM T3
WHERE ref = ...)
..
Why don't you just catch the error message and display a more
user-friendly warning in the app? That's potentially more efficient
than checking up-front and to the user it would surely appear the same.
Your client app ought to be able to do that.
In TSQL, although you can't suppress the message you can catch it and
then act accordingly - for example to return an error result code from
an SP.
David Portas
SQL Server MVP
--|||Arthur,
There is not an ease way to do what you want, better to let sql server to
check the existence of a reference to this id. Here is a simple script that
count the references to a single column (a foreign key could be a multi
columns one) and the id's value let us cast it to varchar. If the unique or
primary key being referenced is a multi columns one, then the script should
be modified. I am also inclueding a link where you can read about the risks
of using dynamic sql.
use northwind
go
create table #t (
r_tn sysname,
r_cn sysname,
r_value sql_variant,
f_tn sysname,
f_cn sysname,
cnt bigint
)
declare @.sql nvarchar(4000)
declare @.f_tn sysname
declare @.f_cn sysname
declare @.r_tn sysname
declare @.r_cn sysname
declare @.v sql_variant
set @.r_tn = 'employees'
set @.r_cn = 'employeeid'
set @.v = N'6'
declare my_cursor cursor local static read_only
for
select
object_name(fkeyid),
col_name(fkeyid, fkey1)
from
sysreferences
where
rkeyid = object_id(@.r_tn)
and col_name(rkeyid, rkey1) = @.r_cn
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.f_tn, @.f_cn
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'select ''' + @.r_tn + N''', ''' + @.r_cn + N''', ''' +
convert(varchar(128), @.v) + N''', ''' + @.f_tn + N''', ''' + @.f_cn + N''',
count(*) from ' + quotename(@.f_tn) + N' where ' + quotename(@.f_cn) + N' = ''
'
+ convert(varchar(128), @.v) + N''''
insert into #t
execute sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t
drop table #t
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Arthur Dent" wrote:

> I was wondering, is there any way to get something like a reference count,
> or even just an "InUse" yes/no kind of field, for primary keys?
> For example, i have a MARKETING_TEXT table, which simply has an ID and TEX
T
> columns. ID is the primary key. What i want to be able to do is find out,
> for a given ID, if there are any children records. But i dont want to do i
t
> using a SELECT COUNT(1) method, because it is a table that is shared among
a
> bunch of child tables. CATS, PRODS, CAT_LINKS, etc. Plus if a new table ge
ts
> added, then it is a maintenance issue to make sure to add a SEL COUNT for
> that one.
> What i am thinking for, is to be able to determine PK usage before letting
a
> user do a delete, rather than having them try to delete, and coming back
> with an "Unable to delete, FK reference........" error message from the
> db.
> Any ideas?
> Thanks in advance,
> Arthur Dent.
>
>|||Thanks both for the help. I had a feeling it was a long shot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:32334D22-327B-49C1-99BB-403EF8DD8545@.microsoft.com...
> Arthur,
> There is not an ease way to do what you want, better to let sql server to
> check the existence of a reference to this id. Here is a simple script
> that
> count the references to a single column (a foreign key could be a multi
> columns one) and the id's value let us cast it to varchar. If the unique
> or
> primary key being referenced is a multi columns one, then the script
> should
> be modified. I am also inclueding a link where you can read about the
> risks
> of using dynamic sql.
> use northwind
> go
> create table #t (
> r_tn sysname,
> r_cn sysname,
> r_value sql_variant,
> f_tn sysname,
> f_cn sysname,
> cnt bigint
> )
> declare @.sql nvarchar(4000)
> declare @.f_tn sysname
> declare @.f_cn sysname
> declare @.r_tn sysname
> declare @.r_cn sysname
> declare @.v sql_variant
> set @.r_tn = 'employees'
> set @.r_cn = 'employeeid'
> set @.v = N'6'
> declare my_cursor cursor local static read_only
> for
> select
> object_name(fkeyid),
> col_name(fkeyid, fkey1)
> from
> sysreferences
> where
> rkeyid = object_id(@.r_tn)
> and col_name(rkeyid, rkey1) = @.r_cn
> open my_cursor
> while 1 = 1
> begin
> fetch next from my_cursor into @.f_tn, @.f_cn
> if @.@.error <> 0 or @.@.fetch_status <> 0 break
> set @.sql = N'select ''' + @.r_tn + N''', ''' + @.r_cn + N''', ''' +
> convert(varchar(128), @.v) + N''', ''' + @.f_tn + N''', ''' + @.f_cn + N''',
> count(*) from ' + quotename(@.f_tn) + N' where ' + quotename(@.f_cn) + N' =
> '''
> + convert(varchar(128), @.v) + N''''
> insert into #t
> execute sp_executesql @.sql
> end
> close my_cursor
> deallocate my_cursor
> select * from #t
> drop table #t
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Arthur Dent" wrote:
>