General SQL question about Primary Keys

I know this is pretty elementary but here it goes.

I would like to know how you know what columns are a primary key in a table that does not have a prim开发者_如何学Pythonary key? Is there a technique or something that I should read?

Thank you in advance

You need to take a look at your data structures.

A primary key must:

• never be NULL (no exceptions)
• reliably and uniquely identify each single row

and it helps if it's

• small and easy to use
• stable (doesn't change at all, or at least not often)
• a single column (or at most two)

Check your data - which columns or set of columns can fulfill these requirements??

Once you have those potential primary keys (the "candidate keys") - think about how you will access the data, and what other data might need to be associated with this one entity in question - what would make sense as a foreign key? Do you want to reference your department by its name? Probably not a good idea, since the name could be misspelled, it might change over time etc. By the department's office location? Bad choice, too. But something like a unique "department ID" might be a good idea.

If you don't find any appropriate column(s) in your actual data that could serve as primary key and would make sense, it's a common practice to introduce a "surrogate key" - an extra column, often an INT (and often something like an "auto-increment" INT) that will serve as an artificial identifier for each row. If you do this, one common best practice is to never show that artificial key on any data screen - it has no meaning whatsoever to the users of your system - so don't even show it to them.

Checking these requirements, and a lot of experience, will help you find the right primary key.

It really depends on the data itself. You need to determine what fields can be used to identify the record uniquely.

In SQL server it'll have a key next to it. It's typically ID or something with ID in it. It's also unique and typically increments. When you look at it in SQL. Server management studio under table design you'll see it towards the top of the list of columns with the Lil key icon.

It's a unique identifier that deciphers each record from one another. Kind of like how each person has a ssn.