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.
2
-
3Check out these two Google Search trends: goo.gl/5u4XdR.– igordcardJan 14, 2014 at 18:38
-
This is interesting though trends.google.com/trends/… I also checked for "oracle" as I worked for an Oracle partner for 17 years, but Compound didn't show up at all– J. GwinnerJul 17, 2023 at 1:00
Add a comment
|
5 Answers
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.
-
6"In ER modelling the term "compound key" also has a more specific meaning" -- Sounds convincing :) But do you have a citation? Apr 13, 2012 at 12:26
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.
answered Jun 2, 2011 at 20:21
-
5jcolebrand: "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.– nvogelJun 2, 2011 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, 2011 at 20:48
-
2I 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.– nvogelJun 2, 2011 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 2, 2011 at 21:07
-
2The 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.) Jan 7, 2015 at 23:56
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.
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
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
