Must a Table have a primary key?

Question

To be correct does every table in a database need to have a primary key?

I have a table in which I have two foreign keys in but not primary keys, it would still work fine but do I need to create a unique identifier (primary key) for this table?

Answer 1

you don't need a primary key in a table, however if you expect to use the table in a relational context then you probably need a primary key. the selection of keys can be problematical. if you expect ot haev to update the table then you will need a mechanism to uniquely identify that row.



out of curiostiy you mention you have to FK's, would a composite made of the those FK's be unique... if so it sounds like you have your candidate for a primary key. its quite a common construct to use the FK's to to other tables, this models the many to many logical model or a so called intersection table.



there is no point in adding an autonumber column if there is an suitable other key even a composite key. autonubers are so calle dsurrogate keys, you only use them when you have to (either there isn't a candidate natural key or the canidate key is in appropriate

Answer 2

Best practice rules suggest that tables need a primary key or at least a unique key. Although, nothing in the world can prevent you from not making a primary key for a table.



For tables with no primary key or unique keys you may encounter a few problems:

1. how can you identify records. You must be able to distinguish one record from another in order to run the correct SQL statement.

2. If your table has millions of records, your select statement to look for a particular record will take forever to run since you are not using a primary key.



But if your table contains only 1 record. There is no point in making a primary key. Although, again, it is good practice to always have a primary key for a table.

Answer 3

Primary Key selection: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person). So, select only keys that are UNIQUE. courses table: course_no char(3) is the primary key Although there is a certain temptation to use course_name char(30) its should be avoided. Consider what if the course name is changed due to curriculum? faculty table: fac_no integer is the best option fac_lastname or fact_firstname are NOT primary keys. People can have same names.

Answer 4

A primary key is not normally mandatory, though it is usually recommended. If the amount of data on a table is very small a primary key will not improve performance as the whole table will be read every time it is accessed. However with most normal size tables a primary key will make read performance quicker.

Answer 5

Hi

It is not necessary that table must have a primary key.



It is depend your data.



If you use unique number then must have primary key.

Answer 6

If you are creating a proper relational database and wish to link them, as they should be to avoid duplication and speed of access, then, say for instance you are doing a customer data base and you have the primary field in the main table, of customer names, addresses etc. of Customer ID or No. then you have the same field in other tables you wish to link with but it is not necessary to set it as a primary field which would in any case be foreign candidate key but say you are linking customer table with sales table through customer id, you would link sales table with stock table through Sales ID, and the stock table would be linked perhaps to supplier table through Supplier ID.

Answer 7

Yes! If you can't think of which to choose, Access does it for you. But if you do choose it has to be a field which is always different e.g. AutoNumber. Cheers

Answer 8

the best practice is typically to have a pk on each table; your solution of adding a uid column is the approach i would take. IF you chose to utilize the pk as a reference is up to you