The textbook definition of a relational key is a subset of columns (NOT fields! Try attributes, if you want a near-synonym) which is unique within each row (NOT record) of a table (NOT a file). A table might have many keys; these are called “candidate keys”. If a subset of columns of a key is also unique, then that key is a “super key”. If the key has more than one column, then it is a “compound key”. A surrogate key is generated and used by the system and never exposed to the users; it does not replace a real key. People who never read Codd screw up this concept all the time. There are other types of keys, but let’s skip that for now.
Things that are used as physical locators like GUID, UUID or IDENTITY are never keys. The GUID and UUID are “Global” or “Universal” things that are not attributes in the logical data model by definition. The IDENTITY in SQL Server is a left-over from Sybase that is a table property, not a column at all. It counts physical insertion attempts and is not a logical concept. It is a UNIX record number in disguise!
Primary Key is a more complicated concept than you first think. When Dr. Codd introduced it, all databases were based on existing fie systems, which grew out of magnetic tapes and punch cards. And so was our mindset. Obviously, you can do one and only one sorted ordering on a file. A sequential file has to be sorted to be usable; random access on a magnetic tape is a bitch. So the first SQL engines needed to have a way to mark this access method in code.
Now, on to heuristics for designing a table.
1. Look for an industry standard. These are often required by law. Think about a VIN on your automobile, an ISBN on your books, etc.
2. Look for validation rules. Since keys are modeled with a nominal scale, it should have regular expression. If it is a neat, clean encoding like ZIP codes, then the expression is easy (zip_code LIKE ‘[0-9][0-9][0-9][0-9][0-9]’). If it badly designed, then the expression can a mess, like British Postal Codes. Compound keys may require validation on the components and also the whole of such a key.
3. Look for verification sources. Call the DMV or AutoMax for the VIN on that car you bought.
4. If you honestly cannot find a source, then carefully design your keys. I have written several articles on how to do this. This is also in my books.
Please post DDL and follow ANSI/ISO standards when asking for help.