5 ms·

Anyone else bothered when primary keys are not given the name 'ID'?

• jfb 13y ago
  I'm bothered by primary keys full stop.
  • nahname 13y ago
    What's wrong with identity?
    • jfb 13y ago
      Designating a candidate key as primary is a nasty SQL implementation wart.
    • VLM 13y ago
      A concrete example is some folks like the abstraction of a row as your primary "thing" and some folks like the abstraction that the data defines a row and rows don't really exist just the data. Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? Shouldn't you use the full name as the "primary key"? Problem: What if the business logic of what a distinct user is changes from unique "full name" to unique "full name" and "telephone number". Whoops now all your foreign keys need messing with, its just a bad scene. Ditto schema changes like you finally change from ascii to utf8 or something, now all your foreign keys need changing (well thats maybe a bad example unless your ascii datatype enforces 7 bits or you're running into byte length vs character length limits...) Or you change the length, which changes the truncation perhaps, which changes your foreign keys. Also you can't just use a rule like all foreign keys are BIGINT now some are CHAR(20) some are FLOAT who knows. On the other hand lets say you implement just a prikey. Now you can have multiple rows with the same data, because you never set up a UNIQUE INDEX. Generally speaking if you KNOW absolutely KNOW that your schema will never change, you should probably optimize it to not have multiple keys aka a primary key and unique indexes, or data definition will never change. Very few people can guarantee it so they're better off in the real world with imaginary prikeys. You can read a lot more about this in "SQL antipatterns" I think chapter 4 or so, but always keep in mind that beyond noob level of being able to define the overall issue, short term snapshots will occasionally (but not always) conflict with longer term thinking.
      • dragonwriter 13y ago
        > Consider the hated multiple primary key situation where you've got a autoincrementing prikey and a "real" key where you make an unique index off "full name" or something. So which is the real conceptual primary key? If the full name is a real conceptual primary key, you shouldn't have introduced an autoincrement key. If the uniqueness of the fullname is a business rule but not a real conceptual restriction (a distinction which can be hard to make, to be sure), then it makes sense to create the autoincrement key -- and it is the only real primary key. (That is, the autoincrement key represents the concept of identity which isn't present in any of the other data.) > On the other hand lets say you implement just a prikey. Now you can have multiple rows with the same data, because you never set up a UNIQUE INDEX. No, you can't, because the "prikey", as you call it, is data, and has meaning -- specifically, it represents identity -- so rows which differ in it do not have "the same data".
        • VLM 13y ago
          The business concept of the "real conceptual primary key" can change, perhaps dramatically, over time, as the business model changes. A real prikey never changes.
          • dragonwriter 13y ago
            > The business concept of the "real conceptual primary key" can change, perhaps dramatically, over time, as the business model changes. A real prikey never changes. This confuses two different concepts: If the conceptual model changes, then, yes, the candidate keys (including primary keys) of entities may change between the old model and the new model. This can be a pragmatic difficulty in migrating between different conceptual models, but that's a problem inherent in different conceptual models. The value of a well-chosen primary key of an entity within any given model should not change, as the primary key should always be a value which identifies the entity such that a different primary key means a different identity.
      • jfb 13y ago
        ON UPDATE CASCADE is in the standard.