In SQL, is it composite or compound keys?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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