SQL Keys in Depth

does seem to me that the author is a hobbyist or a junior developer with little real word experience.

Literally the first Colum of almost every SQL table I have written has had a column called id with auto increment.

We adopted a similar (well, I think probably identical) approach some years ago. No regrets -- it is simple and works well.

> global Hi/Lo id generation system

Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

I think the author fell for falsehood 21: http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-b...

Yeah I was surprised by this in an otherwise great article.

Completely agree. Adding a proper primary key e.g. from a sequence involves very little extra overhead and protects against this. Asking them to drop the constraint in the future sounds a bit silly as that may mean two members get deleted when you intended only one. Plus your foreign keys now have to be maintained. So if a member changes their name you have to update all foreign keys. Also if the hobbyist club then builds a website to view members the URLs can use the primary key e.g. a long / bigint, rather than names. The names may introduce further complexity with having to escape the URL etc. I am all for keeping it simple, but all relations should have a guaranteed unique primary key.

I know the author is talking about a small scale project here, but shortcuts like this (in my experience) just lead to a mess later on; irrespective of the complexity of the problem being solved.

It's also worth noting that the same is true for email addresses. People share them, so two people can have the same address.

> Expecting a club administrator to be able to drop a uniqueness key from their database in order to resolve this situation is not a reasonable solution!

Especially since if you have any FK relationships, and that was the only candidate key for the members relation (and why have such a bad key if it's not the only one), you then need to add new attributes to create a new key, populate it to all existing roles, change all existing FK relationships and associated queries, on top of the easy part, which is dropping the old unique constraint.

For extra fun there was talk today on twitter about several people that have to file Japanese tax returns but have originally english names. There are many ways to convert the name to english, and different systems actaully enforce you to convert it in a different way with various limitations.

This results in them having the system believe they are two people, one of who hasn't filed returns and no easy way to fix it (apparently the easiest fix is to possibly create a company?).

This despite there being some kind of unique ID submitted in both systems, but that ID is not currently reconciled.

Not sure 100% on the details myself but I guess the moral of that story is, duplicate checks on the name itself may not even be a guarantee of uniqueness - even assuming there was only 1 person with that name!

More information in the Twitter threads: https://twitter.com/patio11/status/947821990810869762 https://twitter.com/marcan42/status/947862901725024257

I agree with you, but I think you did overthink his metaphorical example a little.

I think the point was that for a 40 people club odd are very very poor that two people would have the same name. And even if so a club manager could still differentiate by adding a middle name or a nickname (in a 40 people scenario).

Of course IF your neighborhood club expand and you need to manage a lot of people you'll have to switch to a better technique. His point was that you must fit to you business case. (But hey some people change name when they marry so... problems can happen fast, but that's mutability issue not uniqueness).

Overall very good article but keeping critical mind as you did is needed.

Ah, this brings me back to the time when I was trying to convince a product manager that it was a bad idea to "validate" email addresses with regular expressions. I failed, and the product was rolled out with the following regex: ^[a-z0-9.-]+@[a-z0-9.-]+\.[a-z]{2,4}$

I quit shortly thereafter.

Might be a feature though, as in "the Ancient Mystic Society of No Homers", where it is not allowed to have more than one Homer member: https://en.wikipedia.org/wiki/Homer_the_Great#Plot

That's not the only point of the article I really disagree with:

> Here are some values that often work as natural keys:

> login names

This makes user names static. Which would annoy people if they change name (eg marriage, gender change, nationalization change (Chinese name vs English name)) or they just want to update their online handle.

> email addresses

Same problem as above. What happens if someone wants to change email addresses (eg they used a work one and changed jobs, or they got a new email to combat spam on the old one, or a new email address to reflect a name change (see above))

> mac address on a network

This makes more sense than above, but MAC addresses can be spoofed or even just used as a proxy, so you would need to be careful that the information you're uniquely storing against the MAC address is intended to be unique. Most use cases that immediately springs to my mind wouldn't follow his unique rule but I'm sure there will some examples that do.

> (lat,lon) for points on the earth

That might work if you also included an elevation point as well but office and apartment blocks (for example) will often have different tenants at the same latitude and longitude coordinates.

> Some people seem to struggle with the choice of “natural” key attributes because they hypothesize situations where a particular key might not be unique in some given population. This misses the point.

Actually no it doesn't. That's entirely the point as it demonstrates good planning and future-proofing rather than setting arbitrary business rules that are hard to rectify if and when you do run into those particular edge case scenarios.

The bit about duplicates likely being "unintentional at this scale" is an important part of the use context too though. There are many cases where allowing duplicate names increases errors and confusion for users -- not only because of unintentional duplication, but because users themselves often think of names as unique.

To me, surrogate keys are just about always preferable over natural keys, but it's also important to think about the "naturalness" of natural keys from a user's pov. In some contexts, using a surrogate key while also enforcing unique names (with the rare possibility of a case where an admin has to go in and do something weird like add a genuinely identical name with a "2" after it, say) can be a better trade-off.

They don't necessarily matter until you hit Twitter-size for performance purposes, but take care to pay close attention to the idea of natural vs. artificial keys, and heed his warning about turning artificial keys into natural keys.

One day you'll be integrating database systems together and you'll be glad you followed his advice. I've seen some really ugly bizdata schemas.

As you can see leaking through in a few of the examples, the scenarios where it matters are the ones where non-experts are reading to and writing from the database. If a data entry clerk is just typing values into a form thinly wrapping an INSERT, you'd better make sure any input that would break implicit assumptions is rejected. If you have a legion of Bobs from marketing who "know some SQL", you can't hand-optimize every single query they try to run.

When the database is just serving a webapp you control end to end, the only part that'll really have a huge impact is making sure you can partition by the primary key effectively. Which is good, because complex indexing schemes and foreign key constraints actually scale very badly.

You can get some real headaches with auto-incrementing integers if you have to go to a multi-master or some other distributed system for data inserts.

How do you know which integer to insert next for a table that is replicated across systems? One system could do even numbers and one could do odd. One could do every other 100 integers. Or you could use UUIDs and not worry about it.

My argument is that auto-incrementing integers are inherently not scalable. They can scale to a certain extent, and then you have to switch key systems.

I've seen auto_increment fail to scale on MySQL. Large, long-running insert statements (e.g. using insert into ... select from, or load data infile) can lock the the table resource and block concurrent inserts. Pretty easy to work around: have a table containing the next available key for every table, and use a separate small transaction to bump it before bulk inserts.

Another reason not to use auto-inc is if you need to insert lots of data into several separate tables that have foreign key relationships. If you know what you're inserting up front, and you need to insert millions of rows quickly, you're better off allocating the primary keys ahead of time and supplying them directly with the right keys for the relationship at insert time.

Separately, another argument against compound keys: if you're in a situation where you're trying to optimize joins, sorts and filters over moderately large tables, you want to minimize the number of columns you touch. Every column measurably increases query time; more data to shuffle, sort, compare, bigger indexes, etc. You won't see this if you're doing simple key / row lookups, but you will see it if you're presenting user-interactive slices over million+ row data sets.

A lot of these are not performance considerations. I read them as mostly for architectural ergonomics, security, and to some extent "idiot-proofing".

I disagree that UUIDs are generally preferable over integers. For one, they take up more space (on disk and in memory). And for something like a key, it is likely that there will be multiple copies of that value stored, since it will exist in the table itself, at least one index (possibly more) and foreign keys. More space means fewer records per page on disk, more I/O and more memory usage (potentially leading to more I/O). I would wager that for most users (including this case), the cost of this additional I/O is far greater than some theoretical scalability limitation on generating IDs.

Most database vendors make sequence generation (whether through explicit SEQUENCE objects or auto-incrementing columns) performant by making a few compromises:

* Numbers may not always be sequential (you might get 1, 3, 2 - in that order)

* There may be gaps (you might get 1, 2, 5, 6)

But since these are supposed to be opaque identifiers, neither of these compromises should be a concern for most users. But this means that these sequences can live outside of a transaction (you might grab an ID, rollback, and that ID is gone) and that systems with multiple nodes can be allocated a "block" of numbers from which they can quickly pull new values, without needing to coordinate with a master node.

I would certainly look to add not-null and uniqueness constraints, although you might find that by now your data is actually violating some of those constraints!

A lot of people take the view that the application code is sufficient proof against bad data getting into your database, but this ignores (a) bugs and (b) back-door data loading, which in most applications will almost certainly be happening at some point.

The data in your database is the crown jewels (the application that fronts it is by comparison a piece of crap that you can replace at any time), so put guards on that gate ...

No constraints will often be faster, but your data will not be 100% consistent.

Over time you _will_ have child child records pointing to non existing parents and the same with foreign keys. These often don't really hurt anything, until they do. Since most of the testing is with clean data, most issues due to bad data are in production and reported by end users.

I've worked successfully on systems without any real constraints and it is usually much better to start with and remove them later as needed. Adding them later is a pain as you first have to clean up the data and fix the code issues that created the bad data in the first place.

You might not have any observable effects until the day you do, at which point you may be faced with an arbitrarily large problem.

What you are doing is passing up on the opportunity of catching various errors (you are also passing up the opportunity for some optimizations, but that is probably a secondary issue.) In particular, you are passing up on some opportunities to catch inconsistencies in how different applications (or different parts of the same application) create and use data.

One argument made against putting these sort of rules in effect is that they constrain what application developers can do, but that is the wrong way to look at it: any such conflict is an indication of a misunderstanding (not necessarily on the part of the application developers) that has been caught before it can lead to bigger problems, such as a database full of irretrievably inconsistent or incomplete data.

When the problems finally do arise, it is often the case that some sort of workaround is the only practical solution. This, in my experience, is one of the common ways by which systems accrue gratuitous complexity, which in turn has at least two real-world consequences: an increased time to make changes, upgrades and extensions, and an increased frequency of errors, especially WTF-type errors (and probably also efficiency/performance hits.)

My background is in financial applications, where an inconsistent state can literally be a multimillion dollar problem, so maybe my perspective is skewed a little. But FKs and other constraints are simply used to outright enforce consistency, in my experience at least. If you rely on your custom business logic to maintain consistency, then you leave yourself open to the possibility of breaking that in some way, and if you do, who says you'll notice right away? What if you don't notice for 6 months, and then realise that you have a billion transactions that have some form of integrity compromise?

Complexity obviously makes this problem more serious, but something like this can easily occur at any scale of complexity or transaction volume. If you try to commit some code that breaks the integrity of an important relationship in some way, or even if you just want to run an arbitrary statement directly against the database, then you want the DB to throw an error.

> Unfortunately, current DBMSs provide little to no support for properly implemented surrogates.

UUIDs instead of per-table autoincrement keys seem to be a solution which covers the part of the problem that involves DB support as opposed to data model design, and several DBs have adequate support for UUIDs.

I would argue that the support is there. You can either use random UUIDs or a global shared sequence for this. Nothing requires you to have a sequence per table other than some synthactic sugar in the SQL standard. PostgreSQL also has its own OIDs, but I think that is more of a legacy from before they implemented sequences.

Yeah, not the usual poorly argued heresay. Good job author.

That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org

Another way I've seen done to preserve uniqueness where order leakage doesn't matter is prefixing IDs uniquely per table. Something like uid147... for user IDs pid12... for post ID... It is certainly helpful when debugging and isn't hard to write a test to verify prefixes aren't duplicated across tables.

What is the advantage over UUIDs? The author says that the surrogate keys should never be exposed outside of the database anyway, so no problems with data leakage. If an artificial key is to be exposed then it can be obfuscated to prevent information leakage.

I think it should be obvious that it is obfuscated, though, like Youtube video IDs, for example. If you generate integers then your users might assume they are not obfuscated. Eventually a customer will get some low value integer and might assume that they are "customer #5" even if they are not.

> If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table.

Almost always there is no good unique unchanging identifying value on a table. Take something as simple as "Person" -- there is literally no unique unchangeable value for such an entity. And that's the rule more than the exception.

I take exactly the the opposite approach; nearly all entities should be identified with a surrogate key. You pretty much cannot go wrong with this approach.

In the US state names are pretty stable, but in other countries state/province names have changed so even that value is poor choice for a primary key.

One red, one black.

Isn’t that exactly what the NEWSEQUENTIALID function was created for?

https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...

The solution to your concern is using " UUID generation algorithms (like Twitter’s “snowflake” or uuid_generate_v1() in the uuid-ossp extension for PostgreSQL) produce monotonically increasing values per machine." and is discussed in the article.

Some ideas:

1. Rename the constraints to mimick the classification of keys described here. E.g. require UUIDs for artificial keys.

2. Make joins with foreign artificial keys much lighter syntactically than free form joins on natural keys. Make joins involving non-ubique columns in the WITH (today's syntax) harder still. People should be able think of foreign keys as references to be dereferenced separate from full joins.

2. Going further, break apart "tables" to represent the are used for different things people use them for, e.g. key-value maps vs "join tables" (a term I had never heard before but immediately could define from experience) vs cannonical data. In math terms, distinguish sets of aggregate data from relations. Having full algebraic data types for the sets of agreggate data but not relations could help. (maps are a special case of relations.) Another final related distinction is tables-as-types vs tables-as- collections. I suppose that relates (no pun :)) back to what joins are allowed.

4. Keep the references pointing in the right direction. For example, à la Rust and capability theory, the owner should point to it's uniquely owned children. But, in SQL today, we instead invert the back reference, having the children refer to their unique parent. Supporting lists (of children) and unaliased foreign keys directly would make the cannonical back-reference an implementation detail.

Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore.

We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks, the way it writes, or the way it handles. We're not using it because we've tried to use ORMs and those ORMs have proved conclusively better across use cases than SQL.

The reason we use it is because we don't yet have a better solution. If you can write a better solution than SQL I would love to hear about it: I absolutely loathe SQL, but I use it because out of everything I've seen, it's the simplest and most elegant solution to the problem that I've ever come across.

So yeah, while we could possibly and should definitely make SQL better if we can, I don't think that suggesting that we just re-do it from scratch is the way to go. That way lies madness, and possibly javascript frameworks.

The relatively new "INSERT ... ON CONFLICT" mechanism is a great way to circumvent this issue. Using that feature, you can tell it to ignore conflicting dupes, or you can have it transform the insert into an update.

Yes, in PG at least an index is created, otherwise the unique check would become prohibitive.

An index is usually the most efficient way to check for uniqueness, so most databases do in fact handle this constraint with an index. Some of them will also automatically reuse this index if you manually try to create one for the same column.

A joker does not have a suit, nor a number, so it would not fit in the example table anyway.

Decks with four jokers are often suited (just as the most common decks with two jokers are red/black), so a deck with three jokers you could treat as suited jokers that are missing a suit.

OK. But how does that relate to this article? If you're using keepalived what you're doing is moving the connection from one instance to another if the current one is detected as being dead. Even if only the MAC was used to generate the UUID (which it is not, timestamp is factored in too) I struggle to find a scenario in which this would be a problem. Even if you time it down to the nanosecond, if keepalived is used as intended you'd never generate a UUID at the same time on a number of machines sharing the MAC.

And from a privacy concern, if the MAC cannot be considered unique, that's only a bonus?