Composite primary keys

[DaveJHPT]

I've noticed that the great and the good all seem to recommend not using composite PKs. Does that even go for join tables in the middle of a many-to-many relationship? Not sure I see what's to be gained by adding an ID and making that the PK.

Any comments?

Dave

 

[Pat Hartman]

I don't add autonumber primary keys to junction tables UNLESS the junction table has one or more child tables. This is a rare case so it doesn't happen often. An example might be a Student-Class junction table that has a child table that contains grades for individual assignments. The reason for adding the autonumber is usually to facilitate the use of combo or list boxes. Neither of these work if you don't hae a single, unique id.

As to hierarchial relationships where each level down, adds a new field to the pk. Again, the problem is with combo and list boxes. I don't have a firm rule on this. It depends on how deep the structure is and whether other tables are related to the lower levels of the hierarchy. Creating relationships on 3 or more fields gets tiresome.

So, I prefer "natural" keys but I frequently use surrogate autonumbers because they make other things easier.

 

[The_Doc_Man]

If the relationship is many-to-many but not repeatable, the two foreign keys in the linking table can become a compound prime key in that linking table. In a normal table, a compound key makes sense only if the table is in some way a child (or "partial" child) of another table. (In other words, one or more of the candidate members of the compound PK are themselves foreign keys into another table.)

For instance, suppose that you are a distributor who makes no products of your own. You have suppliers. The supplier table has a simple PK, maybe only a WORD integer, because you don't have THAT many suppliers.

Each supplier has something they can supply. For each thing they can supply, they have a product number or code. For any single supplier, the code is unique per product. (In other words, with respect to one supplier, the code is a candidate for being a prime key.)

In this case, you could either provide an autonumber for YOUR product table and have supplier ID and supplier's code as non-key elements of your table. OR you can have the supplier's code and the supplier ID number as a composite PK. See discussion below about why this isn't recommended for everyone. This method even works if you make something on your own - but then YOU have to have a supplier ID, too!

You use autonumber PKs in preference to composite PKs when there is a huge size disparity between the resultant keys. Consider, for example, that a supplier's part number is a 10-character string and the supplier ID is at least a number from 1 to 65K (WORD integer). This makes a composite PK of 12 bytes. Whereas a LONG integer autonumber is a 4-byte key. The index that holds these keys must hold 8 more bytes for the composite key than for the autonumber key. The bigger the PK (whether simple or composite), the less you can fit into a single buffer when searching for the matching key. This has a drastic effect on the speed of your search. Also, the bigger the PK, the more space you take up in other tables for which that table's PK is the other table's FK.

OK, having said that, you can still use compound PKs if all of the component fragments are themselves Autonumbered or otherwise short fragments. Just remember, the more members of the composite key, the longer it gets and the less efficient it gets.

There are times when you can positively be sure that a composite key is the right choice ... when all of the component key parts are VERY short such that the whole key is not much longer than a LONG integer. Such times of certainty are, however, woefully rare.

 

[DaveJHPT]

Thank you both - food for thought!

Dave

 

[seneberg]

Composite Key Using AutoNumbers Problem

I'm still somewhat new to this, so I apologize for what is potentially a dumb question! I’m trying to figure out how to get past a composite key problem. There are three tables involved: tbl_Project, tbl_Employee and tbl_Hours (tbl_Hours is the join table for the many-to-many relationship between the other two tables). I’m trying to create a composite key for tbl_Hours using the AutoNumber primary keys from the other two tables, but as you can see from my example below, duplicate values are being created…any suggestions?

tbl_ProjectID tbl_EmployeeID CompositeID for tbl_Hours
1 23 123
12 3 123

 

[seneberg]

Never mind...

Never mind about my composite key question...my problem was that I did infact have one actual duplicate record, which was prohibiting me from setting the composite primary key using the ProjectID and the EmployeeID. I discovered my error by running a 'find duplicates' query.

 

[Pat Hartman]

You aren't mushing the two columns into a single one are you? The proper way to make a compound/composite primary key is to select all the necessary columns by using cntl-click until all columns are highlighted (up to a maximum of 10) press the key icon on the toolbar.

 

[Fatboycookie]

You aren't mushing the two columns into a single one are you? The proper way to make a compound/composite primary key is to select all the necessary columns by using cntl-click until all columns are highlighted (up to a maximum of 10) press the key icon on the toolbar.

Hi Pat,

I am on a college course and so I will be using this site in the future more often and I would just like to say. I have purchased three different access books and not one of them mentioned hitting CTRL and the Primary Key Icon to include the composite key and I have been hours trying to work this one out.

Thank you very much for your help.

 

[Meast77]

Originally Posted by Pat Hartman
You aren't mushing the two columns into a single one are you? The proper way to make a compound/composite primary key is to select all the necessary columns by using cntl-click until all columns are highlighted (up to a maximum of 10) press the key icon on the toolbar.

I am a college student also and I have been trying for a week to get the composite primary key concept to work. I kept getting a one to one relationship between my parent table and the linking table on the side where the foreign key was listed first in the linking table. Once I did what Pat said I had a composite primary key and two one-to-many relationships. Thank a million.

 

[RainLover]

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.

 

[Pat Hartman]

I believe this thread is about junction tables. Junction tables have a natural composite primary key - the FK to each related table. You can, as I discussed in other places, add an autonumber as a surrogate PK but if you do that, you will need a unique index on the two FK's to enforce the business rule that the conjunction be unique. When the junction table is not a parent to other tables, there is no reason to add a surrogate key. There will never be a join involving both FKs at once. If there comes a time later in the development of the app where it becomes necessary to add a child table, the surrogate autonumber PK can be added at that time and the original PK can be changed to be a unique index.

 

[dportas]

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.

The reason for composite keys is the same as for simple ones. Keys are about data integrity and are not just invented magic numbers for things. Composite keys prevent some combination of attributes from being duplicated on multiple rows and thus implement relevant business rules. They help to prevent inconsistent data entry and provide a means for users and consumers of the data to identify accurately the information of interest. An arbitrary surrogate key doesn't do any of those things.

Composite primary keys are sometimes (wrongly) perceived to be a problem because people mix up the ideas of keys and indexes or fail to understand that a "primary" key is no different in principle from any other candidate key of a table - whether simple or composite. One naive argument against composite keys can be paraphrased like this: "A table can only have one key. For simplicity's sake it is better for the key not to be composite." But the first statement is false and the second is therefore irrelevant.

A table can and should have as many keys as are needed for data integrity and correct identification of data. Very often some of those keys will be composite.

 

[RainLover]

It is a composite key not keys.
Keys are not about data intregity they are about unique identifers. Keys identify records.
Unique indexes prevent the duplication of a combination of fields not keys.

 

[gemma-the-husky]

It is a composite key not keys.
Keys are not about data intregity they are about unique identifers. Keys identify records.
Unique indexes prevent the duplication of a combination of fields not keys.

I do not think this is correct, actually. We may just be arguing semantics, but I think it is rather more than that.

I prefer to side with the view expressed by dportas. incidentally, i am not sure whether dportas is actually a new member, as he has expressed many opinions on this topic - but maybe on the another forum. It is important to distinguish between an autonumber key which helps maintain referential integrity, and the underlying "business rule key" which actually drives the database, and in this respect, I do think these keys are about data integrity

if you have say, a sales order system, you may have these tables

order header
order lines

now an "order header" table may have 2 "effective primary" keys (say)

1. an autonumber key
2. the real key, consisting of account number + order number

key 2, by being described as unique prevents the system raising duplicate orders with the same order number. key 1 does nothing of the sort.

the autonumber key is in truth superfluous to some extent, but is more efficient than the composite key in terms of manipulating the database, and in terms of data storage, hence a lot of us use autonumber keys. the trade off is that the dbmanager has to maintain an additional key.

so the foreign key in the "order lines" table could be either of the 2 afore mentioned primary keys.

and as pat said, if a given table never forms part of the 1 side of a 1-to-many relation there is no practical benefit at all of adding an autonumber key to that table.

In this respect, the composite "business" key of customer + order number is clearly not just there for row identity reasons. This key will actually be the driving force in the system, maintaining integrity that the autonumber on its own just cannot supply.

 

[dportas]

It is a composite key not keys.
Keys are not about data intregity they are about unique identifers. Keys identify records.

That's an extremely partial over-simplification. A database is a set of propositions (facts) about some universe of discourse. A table is a set of propositions of one particular fact type. In order to use the information in a table and to stand any chance of interpreting and updating it correctly the users need some reliable way to identifiy facts and relate them to the real world things about which information is being recorded. The identifiers they use are called candidate keys (strictly identifiers may be just superkeys but in practice from a database design and implementation perspective we are interested in the candidate keys underlying a set of superkeys). An important part of successful database design is to ensure those keys are properly designed and enforced so that their integrity can be relied on. Keys are therefore an indespensible part of designing an accurate and useful database. In almost any non-trivial system, composite keys are going to be an important data integrity feature. It's difficult to imagine why any responsible database designer would not want to take advantage of them.

Unique indexes prevent the duplication of a combination or fields not keys.

A unique index is of course one technical feature used to enforce the uniqueness of some set of attributes. A key is the set of attributes against which uniqueness is being enforced. The subject of this thread and my replies is keys, not indexes. If you prevent duplication on multiple non-nullable columns using a unique index then you have of course implemented a composite key.

 

[RainLover]

I do not think this is not correct, actually.

If you do not think that it not correct then you must be thinhing that it is correct.

Or did you use a double negative by mistake.

 

[RainLover]

deportas

It appears that you have not found fault in what I said. No matter how overly complex you wish to reply.

My simple statements are correct.

The difference between you and I is that you perfer to use the complex system of a Composite Key where I prefer to use the Simple method of One. (A long Integer).

There is nothing that a simple primary key can't do that a complex key can do. Except make it complex when there is no need to,

 

[dportas]

deportas

It appears that you have not found fault in what I said. No matter how overly complex you wish to reply.

Well I certainly disagreed with your claim that keys are not about data integrity! Keys are absolutely central to data integrity as I thought I had explained - but obviously not well enough.

There is nothing that a simple primary key can't do that a complex key can do.

Obviously there is something a simple key does not do. A composite key means that the data in a combination of multiple attributes cannot be duplicated. A simple key does not. That is the critical difference and if you do not understand that then you do not understand keys.

 

[RainLover]

The Prime purpose of a Key is to uniquely identify a field in one table so that it can find information stored in another table.

A primary key does not allow duplication. Be that be a singular field or many fields.

There is no need to create a complex primary key to create uniqueness. A unique index handles this.

This quote from Microsoft identifies the purpose of a Primary Key. It also mentions that its composition must be unique.

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 usingqueries forms , and reports 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 values from being entered in the primary key fields.

Again the primary Key identifies and the index creates the uniqueness.

What is so difficult about this that you cannot understand.

 

[dportas]

The Prime purpose of a Key is to uniquely identify a field[?] in one table so that it can find information stored in another table.

A primary key does not allow duplication. Be that be a singular field or many fields.

But uniqueness in relational database terms is a property of a set of attributes, not of a table. Uniqueness of just one attribute clearly does not guarantee uniqueness of some others. A row may be identified by multiple keys, not just one key and any of those keys might be either simple or composite.

For example if the business rule is that the combination of VendorNumber and InvoiceNumber must be unique and if users need to identify a particular invoice based on those attributes then only a composite key on {VendorNumber, InvoiceNumber} will meet that requirement. Making just one of those attributes a key or making some other attribute a key does not meet the requirement. Allowing a vendor's invoice to be duplicated in the database is not the way to stay friendly terms with the accountants and auditors - no matter what other keys may also exist in that table.

There is no need to create a complex primary key to create uniqueness. A unique index handles this.

A unique index is a feature used to implement keys, which is something I already said. Put another way, the key is the minimally unique set of attributes which is constrained by a unique index (or by whatever other mechanism the DBMS supplies for key enforcement). Indexes and keys are quite different issues but sadly that's a point that often seems to cause confusion.

What is so difficult about this that you cannot understand.

I don't understand how you can deny that keys are important for data integrity. I'm happy to agree with the bit from Microsoft that you quoted. It doesn't say anything that contradicts what I've said and I'm not sure what it is you think I don't understand.