39

About SQL (Computing/Databases):

When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys?

I've seen on the web both uses so I'm not really sure.

igordcard
  • 493
  • 1
  • 4
  • 6
  • 3
    Check out these two Google Search trends: http://goo.gl/5u4XdR. – igordcard Jan 14 '14 at 18:38
  • This is interesting though https://trends.google.com/trends/explore?date=all&q=compound%20key,composite%20key I also checked for "oracle" as I worked for an Oracle partner for 17 years, but Compound didn't show up at all – J. Gwinner Jul 17 '23 at 01:00

5 Answers5

40

Both composite key and compound key describe a candidate key with more than one attribute. According to the Relational Database Dictionary (C.J.Date) they mean the same thing.

In ER modelling the term "compound key" also has a more specific meaning. It means a key whose constituent attributes are references to keys in other entities - i.e. a compound key forms an identifying relationship. For most purposes, this isn't an especially useful or important concept so the terms composite/compound are often treated as interchangeable. It's probably best to stick to "composite key" unless you are referring specifically to the ER modelling concept of a compound key.

nvogel
  • 3,777
  • 20
  • 25
  • 6
    "In ER modelling the term "compound key" also has a more specific meaning" -- Sounds convincing :) But do you have a citation? – onedaywhen Apr 13 '12 at 12:26
29

I'm still not sure why http://en.wikipedia.org/wiki/Compound_key was not consulted. It very clearly states (and is correct):

In database design, a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.

This is often confused with a composite key whereby even though this is also a key that consists of 2 or more attributes that uniquely identify an entity occurrence, at least one attribute that makes up the composite key is not a simple key in its own right.

A composite key is made up of elements that may or may not be foreign keys. Example: In a table of Transaction details, the key is (TransactionId, ItemNumber). A transaction detail is a subentity of a transaction. TransactionId is a foreign key, referencing the Transactions table. ItemNumber is not a key in and of itself. It only uniquely identifies an item within the context of a single transaction.

A compound key is a key whereby any part of the key is a foreign key. Example: in an a hotel reservation system, a reservation has the compound key, (GuestId, HotelId, ArrivalDate). GuestId identifies a Guest, and references the Guests table. HotelId identifies a Hotel, and references the Hotels table. ArrivalDate identifies a Date. There may or may not be a Dates table that it references, but it identifies an Entity (a Date) either way.

Also of note is this factoid: A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.

Walter Mitty
  • 4,306
  • 20
  • 21
jcolebrand
  • 6,354
  • 4
  • 42
  • 67
  • 5
    jcolebrand: "A compound key is a key whereby any part of the key could identify the record." That's incorrect. By definition a key must be irreducible (a minimal superkey). If only some of the attributes are required to uniquely identify a tuple then by definition that's a superkey and not a key. – nvogel Jun 02 '11 at 20:39
  • a) I was quoting the wikipedia page. b) I tend to agree with it. Any part of a compound key is itself a simple key. I don't mind if you also call it a superkey. I don't believe that all compound keys can be superkeys, however. Ergo, I stand by what is in the wikipedia article, and I stand by the definition I quoted. I can go and fetch my published hardcover database design book if that would make you happier. – jcolebrand Jun 02 '11 at 20:48
  • 2
    I think you missed the relevant point though, which is that a compound key is made up of keys from other entities. A proper subset of a key can't possibly be a key. As I'm sure you know, a key is required to be minimal (within the table of which it is a key) - so if you remove any attribute from it then it wouldn't be a key any more. – nvogel Jun 02 '11 at 21:06
  • Not true, and I disagree. A candidate key may very well not be a key at all. It's only a key when it's ascertained to be a key. Also noting that candidate keys are often over relationships, not over entities. – jcolebrand Jun 02 '11 at 21:07
  • 2
    The definitions of "compound key" and "compsite key" on the Wikipedia page were changed on 13 Jul 2014, and no longer match the definitions in this answer. (I didn't change the definitions, just pointing out that the definitions don't match now.) – spencer7593 Jan 07 '15 at 23:56
  • I think the original definition of composite key is good, but I've been seeing quite often the assumption is that a composite key is only comprised of simple keys joined together. In that case, if you had a database with last_name and first_name, where neither was a simple key (we have Bob Johnson and Bob Smith), but the combination is unique. You can't do that if the columns within the composite are simple keys. Note that this doesn't mean the keys have to be from other entities. – J. Gwinner Jul 17 '23 at 01:10
3

It sounds to me like a composite key is a superset that includes compound keys. If we accept that a composite key is made of more than one attribute (of any kind), and a compound key is made of more than one simple key. A compound key is a type of composite key with a more specific meaning, but the term "composite" key is always appropriate to use.

Tom Gnade
  • 31
  • 1
2

In English it is "composite keys". For instance, take a look at MSDN website (any search containing "sql composite key" will do).

http://www.google.com/search?q=sql+composite+key+site:msdn.microsoft.com

Assuming that the question regards Relational Databases, I searched for a "neutral" definition in Wikipedia:

A composite key is a key made up of two or more attributes within a table that (together) uniquely identify a record

Hemme
  • 129
  • 1
-3

A compound key is created when 2 or more primary keys from different tables are present as foreign keys within an entity. A composite key is a composition of two or more columns that uniquely identify rows in an entity

user3448324
  • 1
  • 1