Posted by Tom on 10/13/05 21:01

thanks for the tips.

But I need to follow the specs. The specs says that cascade update or
delete is not supported (along many other things like joinning tables is not
allowed). And it comes to a point that I have to say screw this. I am
going to use autonumber.

I am trying to look for any sounded and proven pattern. But searching
Google gives me only the "it depends" solution.



"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1129224960.345778.104210@z14g2000cwz.googlegroups.com...
> Your whole mental model of RDBMS is wrong.
>
> Exposed locators such as IDENTITY are not surrogate keys. A surrogate
> is totally hidden from uers and maintained by the system -- think of
> how an index works. If you change a natural key, the DRI actions will
> cascade it for you. Hell does not break loose. But if your autonumber
> and real key are out of synch,then you are screwed. Just enter the
> same record several times to get different autonumbers on duplicate
> rows. Drop all but one of the dups and then try to find all of the
> referenced rows in other tables.
>
> If you maintain the exposed locator by hand, you have extra work, extra
> disk seeks and will eventually make a mistake, thus destroying your
> data integrity. How would you validate and verify your data?
>
> The UPC code just went from 10 to 13 digits. It does not matter if you
> used an autonumber or the UPC, the UPC has to be updated. If you had
> used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
> a single update with CASCADE. This is one of the MANY reasons that
> rows are not records. This is a problem in SQL Server because it is
> still based on a contigous storage model, but other products are not,
> so wind up using tools to write scripts.
>
> Finally have you bothered to time the two approaches with small and
> large tables? It does not make a difference until the table has to do
> a lot of paging and redundant autonumbers accumulate. This is the
> 21-st century; we have 32 and 64 bit hardware, nano-second speeds,
> multi-word instructions, parallel processing and really good disk
> drives.
>

[Back to original message]