Problem solve Get help with specific problems with your technologies, process and projects.

Is it necessary for a table always to have a primary key?

In a certain table, it is not neccessary for a table to have a primary key always, is it? In other words, a table...

might have only foreign keys only, not primary key. Does it happen?

Yes, it is quite possible to have a table without a PK (Primary Key), but it is normally undesirable for a number of reasons.

First and foremost, many database engines use the declared PK for internal purposes. on the Z series does some intriguing things to make tables without a declared PK work properly. DB2/390 has problems with a table without a declared PK, but it copes better because it isn't so dependent on it. Oracle on the Novel platform, DB2/UDB, and MS-SQL work well enough, but are mildly handicapped by the lack of a declared PK. Oracle on the Sun platform actually works slightly better without a declared PK than it does with one, but it is the exception to the rule.

Second, and probably more importantly, many of the GUI development tools don't like tables without a declared PK. Because there is no guaranteed unique way to locate a row, they have to either refuse to allow the GUI tools to manipulate that table, or they need to somehow derive something else they can use in place of a PK.

A distant third problem to most users, but quite near and dear to my heart is the fact that many of the tools that help a DBA do their job refuse to work on tables without a declared PK. You can certainly work without these tools, but you'll do a lot more manual work than is necessary to get your job done. I don't have the time to do things the hard way, when an easy way is readily accessible!

The short answer is: Yes, you can have a table without a declared PK, but I won't!

For More Information

Dig Deeper on Oracle database design and architecture