Composite primary keys

[RainLover]

dportas may have chosen to leave this discussion. However, I would prefer to explain these differences of opinions that have been raised.

I would prefer not to get off the main point by discussing other points such as Surrogate V Natural, Trade offs etc.

I will refer to any who may be reading this to my original statement in Post # 10 where I said.

Why would you use 2 or 3 or 4 or more fields to create a Primary Key when you can do it with just One.

After some debate my opinion has not changed.

I have experienced first hand the result of many fields combined to create a Primary Key. If you have not seen this then may I suggest that you create a Database where all Tables have a Composite Primary Key of, say for example 4 Fields. Create a dozen or more tables. Finally go to the Relationship Window and create the joins. The complexity of it all is such that it is at least difficult to understand if not near on impossible.

Now do the same with a single field as the primary key. Much clearer and easy to follow.

I digress so back to the point.

I refer to my post # 21 where I said

An AutoNumber is unique.

Client Number together with Invoice Number is unique (Using a unique Index) then it follows that the Primary Key can only refer to the unique Index.

This was challenged so I shall explain.

For this example I will use UPPER CASE for CLIENTS and lower case for invoices.

A a
A b
A c
B a
B b
B c
C a
C b
C c

All of these are unique and can be enforced by creating a Unique Index so that A a etc cannot be repeated.

A Single Primary Key is also unique as per the rules of Access.

1
2
3
4
5
6
7
8
9

These are all unique.

Now to complete the picture create a table with a Primary Key of Autonumber and a Unique Index of CLIENT and invoice. We end up with the following.

1 A a
2 A b
3 A c
4 B a
5 B b
6 B c
7 C a
8 C b
9 C c

It is not possible that 7 could refer to anything other than "C a". 7 Cannot be duplicated and "C a" cannot be duplicated.

These facts cannot be disputed.

Therefore, there is no advantage in using Composite Primary Keys. If you choose to do so then that is your option. However, to attempt to prove that a Composite Primary Key has an advantage over a single Primary Key with a unique Index is futile and incorrect.

The advantage of a Single Field as Primary Key is simplicity and clarity.

 

[dportas]

Now to complete the picture create a table with a Primary Key of Autonumber and a Unique Index of CLIENT and invoice.

To rephrase in more conventional database design terms: this is a table with two keys, one simple key and one composite key. The purpose of the keys is data integrity. This is a point that I think Gemma, Pat and I have already made in different ways. Referring to client and invoice as a "unique index" doesn't make it any less of a composite key.

It is not possible that 7 could refer to anything other than "C a".

Certainly it is possible because any of those values could be changed. Depending on which of the keys you choose to reference in other places you will set up different kinds of dependency and get different results if one of those values changes. I will say something more about this in a moment.

Therefore, there is no advantage in using Composite Primary Keys. If you choose to do so then that is your option. However, to attempt to prove that a Composite Primary Key has an advantage over a single Primary Key with a unique Index is futile and incorrect.

Pat already explained some of the potential disadvantages of maintaining two keys instead of one. Query complexity may be greatly increased if you reference only the surrogate key rather than the business key in other tables and performance could also suffer as a result. If the surrogate key is never referenced elsewhere then it is just dead weight - incurring additional maintenance and insert performance cost while achieving nothing useful.


There is at least one other possible disadvantage. As I already mentioned, having two keys implies a different set of dependencies than just having one key. Most DBMSs unfortunately don't make it easy to enforce constraints between multiple tables (other than referential integrity constraints). So if you create additional keys and reference only those with foreign keys in other tables then you make it harder or perhaps impossible to implement some data integrity constraints.

To take the example I used before. If a Payment table has a foreign key that references a surrogate key in the Invoice table instead of {VendorNumber, InvoiceNumber} then it isn't possible to implement other constraints on VendorNumber, InvoiceNumber in the Payment table (meaning constraints that also involve other attributes from the Payment table). Of course you don't have to reference the surrogate key in the Invoice table just because it is there. You could make the foreign key reference the composite key instead - but if you find you need to do that in each and every referencing table anyway then the surrogate key is likely to be of no use at all.

 

[RainLover]

Quote:
Originally Posted by RainLover
Now to complete the picture create a table with a Primary Key of Autonumber and a Unique Index of CLIENT and invoice.

To rephrase in more conventional database design terms: this is a table with two keys, one simple key and one composite key. The purpose of the keys is data integrity. This is a point that I think Gemma, Pat and I have already made in different ways. Referring to client and invoice as a "unique index" doesn't make it any less of a composite key.

Here are two links from Microsoft. Both of which refer to the one Primary Key.

http://office.microsoft.com/en-us/access-help/about-primary-keys-mdb-HP005188337.aspx
http://office.microsoft.com/en-us/access-help/CH006364902.aspx?CTT=97

Microsoft Access does not allow for more than one Primary Key.

The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.), forms (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.), and reports (report: An Access database object that you can print, which contains information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.). In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) values from being entered in the primary key fields.
There are three kinds of primary keys that can be defined in Microsoft Access:
AutoNumber primary keys
An AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don't set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key.
AutoNumber primary keys in a replicated database
There are additional considerations if your table will be used with database replication (database replication: The process of creating two or more special copies (replicas) of an Access database. Replicas can be synchronized, changes made to data in one replica, or design changes made in the Design Master, are sent to other replicas.).

• If fewer than 100 records are routinely added between synchronizing replicas (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.), use a Long Integer setting for the FieldSize property to take up less disk space.

If more than 100 records are routinely added between synchronizing replicas, you should use Replication ID for the FieldSize property setting to prevent records from being assigned the same primary key value in each replica. Note, however, that an AutoNumber field with a Replication ID field size produces a 128-bit value that will require more disk space.


Single-field primary keys
If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. You can specify a primary key for a field that already contains data as long as that field does not contain duplicate values or Null values.

Multiple-field primary keys
In situations where you can't guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. The most common situation where this arises is in the table used to relate two other tables in a many-to-many relationship (many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.). For example, an Order Details table can relate the Orders and Products tables. Its primary key consists of two fields: OrderID and ProductID. The Order Details table can list many products and many orders, but each product can only be listed once per order, so combining the OrderID and ProductID fields produces an appropriate primary key.

Please show me where Microsoft allows the use of more than one Key.

 

[Galaxiom]

Why would you use 2 or 3 or 4 or more fields to create a Primary Key when you can do it with just One.

Namely Autonumber.

I never use anything else nor would I recommend it.

If you have multiple Fields to create a Primary Key you will then need to use the same multiple Fields to create the Foreign Key.

Then to take it further imagine a query with 3 or 4 Tables each with Composite Primary keys. What a mess.

Few practices lauded as "never" in database design actually turn out to be so universally unacceptable as portrayed by the detractor. The most extreme I have encountered was a highly respected advice-publishing developer who insisted that all tables should include an autonumber primary key. It is simply not true.

All decisions should be considered in context. If there is one universal truth in database design would be "don't blindly adopt something because you have been told it is the only way to do it".

I have argued the benefits of composite natural keys on this forum before and assure you there can be good reasons to use them over an autonumber.

Like Rain many of the detractors baulk at the concept of having two or more fields to make the foreign key in the related tables and see this as inefficient.

However carrying both fields can sometimes be a benefit because a query of one of the fields may be sufficient to return the required data from a single table where a synthetic key may need a join with another table to determine the records which match the field you are actually interested in.

A natural composite key includes an index. In many cases the field combination would still require an index to prevent duplicates even if the synthetic key was included too. Consequently the design with the autonumbered synthetic key actually includes a redundent index with the ensuing overheads.

 

[RainLover]

Originally Posted by RainLover
It is not possible that 7 could refer to anything other than "C a".
Certainly it is possible because any of those values could be changed. Depending on which of the keys you choose to reference in other places you will set up different kinds of dependency and get different results if one of those values changes. I will say something more about this in a moment.

You are really getting desperate here. Of course if something changes then it can't be the same.

This is like saying that if my Uncle Bob shaved off his beard he would be my Aunty.

Just because I am shooting your arguments down in flames there is no need to treat me with distain.

By the way.

Both Pat and Gemma are quite capable of arguing their point and have done so to me in the past and I am sure they will in the future.

Particularly with Pat. I have a great deal of respect for what she says. She has not written that I am wrong. She may have differing ideas to me but that happens all the time. When she says I am wrong I will take notice.

 

[Galaxiom]

If you have not seen this then may I suggest that you create a Database where all Tables have a Composite Primary Key of, say for example 4 Fields. Create a dozen or more tables.

But would that be a realistic databse scenario? No. If you disagree, please describe a real world data model where a dozen tables would need to be linked with a composite key made from four fields.

Presenting an extreme case does not prove that the typical case is unviable.

 

[dportas]

Microsoft Access does not allow for more than one Primary Key.

Please show me where Microsoft allows the use of more than one Key.

Every DBMS that I know of allows you to implement multiple keys per table as you have already demonstrated in your example. A primary key is no different from any other candidate key (unless you choose to make it so). As I hope you know, in the relational model every table has at least one key but quite often requires more than one. When a table has more than one key then the choice of a "primary" key is essentially arbitrary and only as important as you want it to be. This is fundamental stuff but here isn't really the place to explain basic principles. There are plenty of books on the relational model and database design.

About the wisdom of using composite keys Pat says "maybe" and Galaxiom says "Few practices lauded as "never" in database design actually turn out to be so universally unacceptable". I very much agree with what they and Gemma have said about the value of composite keys. "It depends" is the best advice. Choose keys depending on the data integrity requirements and other requirements of each specific scenario. Don't choose them based on dogmatic rules that a simple key is somehow always preferable to a composite.

 

[RainLover]

Galaxiom

I did present a case that was not typical. But I have seen situations where Composite keys have been made up of more than 4 Fields.

I do think it a good practice to program for the possible rather than for the unlikely event. If it is possible for something to happen then the Programmer should take steps to prevent it. That is if it is going to cause problems.

Presenting an extreme case does not prove that the typical case is unviable.

That is a reasonable statement.

 

[RainLover]

dportas.

Every DBMS that I know of allows you to implement multiple keys per table as you have already demonstrated in your example

I have never said, demonstrated or hinted that you can have multiple keys.

Please do not misquote me in order to better your argument.

I have said that a Key can be made up of multiple fields, but this is a Key, singular, not more than one.

I have also said that Access does not allow multiple primary keys. This is different from a key made up of multiple fields.

I have quoted Microsoft and included a link that supports my statement.

You however claim that you can have Multiple Keys. That is more than one key be they a single field or a composition of more than one field.

You have failed to show one piece of evidence to support your statement.

Could you please post your proof.

And please refrain from misquoting me.

 

[Galaxiom]

Rain, you have backed yourself into a corner. Of course a table can have more than one key.

Think about it. Primary Key. Does this name not imply the possiblity of a Secondary Key?

For example it is quite possible and even typical for a Customer table to use both an Autonumbered Primary Key and a Customer Number field. Both contain unique values and either could be used a key to select a unique record.

A table can only have one Primary Key but that does not preclude other keys.

 

[RainLover]

Galaxiom

Do you realise what you have said here.

However carrying both fields can sometimes be a benefit because a query of one of the fields may be sufficient to return the required data from a single table where a synthetic key may need a join with another table to determine the records which match the field you are actually interested in.

Surely you realise that this can be done with any two tables. Even a table with NO Keys.

 

[Galaxiom]

I have quoted Microsoft and included a link that supports my statement.

Microsoft is not an absolute authority, sometimes not even a mediocre one. Their advice is only as good as their staff. I have seem seriously ridiculous code offered by them.

Here is my favourite example. See if you can spot what I am talking about.

http://support.microsoft.com/kb/892490

I first saw it in some code posted by a user on this forum. I wondered what they were on. Only later when I came across it at this link did I realise they got it direct from Microsoft.

 

[RainLover]

Rain, you have backed yourself into a corner. Of course a table can have more than one key.

Think about it. Primary Key. Does this name not imply the possiblity of a Secondary Key?

For example it is quite possible and even typical for a Customer table to use both an Autonumbered Primary Key and a Customer Number field. Both contain unique values and either could be used a key to select a unique record.

A table can only have one Primary Key but that does not preclude other keys.

I don't believe so.

A Primary Key could imply a Secondary Key but in Access speak it does imply a Foreign Key.

A customer Number field in your example may be Unique, it could be indexed or it could be a unique index. It could also be used as a join to to another table but it is not a Primary Key, nor is it a Key of any kind.

 

[dportas]

And please refrain from misquoting me.

I don't think I misquoted you at all, I merely commented on the table structure you sketched earlier.

Seriously though, if you are denying the possibility that a table can have more than one key then I don't think this discussion is going to go anywhere. As Galaxiom says, you have backed yourself (and the rest of us) into a corner. I'm not going to spend a lot of time digging up references about database fundamentals but you can find plenty of books that cover this stuff. Two of my long-standing recommendations are: Information Modeling and Relational Databases by Terry Halpin; Fabian Pascal's Practical Issues in Database Management. Right now I'll point you to a fine introductory article about keys by Hugh Darwen, who is a much better teacher than I'll ever be:

Also in general, a relvar can have several keys, but we choose just one for underlining and call that one the primary key. The choice is arbitrary, so the concept of primary is not really very important from a logical point of view. The general concept of key, however, is very important! The term candidate key means exactly the same as key (i.e., the addition of candidate has no real significance—it was proposed by Ted Codd because he regarded each key as a candidate for being nominated as the primary key).

http://www.dcs.warwick.ac.uk/~hugh/M359/What-Is-a-Key.html

 

[RainLover]

Here is my favourite example. See if you can spot what I am talking about.

I really can't comment.

I failed at SQL Server.

I use MySQL when necessary.

Perhaps you could explain.

 

[RainLover]

dportas

You have misquoted me, either that or you have put words into my mouth.

I have posted what Microsoft have said so there is no need for me to search for something that does not exist.

And you won't search because you also know that it does not exist.

Again there is a difference between a Primary Key and a unique index but you are either unwilling or unable to understand.

And for the record you have not proven me to be incorrect in anything I have written. The best you could do was to post your opinion and the opinion of others without credible evidence.

 

[Galaxiom]

Surely you realise that this can be done with any two tables. Even a table with NO Keys.

It appears my description was not clear. A typical example using a composite key will illustrate the point.

Take a table with fields including, say, EmployeeID and WorkDate. These two fields can form a composite key because in our data model there is only one record for each employee per day.

A one-to-many related table containing further information about the employee activities on that day will contain both these fields as the composite foreigh key. This table can be independently queried to return aggregates by EmployeeID and/or WorkDate.

The alternative structure with a synthetic autonumber field as the key still needs the composite index to ensure data integrity is maintained with a single record for each EmployeeID/WorkDay, plus it requires the autonumber with its own separate indexing.

More importantly the related table contains only the autonumber field to indicate the associated record in the first table. Any query requiring information about either the EmployeeID or the WorkDate can only be determined with a join on the autonumber field.

 

[RainLover]

Galaxiom

I am not sure if I read you right or not.

But a Table with AutoNumber as Primary Key and EmployeeId/WorkDay as a Unique Index does the same thing as your example.

The only difference is that I use a Single Primary Key (AutoNumber) and you use a Composite Primary Key (EmployeeID/WorkDay)

What I believe is been misunderstood is that the Composite is a Key. It is not two keys. It uses the Index as its unique reference.

 

[rodmc]

I always miss the good fights

 

[gemma-the-husky]

rain

it may be that this is just about semantics.

maybe i/we are misunderstanding - you seem to be saying that an autonumber key is as good as any other key to determine the uniqueness of a record

i think the point at issue is that an autonumber key is certainly good enough to maintain relatedness between items in tables - and very usefully is also rather more efficient in use than a composite key - but of itself it is insufficient to guarantee the integrity of the data - whereas a correctly chosen composite key is by definition selected in order to maintain integrity.

is that just semantics?