Learning SQL right now and the definitions for these terms are a little confusing. Can someone please explain them with an example? Thank you.
In: 15
“Keys” are selections of column in a table that are indexed in some way, intended to allow for much faster searching. Typically the index is stored in a different file from the raw table records. With that said…
A “primary key” is the key designed to uniquely identify rows in a table. So if a primary key is defined on columns `(a,b,c)` then a search with condition “WHERE a=1 AND b=2 AND c=3” will turn up exactly 0 or 1 rows. 2+ rows are impossible. As a result, rules that may allow multiple row matches, especially where NULL is involved, are forbidden. For example, if you run a shopping store, then products could be uniquely identified by their barcode numbers. Duplicate of that should never happen, and if they do then yeah you have a problem that needs solving anyway.
A “foreign key” is a cross-reference between tables. Table X may have a column named A and it’s a foreign key reference to table Y which also has a column named A (typically the same name to help reinforce they are the same piece of information for that cross-reference). The database may refuse to allow you to delete information in one table because the other references it, or the act of deleting from one table may cause deletion of data in the other table to keep the foreign key requirements met, depending on settings. It also helps software visualize the relationship between the tables. For example, if you have a table of customers, and a table of outstanding orders, you may not delete a customer if they have any outstanding orders placed, but deleting orders is fine.
“Clustered index” means the actual raw records on disk are sorted to this ordering. There can be advantages to that if you are fetching large amounts of data and you want it delivered pre-sorted… if you ask for it in the same sort order that the clustered index is provided in, then the DB just does sequential disk reads and gets data back pre-sorted, solving that problem without needing to actually perform a sort in memory or anything.
Now let’s go back to the primary key: a unique way to identify rows. How do you actually do that? There’s 2 common strategies: a unique number typically starting at 1 and counting up indefinitely, or finding some set of columns that are actually enough to uniquely identify a row. The former is what we call a “surrogate” key – something we introduced to assist. The latter is what we call a natural key – something already provided. For our list of products, barcodes make a pretty good natural key. For our customers, names aren’t unique, even addresses aren’t uniqu and we don’t have anything we can give them (damned customers keep refusing our loyalty cards) so we just give them a customer number and each new customers gets the next number. That’s a surrogate key. Customers might see the number on their invoices, but by itself the number doesn’t mean thing.
Latest Answers