Can SQL table have multiple columns with primary key? [duplicate]

This question already has answers here: How can I define a composite primary key in SQL? (4 answers) Closed 3 years ago.

Can SQL table have开发者_运维问答 multiple columns with primary key?

---

A table can have just one primary key constraint, but that may be comprised of several columns e.g.

create table my_table (col1 integer, col2 integer, col3 integer,
   primary key (col1, col2, col3)
   );

In addition to the primary key, a table may also have one or more UNIQUE constraints, e.g.

create table my_table2 (col1 integer, col2 integer, col3 integer,
   primary key (col1, col2),
   unique (col2, col3)
   );

---

If you mean "can a primary key in SQL have multiple columns", the answer is yes.

---

If you're asking if a table can have multiple columns as a primary key, then for MS SQL Server, the answer is yes, and it's called a composite (corrected) key.

---

"Usually a composite key is a poor practice."

Beware of the false prophets who try to sell you such crap.

A key is a key is a key is a key. A key IS a set of attributes. Nothing more and nothing less. The cardinality of that set can be 1, or it can be >1, and it can EVEN BE ZERO ! And a key corresponds, one-on-one, to some uniqueness constraint.

The relational model HAS NO PRESCRIPTION WHAT SO EVER that a key/uniqueness constraint can only involve a single attribute.

Moreover, nor does the relational model have any prosription what so ever against there being more than one key, and it is EVEN a fact that relational theory has ditched the concept (for decades already) of "primary key" (implying that such a "primary" key would in any sense be "more of a key" than are the others), because completely unnecessary and irrelevant. As far as the uniqueness the keys imply is concerned, ALL KEYS ARE EQUAL (and it is NOT the case the one particular key is more equal than the others).

---

Usually a composite key is a poor practice. It will cause things to be slower when you need to join to it. It is also harder when you need to update one or more of the fields in 27 child tables. A better practice is a surrogate key and a unique index on the fields that would normally make up the composite key. Then you have the speed of the integer join and the the unique attribute is maintained and when the key value changes (as it often does in a composite key), then you only have to change one table instead of all the child tables.

There is one place where I will use a composite key though and that isa mapping table that is used to create the realtionships for a many-to-many relationship. In this case you typically only have two columns and both are usually integers which normally do not change. Then I will usually use a composite key as this particular case does not have the disadvatages a composite has in a normal table.