Can SQL table have multiple columns with primary key?
0
Add a comment
|
5 Answers
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)
);
answered Nov 17, 2009 at 17:13
-
Although a table can only have one primary key, (just by definition) it can have any number of keys, any one of which could be designated as the primary key. (The others will then all be refered to as 'alternate' keys) Commented Nov 17, 2009 at 17:27
-
1Interesting..so if a unique key is over multiple columns does that mean that col2.col3 are unique (together) OR does it mean col2 is unique and col3 is unique?– MarkCommented Nov 17, 2009 at 18:21
-
3Together. Unique(col2) and unique(col3) would be 2 seperate constraints. Commented Nov 17, 2009 at 19:08
If you mean "can a primary key in SQL have multiple columns", the answer is yes.
-
-
6Another good question is SHOULD a primary key be multiple columns. :) Commented Nov 17, 2009 at 17:31
-
7Well, that's a different question, and the answer is NO, NOT EVER, AND IF YOU DO I WILL HUNT YOU DOWN AND HURT YOU. Just a personal preference, of course :-)– StuCommented Dec 4, 2009 at 16:46
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).
answered Nov 17, 2009 at 18:59
-
1-1. No, not all keys are equal... Composite keys are slower for foreign Key lookups than single column keys. And a "natural" key (based on true domain model data definition) is intrinsically different from a meaningless surrogate key (it is not the same at all in anything but meaningless table row value uniqueness). Use both when both are required. Commented Nov 17, 2009 at 21:54
-
3I don't know if I should upvote or downvote. I agree with the first part, but I disagree that all keys are equal. Primary keys by definition are required to be unique. Foreign keys can be null, used once (one-to-one relationship) or repeating (whatever the database design calls for). Commented Oct 27, 2012 at 21:05
-
4All keys that define uniqueness are equally unique. Foreign keys are not keys that define uniqueness. I was not talking of those. It was a huge mistake to call that concept a "key". Naturally, if you use composite keys for some relvar/table/entity, and in another relvar/table/entity you have to "reference" the former, then there will need to be a composite foreign key in that 'other' relvar/table/entity. Commented Oct 28, 2012 at 9:56
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 then 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 is a mapping table that is used to create the relationships 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 disadvantages a composite has in a normal table.
-
2To make a fine point, (but an important one I think) Keys serve two distinct purposes. 1. A Unique key in a table ensures data consistency within the table (no logically duplicate rows). To that end, if the "natural key" is composite, then the key is composite. end of story. No other 'simpler' key will ensure data consistency. But this function has NO impact on performance, so there's no performance impact from using the correct, composite key. Commented Nov 17, 2009 at 21:48
-
12. Secondly, Unique Keys (Prim or alternate) are used as targets for foreign keys in related tables to ensure that Foerign Key values exist. For this purpose, the shorter the key, the better the performance. so using a non-natural (or surrogate) key which contains no meaning, to accomplish this second purpose increases performance. So why not use both where both needs exist and the natural key is composite, or too long, to be perforant ?? Commented Nov 17, 2009 at 21:50
-
2Next time try to answer a question that was asked. The question was "Can SQL table have multiple columns with primary key?" not "Is it a good idea to have a composite key?"– WodzuCommented Jan 20, 2021 at 9:40