From what I have read - and did not know before - a MySQL field with a Unique Key/Index can contain NULLs.
Starting a few months ago, when I create a new table, I like to make the first field be “ID”, and set it as AutoIncrement, Null = No, Index = Unique. (That serves as a pseudo Primary Key.)
Then, when possible, I look for another field which has a “natural” key in it (e.g. article_slug, username, etc), and I make that my Primary Key. (If I can’t find a “natural” key, then I would just make the “ID” the Primary Key.)
Any issues with that approach?
To me, even if a Unique Indexed field in MySQL can contain NULLs, that wouldn’t be able to happen if you have the field set as AutoIncrement, Null = No. (Unless someone tinkered with things on the backend. And since only I have access to the database, that wouldn’t apply.)
The question not whether a Primary Key field can hold NULLs. (No it cannot!)
The question is whether a field defined with a Unique Index and Null = No is any more likely to allow Nulls (I said “Null = No”, right??) than a Primary Key.
And I am saying, “No!! There is no difference!”
In the one of the example tables I provided above…
“ID” is defined as MediumInt, AutoIncrement, Null = No (**So there can never be Nulls.)
“SLUG” is defined as Varchar(100), Null = No (**So there can never be Nulls.)
Since neither of those fields allows for NULLs without any Index at all, in my mind it should not matter if I choose to make each Unique Index or a Primary Key.
That was my original belief, and then some Oracle guy I know at work came along and made it seem like it was suicide to use a field that had a Unique Index on it in a relationship.
The more I think about it, that argument not only makes little sense, since I have just shown that it is the Null = No attribute which determines if a field is NULL or not. But think about it…
In MySQL, most people use a generic INDEX for Foreign Keys. And an Indexed field can hold Nulls, just as a Unique Index can hold Nulls. And yet you don’t hear anyone saying, “Your Foreign Key fields must be set as a Primary Key to disallow Null values.”
My point being, unless you make your Foreign Key a Primary or Composite Primary Key - which a lot of people don’t - you’d have the risk of there being Nulls using either INDEX or UNIQUE. Of course, the way to prevent this issue is by defining the attribute (right word?) Null = No.
After thinking about this over supper, as long as my key fields have a Null = No, I really don’t see where using either of the two combinations below would make any real difference…
I think it will make your application less flexible to changing requirements. What if one day, for example, you decide that users should be able to change their username? It would be awfully difficult to implement that feature if you used the username as a “natural” key, because every username would be repeated throughout other tables as a foreign key.
And I don’t think you would even get any benefit for your trouble. The only benefit I can fathom is the expectation of some kind of performance boost. Are natural keys supposed to perform faster? How much faster? Are they supposed to use less disk space? How much less? If you don’t know the answer to those questions, then you’re going into this blind, and it may turn out that you’re sacrificing flexibility for just a micro-optimization – or it may turn out not to be an optimization at all.
My member table is one exception to my new rule of making “ID” a UK and finding a natural key for the PK. For that table, I left “ID” as the PK and have “username” as a UK.
Here is where I am coming from… (Right or not?!)
1.) Records - and most things in the Universe - are easier to refer to when they have a short, unique name.
This is why God create Serial #'s, SKU’s, Tele #'s, and so.
When I am developing, and I need to visually find a record for whatever reason, it is much easier to look for record 1234 than for “The record where field1 = ‘John Doe’ and field2 = ‘1970-02-01’ and field3 = ‘Arizona’…”
And in a production database with 100,000 records, that feature is even more useful.
2.) Creating a Surrogate Key called ID using AutoIncrement creates a nice, standard, expected numbering scheme for every table.
If there is also a good Natural Key, then you have choices! And if one doesn’t exist, you always have your Surrogate Key.
3.) At the same time, just creating an ID field that is your PK is academic in that it only ensures unique ID’s for records and not unique entities for records.
That is a good answer. In more cases than not I tend to error on the side of caution and just use auto increment PK. If something needs to be unique than I just make it unique end of story. I find that dealing with integer PKs is much easier than dealing with strings anyway. Especially when it comes to passing that type of info in URLs. There are several problems that are likely to occur if user input is used to reference items in the database that I just rather not deal with and succumb to using auto increments.