Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 2 '11 at 21:06

This question came from our site for professional and enthusiast programmers.

1  
Check out these two Google Search trends: goo.gl/5u4XdR. –  igordcard Jan 14 at 18:38
add comment

5 Answers 5

up vote 20 down vote accepted

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.

share|improve this answer
    
Enough. Thank you. –  igordcard Jun 2 '11 at 21:05
1  
"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
add comment

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

share|improve this answer
1  
No source, no reference, no link, no nothing… bad answer. –  FX Jun 2 '11 at 19:36
    
    
google.com/search?q=sql+composite+key+definition will just bring you to a zillion of sites talking about SQL Composite Keys –  Hemme Jun 2 '11 at 19:41
1  
so will a google search for compound key –  jcolebrand Jun 2 '11 at 20:16
2  
This is scientific: googlefight.com/… –  Hemme Jun 2 '11 at 20:32
show 1 more comment

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.

share|improve this answer
4  
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. –  sqlvogel Jun 2 '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 2 '11 at 20:48
    
This was consulted. However, it isn't clear enough. I raised this question because of the blurriness and opposite opinions/definitions on the web. Yeah let's go to dba. –  igordcard Jun 2 '11 at 21:00
1  
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. –  sqlvogel Jun 2 '11 at 21:06
2  
"Key" is just short for "candidate key". The textbook definition of a candidate key is and always has been a "minimal superkey". References: Date's Dictionary pg17 or The Alice Book if you are in any doubt about the definition of a key. –  sqlvogel Jun 2 '11 at 21:15
show 2 more comments

A composite key consists of more than one attribute to uniquely identify an entity occurrence. This differs from a compound key in that one or more of the attributes, which make up the key, are not simple keys in their own right.

For example, you have a database holding your CD collection. One of the entities is called tracks, which holds details of the tracks on a CD. This has a composite key of CD name, track number.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.