If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Table without a primary key.

Hey,

I'm currently scripting this database from MsSQL to MySQL and have come across this table that has no primary key. Now this is just out of curiosity, I don't have a lot of SQL experience but from what I have been thought: Every table needs a primary key.

I guess my question is: Would there be any time or situation where one would need to create a table without a primary key?

I'm currently scripting this database from MsSQL to MySQL and have come across this table that has no primary key. Now this is just out of curiosity, I don't have a lot of SQL experience but from what I have been thought: Every table needs a primary key.

I guess my question is: Would there be any time or situation where one would need to create a table without a primary key?

You can create tables in MySQL without a primary key. You normally only do this when a table is acting as a 'go-between' of two tables and a unique identifier is not required or useful. For example, if a person adds a property to their favourites, then there could be a table which has the following fields - personIDfk, propertyIDfk, date_added - which has the effect of linking the person with the property and no primary key is required or useful.

That makes sense, although the table I have come across holds these fields:

PCName

AppPath

Active

I guess it's used to locate available applications, my guess is this table would need a key if they ever need to add another table giving properties to this one. Maybe they'll never need to but I just think it would be good practice to add a key anyways.

Note that a primary key can be across mutliple columns, so if in this example some combination of 2 or 3 columns should never be duplicated, you might make that combination the primary key (PCName and AppPath, perhaps?).

As stated, you don't need a PK and/or your PK can span columns. However, best practice is to use an auto_inc PK (even if you're using UUIDs) on every table. This is particularly important in web development, particularly because most of your tables will/ought be InnoDB.