|
|
Posted by Hugo Kornelis on 10/13/05 22:06
On 13 Oct 2005 10:36:00 -0700, --CELKO-- wrote:
>Your whole mental model of RDBMS is wrong.
Hi Joe,
You're whole mental model of surrogate keys 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.
So don't display the surrogate key on the data entry screen.
>If you change a natural key, the DRI actions will
>cascade it for you. Hell does not break loose.
If you change a natural key in a table that has a surrogate key as well,
there's no need to cascade it, as there's no redundant storage of the
key values. Hell does not break losse AND you don't need to get locks on
half the tables in the database.
>But if your autonumber
>and real key are out of synch,then you are screwed.
Eh? How can they "get out of synch"? Please post proper DDL and DML to
clarify what you mean.
>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.
That's not a result of IDENTITY, that's a result of forgetting to
include a CHECK constraint. Database theory 101.
>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.
That's the point - you DON'T maintain the surrogate key by hand.
>How would you validate and verify your data?
Same as you would do it: use the natural key.
>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.
For starters, SQL Server 2000 doesn't have a switch that customers can
just flick to get full SQL-92 compliance. There is no ALTER DOMAIN
command in SQL Server 2000.
Fortunately, those who had the foresight to add a surrogate key to the
Products table don't need ALTER DOMAIN, nor CASCADE. A simple ALTER
TABLE and a single UPDATE on one single table suffices.
>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.
You mean to imply that other RDBMS's have no autonumber facility? Get
off it. Less than one minute of googling gave me:
- SQL Server, DB2: IDENTITY (with some variations in syntax);
- MySQL, SQLite: AUTO_INCREMENT (with some variations in syntax);
- Postgres, Oracle, Sybase, Mimer: CREATE SEQUENCE (with some variations
in syntax).
http://sqlzoo.net/howto/source/z.dir/tip000001
>Finally have you bothered to time the two approaches with small and
>large tables?
The OP was not asking for an advice for a specific situation, but for a
generic truth on whether to use or avoid identity. Which of course there
isn't - since your religious insisting that IDENTITY should never be
used is just as fundamentally wrong as brainlessly whacking an IDENTITY
column onto each and every table.
In databases, "it depends" is the only reliable answer.
>It does not make a difference until the table has to do
>a lot of paging and redundant autonumbers accumulate.
Redundant autonumbers? Care to elaborate?
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.
But we also have applications that gather hundreds of rows per second,
and queries that run on million-row tables - situations that can't be
"tuned" by just throwing more and better hardware at it. Performance s
just as important a consideration as it ever was. And including or
removing a surrogate key can (in some situations) improve or damage
performance by several degrees of magnitude.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|