SEO

#1 (**permalink**) 05-02-2008, 05:04 AM

Hello there,

I was wondering if anyone could give me some advice on orphaned rows and
foreign key constraints.

I am migrating a MS-Access database that didnt have any relations setup
properly, consequently there are gazillions of orphaned tuples.

If I was to leave these as they were and set constraints with NO CHECK is it
likly that I am going to run into problems down the track?

Some may shudder that I would consider not cleaning the data but we have a
timeline and there is months of cleaning to do, its something I would prefer
to do after we make our deadlines.

Thank you
John Sheppard

#2 (**permalink**) 05-02-2008, 05:04 AM

On Fri, 2 May 2008 14:19:57 +1000, "John Sheppard" <spam@nospam.com>
wrote:

I am a purist and would not allow bad data in my database. But I'm
also a realist and your suggestion makes some sense. However, I have
seen that the desire to clean up the data disappears as the app is
taken into production and no immediate averse effects are noticed. You
and I know that's just a matter of time...
Also look at it this way. If I have Orders for a CustomerID that no
longer exists, I have limited options:
1: Do research in printed invoices in some filing cabinet and try to
find the missing customer and recreate him. A time-consiming
proposition.
2: Delete the orphans. Most are old. None can be recovered easily.
3: Assign all orphans to CustomerID=-1,
CustomerName=UnknownBadStuffLeftFromEarlierDays

Personally I would keep the Access app for research purposes, and
choose option 2. ¡No Pasarán!

-Tom.

>Hello there,
>
>I was wondering if anyone could give me some advice on orphaned rows and
>foreign key constraints.
>
>I am migrating a MS-Access database that didnt have any relations setup
>properly, consequently there are gazillions of orphaned tuples.
>
>If I was to leave these as they were and set constraints with NO CHECK is it
>likly that I am going to run into problems down the track?
>
>Some may shudder that I would consider not cleaning the data but we have a
>timeline and there is months of cleaning to do, its something I would prefer
>to do after we make our deadlines.
>
>Thank you
>John Sheppard
>

#3 (**permalink**) 05-05-2008, 05:51 AM

>> If I was to leave these as they were and set constraints with NO CHECK is it likly that I am going to run into problems down the track? <<

oh yeah! The schema will fill with garbage and will choke. Someone
will use the meaningless data for a report, etc.

#4 (**permalink**) 05-13-2008, 06:13 PM

I can't help but comment here.

We're implementing a 3rd party CRM package, and the software treats the
database like a big pile of stupid ascii files. There's no RI - no FK's
in the tables. NULLs are allowed everywhere... When a PK is deleted,
rather than cascade delete, it looks like any logical references to it
are simply set to null..

Its enough to drive a DBA completely freaking insane...

Is this sort of thing common in CRM packages?

aj

--CELKO-- wrote:
>>> If I was to leave these as they were and set constraints with NO CHECK is it likly that I am going to run into problems down the track? <<
>
> oh yeah! The schema will fill with garbage and will choke. Someone
> will use the meaningless data for a report, etc.

#5 (**permalink**) 05-13-2008, 06:13 PM

>> It is enough to drive a DBA completely freaking insane...Is this sort of thing common in CRM packages? <<

I don't know about CRM packages, but I worked for a company that did
the same kind of crappy coding in Configuration Management software.
I reviewed a municipal public works package which only had IDENTITY
columns for keys and absolutely no column constraints. I am no longer
amazed at the level of cowboy coding in packages.

>> When a PK is deleted, rather than ON DELETE CASCADE, it looks like any logical references to it are simply set to NULL.. <<

Done manually, instead of with ON DELETE SET NULL? You probably don't
remember pre_RDBMS databases, but they often had a bit flag at the
start of a record to mark it as active or deleted. The reason that
records were located by physical position within a file or pointers of
some kind. You would do explicit garbage collection every now and
then to re-claim the disk space and to re-build the pointer chains and
links. Another form of flag for this purpose was to set a link to NIL
by traversing down a chain to the leaf nodes, then recursively back up
the chain to the parent record.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Orphaned tupels, should I clean or is it ok to leave?