10

Possible Duplicate:
Should each and every table have a primary key?

If you never refer to the ID anywhere is it necessary to include one? Does a table need an ID or primary key?

Community
  • 1
  • 1
John Smith
  • 8,567
  • 13
  • 51
  • 74
  • What table engine are you using? (MyISAM, InnoDB, ...) It actually makes a difference, with MySQL. – T.J. Crowder Mar 03 '11 at 22:59
  • This is not a duplicate of that question because this one is asking about the "ID" and not just a primary key. But this should be made more clear that these are different from each other. – Mark Dec 04 '14 at 05:32
  • I found this answer that made more sense to me if looking into that part of the question: http://stackoverflow.com/questions/8279474/mysql-unique-id-or-combined-id?rq=1 – Mark Dec 04 '14 at 05:33

6 Answers6

13

No you do not need a primary key to make a table work in MySQL. That said, a primary key allows for a unique value to refer to a row in a table from another table, or in any code using the table.

You do need a primary key to make a table work well in MySQL though. Indexes (which the primary key is one of) allow MySQL to search through small, highly optimized subsets of the table to process relationships and searches. In general, any fields that you use in a WHERE clause or use to link two tables together should be indexed.

Matthew Scharley
  • 127,823
  • 52
  • 194
  • 222
  • 13
    You've argued for *indexes*, not necessarily an (artificial) *ID* column. – T.J. Crowder Mar 03 '11 at 23:01
  • @T.J. Crowder: The primary key is an index. It is nothing more than a unique index that has the semantic of being a unique identifier for a column (though, any unique index has this property). If you're indexing other columns and don't have a primary key, then you are likely doing something wrong as MySQL doesn't have eg. Postgres's object ID's to be able to refer to rows in the table. – Matthew Scharley Mar 03 '11 at 23:06
  • Also, my first paragraph does address the issue of primary keys in particular. You are right that the rest is about index's in general though. – Matthew Scharley Mar 03 '11 at 23:07
  • *"If you're indexing other columns and don't have a primary key, then you are likely doing something wrong as MySQL doesn't have eg. Postgres's object ID's to be able to refer to rows in the table."* See my comment in reply to that point on another answer. For at least the MyISAM engine, I'm fairly sure it *does* -- the file position. – T.J. Crowder Mar 03 '11 at 23:14
  • Anyway, I'll leave it there. You almost certainly know more about DB design than I do, whether or not specifically for some obscure storage engine; and in any case, I've said my piece -- and almost seem to be arguing against PKs, which is not my intent at all. :-) – T.J. Crowder Mar 03 '11 at 23:18
6

Actually, InnoDB uses its own row id as PK for the table in case you didn't create one, so it can use it for indexing. And that has some negative effects on performance.

See a very good explanation here: http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/

To sum it up, there are 3 rules:

  1. Do explicitly define a primary key, and make it numeric.
  2. The primary key should be sequential.
  3. The primary key should be as small as possible.

As a side note: some SQL editors and tools may have issues if there is no PK on a table.

When you are manually editing result sets or table data in such a tool, the tool runs an UPDATE command. In case there is no unique key, several identical records may be inserted, and then there is no way to update only one/some of them. In an SQL editor you can manually edit one of those records, but when the update command is sent to the mysql - it will either fail, or update all identical records instead of that one record.

Galz
  • 6,713
  • 4
  • 33
  • 39
  • 1
    @Patrick - Thanks. here is a similar one that's still alive: http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/ – Galz May 30 '14 at 22:24
3

By default MySQL (InnoDB engine) uses primary key to determine the order in which the data is physically stored in the main data file. If there is no primary key MySQL will automatically add a hidden AUTOINCREMENT column to act as pkey. This might cause performance issues because during inserting the unique autoincrement value acts like a global lock for all inserts. Also primary keys are used to associate all of a table’s indexes with the main data file. So the primary key is replicated in every row of every index.

criticus
  • 1,571
  • 8
  • 15
0

ID is not a requirement, and neither is primary key, but it is a fundamental concept of the relational database model. You wouldn't usually want to try and use name as a key, for example.

also found : ID fields in SQL tables: rule or law?

Community
  • 1
  • 1
Brandon Frohbieter
  • 17,563
  • 3
  • 40
  • 62
0

Its not a REQUIREMENT, but if you may ever in the future reference any data in this table, its a very good idea to have one as a primary key.

rayman86
  • 1,385
  • 10
  • 9
  • Care to back that up with *why*? Maybe a reference or two? Different RDBMS's work differently... – T.J. Crowder Mar 03 '11 at 22:59
  • @T.J. Crowder: MySQL doesn't have row id's outside whatever is in the table like eg. Postres does. The only unique identifier in a MySQL table is any unique index/primary key in the table. – Matthew Scharley Mar 03 '11 at 23:05
  • Mainly its good design: http://www.opensourcetutorials.com/tutorials/Databases/PostgreSQL/practical-database-design-part-1/page2.html is one such link. Attempting to reference a table on data that may change (even if you think it is unlikely) can cause some referential errors in the tables that may link to the table. Using an id as a primary key (preferrably assigned by the dbms, auto-increment for easy use) is a way to assign a value that never changes, allowing the subsequent data to be changed without causing these referential errors. – rayman86 Mar 03 '11 at 23:05
  • http://en.wikipedia.org/wiki/Database_normalization another good link on the error types and normalization – rayman86 Mar 03 '11 at 23:06
  • @Matthew: Again, it depends on the table engine. MyISAM and InnoDB are quite different. I can't speak for InnoDB, but I think MyISAM works perfectly happily without a PK -- just index the columns you're actually going to query, and it uses the file position of the rows. I *think*. (Best practice? Well, unless the engine needs it, there's not much point adding a column you're never going to query.) – T.J. Crowder Mar 03 '11 at 23:11
  • I know for the design part it is very nice to have. Had to go in a project that someone else had not set one up and then added onto, referencing the userid that a user got to pick. It was very annoying having to go back in and create the id field and then changing all the scripts once the owners of the site wanted to be able to change usernames. – rayman86 Mar 03 '11 at 23:14
-1

By "ID", I think you mean "identifier" - something that uniquely identifies a row in a database table. They are also called keys, which is the more concise technical term used in database design.

Implementing keys achieves at least two important things: keys prevent redundant and therefore potentially anomalous data from getting into your table; keys ensure that consumers of the data can accurately identify, use and update the information in the table. For these reasons, yes, you ought to create keys in your tables.

nvogel
  • 24,981
  • 1
  • 44
  • 82