6 Primary keys in a database

Question

May I please know would I be able to have 6 primary keys in my database project. I am just beginning to create my database project and know of one and two being possible but not sure for 6. Any help please!

Answer 1

What do you mean by multiple primary key? Can you explain? If in a database you are asking more than one primary key then its fine you can take up per table primary key. but in a same table two or more primary keys are not possible.

A table can have only one primary key and more than one unique key, that would be your composite key.

example:-Employee table

     EmpID(Not null)   ManagerID(null)
     001               001
     002               002

here EmpId is your primary key and manager Id is your composite key.

Like this way you can make your table's columns unique.

Answer 2

The moment you say more than one then it becomes a composite key. Technically you can have a composite key with more that one column. But I would really love to know what is such a use case which caters such a requirement to have 6 keys as a composite key in a table.

Answer 3

I'm going to guess you're trying to ask how to have multiple primary keys in a table, not a database. (Strictly speaking, this makes it a composite key).

You can certainly do this. For example, the syntax in mysql is:

CREATE TABLE tbl_name (
  col1 int,
  col2 int,
  col3 int,
  col4 int,
  col5 int,
  col6 int,
  col7 int,
  col8 int,
  col9 int,
  PRIMARY KEY (col1, col2, col3, col4, col5, col6));

Having said that, unless you really know what you're doing this probably isn't what you should be doing. I've certainly worked with very complicated business models that had large composite keys.

(For example, one client had lots of tables with 5 fields in the composite key, for example, representing salesOrganizationNumber, salesRepNumber, locationId, styleNumber, styleType on certain inventory/ordering line items.)

But your question is so basic, I suspect that you should probably ask a much more detailed sample question about how to design a schema and you'll probably find that your primary keys will be much smaller.

Answer 4

You can have as many primary keys in your database as there are tables - you're allowed one primary key per table.

Of course, you can have multiple indexes per table but they're not considered primary keys.

You can also construct a primary key from multiple columns (as many columns as you like), but this is still one primary key, just a composite one.

It's vastly different from multiple primary keys since the former allows composite parts of the key to be duplicated as long as the complete key is unique. The latter (if allowed) would require each part to be unique since that's the property of a primary key.

Answer 5

Yes you can have multiple keys per table and you can have multiple attributes in each key. Each key by definition must be irreducible, which means it contains the minimal set of columns needed to maintain uniqueness of that key.

Based on your comments, I suspect you are actually enquiring about having ONE key with multiple attributes rather than multiple keys. The difference is important and you should understand that. You might want to consider studying a book or taking a course on database design fundamentals to help you answer questions like this one.