|
|
Posted by NickName on 11/24/84 11:26
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
Navigation:
[Reply to this message]
|