Can someone please explain to me the difference between a primary key, foreign key, clustered index, natural key, and surrogate key?

Question

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

Answer 1

primary key: the thing that is guaranteed to identify a row in a table. it might be a single column that’s an incremental number (an identity column), or it might be something like a guid, or it might be two columns that together make a unique value, but might not be unique in themselves.

foreign key: when one table links a column or columns to the primary key in a different table. this is good for referential integrity, you can’t put a value into the foreign key columns unless they actually refer to a row in the other table. and that row is prevented from being deleted if something references it. (or you do a cascading delete, that deletes all records pointing to the initial record you’re deleting).

clustered index: the columns in the table that define the order that data is stored in. normally it’s the primary key. sometimes it can be useful to have that as a timestamp, so records from similar times are all kept together in storage.

natural key: sometimes the data you’re given has a key that uniquely identifies the record already available in it. a (bad) example might be your social security number. that uniquely identifies you from everyone else in the usa. isn’t good for foreigners tho. the chassis number on a car might be another natural key.

surrogate key: if there’s no natural primary key in your data, you’ll need to give it one. if you just number the first inserted record as 1, the next as 2, etc, that key is not part of the natural data you’re storing, it’s surrogating for a natural key that isn’t present.