[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [ProgSoc] NULL values in primary keys in MySQL
On Sunday 04 June 2006 1:20 pm, Myles Byrne wrote:
] On 04/06/2006, at 12:57 AM, John Elliot wrote:
] > Natural keys are a nightmare.
]
] I just want to reaffirm this. Unless this is a legacy db where it's
Can you point me to a nice explanation of why this is so?
I have an easier time conceptualising a natural key, than an
arbitrary one. Most (all?) of my selects, joins, and sorts will be
based upon the two attributes that go to make up my (natural)
primary key.
I'm not talking a lot of data - maybe in the hundreds of rows, with
about two dozen attributes (no large blobs). The other four or
five tables will be comparably trivial. Concurrent users would be
maybe a dozen. This is not a high performance OLTP app I'm cranking
out. I haven't even looked at indexes, as I imagine they'd be
overkill, plus I don't fully understand their size/perf implications.
Is the avoidance of natural keys for performance reasons, or the
tendency for them to not be immutable? (See - I did go and read
up on all this stuff last night.)
Just what does happen if I change my '0' attribute on one of these
records to, say, a 'j' in the future. Will that really break things,
or is that only a concern with far more complex schemas that have
lots of permanent connections between tables?
I'm loathe to add another attribute, particularly one that I won't
use, and that has no *meaning* -- yes, this may be an old-fashioned
way of thinking about schema design. Happy to be dragged into the
21st century.
arp the rested -- I realised that I'd been using the phrase primary
key to refer to what is in fact a compound key. I'd have data that
looks like the following. PK defined as combo of (number, suffix).
number, suffix, other crap ...
1, NULL,
2, a,
2, b,
3, g
3, j
4, NULL
5, NULL
etc ...
There would never be a row of data that wasn't uniquely identified
via a unique combination of INT and one of [ NULL, [a-z]].
This comes back to John's observations .. and I still don't grok
it. I realise that NULL != NULL, but I'm not comparing one NULL
to another. MySQL (at least) gives the client the ability to do a
test such as 'WHERE suffix = NULL' -- so presumably the engine has
a way of understanding that something *is* null, at least, and that's
as effective (for my purposes) as its ability to determine if an
attribute is a zero or a particular lower-case char.
I'm happy to let this one go .. I suspect it's a lack of understanding
of the subtleties and nuances of what NULL really means.
Jedd.
-
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to progsoc-request@xxxxxxxxxxxxxxxxxxx
If you are having trouble, ask owner-progsoc@xxxxxxxxxxxxxxxxxx for help.