SQL Keys in Depth
begriffs.com
SQL Keys in Depth
1–10 of 174 posts
Re: SQL Keys in Depth
#2A review article that combines original research, excellent writing, and useful advice. Impressive!
Re: SQL Keys in Depth
#3On 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").
Re: SQL Keys in Depth
#4A review article that combines original research, excellent writing, and useful advice. Impressive!
Yeah, not the usual poorly argued heresay. Good job author.
Re: SQL Keys in Depth
#5This 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.
Re: SQL Keys in Depth
#6This 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.
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.
Re: SQL Keys in Depth
#7The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database, have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as above you may freely join relations on surrogate keys being sure that you are joining related data.
In his landmark 1979’s paper, Codd defined surrogates and pretty clearly, and that part of his work was based on an earlier paper entitled On Entities and Relations (I don’t recall the authors right now), so this is not exactly news.
Unfortunately, current DBMSs provide little to no support for properly implemented surrogates.
Re: SQL Keys in Depth
#8The most important property of surrogates, which I never see mentioned, is that two tuples, anywhere in the database , have the same surrogate key if and only if they are (believed) to refer to the same real-world entity. This is a crucial difference wrt auto-incrementing or randomly generated values that are independent in different relations (which is a common practice). Among the rest, with surrogates defined as a…
> 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.
Re: SQL Keys in Depth
#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 delays due to the data being ordered randomly on disk. There are obviously ways to counter that like adding extra non-clustered indexes or changing the clustered index to something besides the primary key, but at that point the extra time and overhead might defeat the benefits you gained from going with UUIDs in the first place.
Re: SQL Keys in Depth
#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 because their name clashes with an existing member is not OK.
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!