Systematically determine DRI problem(s)

[NickName]

Hmm, I'm wondering if some of you have figured out a way to check a
large database's DRI programmatically.
The term, 'a large database' is a loose one, let's just say, at least
over 200 user tables. Yes, DBCC CHECKCONSTRAINTS is handy.
However, it won't be able to tackle some hidden DRI problems. For
instance, here you have two tables, totally fictionary! but possibly
in the
real world, Customer and Account (for demo purpose, I'll name them temp
tables here),
the original designer probably meant to link them via customer_ID,
however, he/she did not do it properly.
-- DDL and DML
-- one day at data life
create table #customer (customer_ID char(12) not null primary key,
first_name varchar(20), last_name varchar(20), sex char(1), state
char(2), ssn char(11),
check(Left(customer_id,2)=state AND Right(customer_id,4)=Right(ssn,4))
)
-- thanks Joe Ceilko for a more meaningufl PK ...
insert into #customer
values('md-1234-1234','Dan','Li','M','VA','567-28-4321')
create table #account (account_id int identity(10000,1) primary key,
account_type varchar(10), amount money, last_update datetime,
first_name varchar(20), last_name varchar(20))

insert into #account (account_type, amount, last_update,
first_name,last_name)
values ('Receivable',3000.0000,getDate(),'Dan','Li')
/* at least two problems here
a) DRI is lost here
b) Instead of 'Dan' and 'Li', one could enter 'NosuchFN' and 'NosuchLN'

-- another day at data life, don't ask me why they do that, I would
likely use ACTIVE flag to keep all data
delete
from #customer
where customer_id = 'md-1234-1234'
-- now, boss ask why do we have this Dan Li in the #account table while
there's no such corresponding record in the #customer table or
who the heck is this Dan Li anyway (give me more info about this guy?)?
Well, if we have only a few or a dozen tables, it won't require tons
of effort to find data problem for the given situation (database),
but again, let's say, this db has over 200 tables, checking them by
hand would seem to be like doing things like Homo Sappiens, I don't
mean
to be lazy, so, how would you systematically at least programmatically
identify the DRI problems?

Many thanks in advance to those clearer heads.
DL

[Erland Sommarskog]

NickName (da****@rock.com) writes:

Well, if we have only a few or a dozen tables, it won't require tons
of effort to find data problem for the given situation (database), but
again, let's say, this db has over 200 tables, checking them by hand
would seem to be like doing things like Homo Sappiens, I don't mean to
be lazy, so, how would you systematically at least programmatically
identify the DRI problems?

I'm afraid that the only answer I give, is the one you don't want to
hear: do it right from the beginning.

And if you didn't do it right from the beginning, you have a nightmare
now to sort out. You can of course set up a unique constraint on the
customer name (just temporarily) and then an FK from accounts to see
what happens, but since it's likely to fail, it's not that useful. You
will have to write SELECTs for all relations you want to check.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickName]

Erland,

Homer sometimes nodds. We miscommunicated.
"the original designer probably meant to link them via customer_ID"
was meant to say, this or any other such db is inherited from some one
else.

Don

Erland Sommarskog wrote:

NickName (da****@rock.com) writes:

Well, if we have only a few or a dozen tables, it won't require tons
of effort to find data problem for the given situation (database), but
again, let's say, this db has over 200 tables, checking them by hand
would seem to be like doing things like Homo Sappiens, I don't mean to
be lazy, so, how would you systematically at least programmatically
identify the DRI problems?

I'm afraid that the only answer I give, is the one you don't want to
hear: do it right from the beginning.

And if you didn't do it right from the beginning, you have a nightmare
now to sort out. You can of course set up a unique constraint on the
customer name (just temporarily) and then an FK from accounts to see
what happens, but since it's likely to fail, it's not that useful. You
will have to write SELECTs for all relations you want to check.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp