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

New or composite primary key for a composite table?

I have a question on database design. For example, I have a STUDENT table and a COURSE table. I know that I have to create a composite table to link the tables together to resolve the many-to-many relationship. Therefore, I created a REGISTRATION table. For the primary key of the REGISTRAION table, is it better to use a sequence number called REGRISTRATION NUMBER as Primary Key or better to use the PKs from the two table, i.e. composite primary key Student Number & Course Number? What will give the better performance & better database design technique, the use of sequence number or composite primary key? Also, which of these techniques will facilitate the use of indexes more?

It is better to use the PKs from the two tables as a composite primary key. This method insures that only valid values are used in the REGISTRATION table. If you used a sequence number, then it would just add extra overhead that is not necessary. Since you will derive the composite key values from existing keys from the other tables, you'll likely have the STUDENT and COURSE tables already queried when you are ready to do REGISTRATIONs. Therefore, the use of a sequence wouldn't really buy you much, if any, time advantage.

As far as which technique facilitates the use of indexes, I'd say it's a toss up. The primary key of a table is automatically indexed, so it really doesn't matter if the PK is generated with a sequence or otherwise.

So the bottom-line is that there may not be a real clear winner performance wise, but design wise, the best choice in my opinion is to go with the composite key.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.