CODEWITHSUNDEEP
mysqlprimary-key
PFranchise
PFranchise

Reputation: 6752

Getting underlying Primary Key in MySQL (InnoDB)

It is my understanding that when I make a table without a primary key that MySQL creates a sort of underlying primary key that it uses internally.

I am working with a table that does not have a primary key, but it would be very useful for my application if I could somehow access this value, assuming it does in fact exist and is retrievable.

So, I am wanting to know if I am correct in believing that such a value exists somewhere and also if it is possible to get that value.

Edit: just to make it clear, it would be very useful for my application for this table to have an incrementing int attribute. Unfortunately, it was not implemented that way. So, I am sort of grasping at straws to find a solution. What I am trying to do is select every nth row in the table (n changes). So, as you can see if there was this key, this would be very simple.

Upvotes: 0

Views: 196

Answers (1)

GordonM
GordonM

Reputation: 31730

If a table has no primary key then there's no way of specifying a specific row within it because there is no way to uniquely identify an item. Even if you use a query that specifies a specific value for every column that still wouldn't be certain to only return a single row as without a primary key there's nothing to prevent duplicate rows.

However, a primary key is simply a unique index. If a table has a unique index on one or more of its columns and those columns don't accept NULLs then this is the primary key for the table in all but name.

If you table has no unique columns then you've got nothing to go on. You'll have to either make one column or combination of columns (for a composite key) unique, or add a column that serves as the primary key for the table. Fortunately it's relatively easy to add columns to a MySQL table, just add a primary key autoincrement column to the existing table.

Upvotes: 3

Related Questions