Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Keys in Depth

begriffs.com

Post reply on HN
Print view View original

11–20 of 174 posts

Re: SQL Keys in Depth

#11

Post by jeremy_wiebe »

slg wrote: post #9

>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition. Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delay…

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

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

Re: SQL Keys in Depth

#12

Post by dizzystar »

It's an interesting article with interesting ideas.

I'm squarely in the camp of using natural PKs until there is good reason to use surrogate PKs.

I generally disagree with the notion of using a combination of surrogate and natural keys. In SQL, a PK isn't just another unique key: a PK an important block of communication.

As a rule of thumb, when a PK is attached to a semantic value, it is saying that this is the identifier of the table. If a PK is on a surrogate key, it is saying that there is no good unique identifying value in the table. When you are dealing with larger data sets, this distinction isn't minor, as it helps to understand the intent of the data when working with it. PKs serve as guideposts in your design along with guideposts to the person who has to maintain (or fix) your database later on.

I know some disagree with me on that, but there are many undeniably good reasons to use a natural key. A good place is a check-constraint table, where you say, have a list of US states and you want to ensure that "New York" and not "New Yoerk" inserted into the state column of an address table. Put a PK on valid_state_names and FK to the PK from addresses.

Re: SQL Keys in Depth

#13

Post by eropple »

stickfigure wrote: post #3

On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").

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

Re: SQL Keys in Depth

#14

Post by hug »

Ericson2314 wrote: post #5

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.

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.

Re: SQL Keys in Depth

#15

Post by dullgiulio »

simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

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

Re: SQL Keys in Depth

#16

Post by slg »

jeremy_wiebe wrote: post #11
slg wrote: post #9

>Modern SQL is supposed to abstract from the physical representation. Tables model relations, and should not expose an implicit order in their rows. However even today SQL Server creates a clustered index by default for primary keys, physically ordering rows in the old tradition. Which is why the UUID (or GUID in SQL Server speak) can have other drawbacks there in comparison to auto incrementing bigints, namely delay…

Isn’t that exactly what the NEWSEQUENTIALID function was created for? https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...

In theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.

Re: SQL Keys in Depth

#17

Post by maxyme »

stickfigure wrote: post #3

On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").

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.

Re: SQL Keys in Depth

#18

Post by ofirg »

dullgiulio wrote: post #15
simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

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

I do not want to belong to any club that would not need to drop a database constraint in order to accept me.

Re: SQL Keys in Depth

#19

Post by Terr_ »

hug wrote: post #14
Ericson2314 wrote: post #5

This is well done. And yet many people get this stuff wrong. As a language person, I think about how we could make these choices more natural by rewriting SQL; make them the path of least resistance rather than requiring much pondering and wisdom.

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,…

I think you're discounting network-effects and historical inertia.

Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."

Re: SQL Keys in Depth

#20

Post by int_19h »

eropple wrote: post #13
stickfigure wrote: post #3

On Postgres I prefer to use a single sequence to generate ids across all tables. This reduces the chance of accidents (eg accidentally deleting the wrong thing with id #123) and reduces information leakage ("oh, I see I'm customer #5, you must only have 4 other customers").

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

In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.
Post reply on HN