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

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

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Database Design Web Links: tips, tutorials, scripts, and more.
• Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.