Find solution
What is a database engine?

What if we don't provide any primary key explicitly in database table? Then how query would be evaluated by the DBMS engine?

  • What part of DBMS decide which column or columns to consider as primary key if we don't provide?

  • Answer:

    I'll answer for MySQL's InnoDB storage engine, someone else will have to answer for Oracle. These are the two you tag in your question. InnoDB will use the first UNIQUE key on NOT NULL columns as the clustered index if you don't define a PRIMARY key. If you define neither UNIQUE nor PRIMARY key, then InnoDB will create an extra 6-byte integer column as the clustered index. You cannot reference this extra column in SQL queries. See http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

Bill Karwin at Quora Visit the source

Was this solution helpful to you?

Other answers

The answer to a question like this is "it depends". To know for sure you would have to examine the execution plan. By not telling the optimizer that a column ( or group of columns) is a primary key, you are robbing it of the ability to make some query optimizations or transformations. For example, if you have a query of the following shape Select * from t1 where c1 in ( select c2 from t2 ) if you omit the fact that c2 is a primary key, the optimizer may use a hash semi join vs a hash join if it knows that c2 is a primary key.

Bob Carlin

SQL Server is similar it adds a "uniquifier".  http://www.mssqltips.com/sqlservertip/2082/understanding-and-examining-the-uniquifier-in-sql-server/ has a decent  explanation.

Greg Moore

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.