Composite primary keys

[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?

 

[RainLover]

I always miss the good fights

And this has been a beauty.

The best part is that I won.

 

[Galaxiom]

I really can't comment.

I failed at SQL Server.

I use MySQL when necessary.

Perhaps you could explain.

It is nothing to do with the Server but their inadequate understanding of the TableDef Object.

For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
Next

The only line required for exactly the same functionality is:

CurrentDb.TableDefs.Delete stLocalTableName

The loop through the tableDefs Collection is entirely superfluous.

Moreover, the same outcome could be achieved by simply changing the Connect Property of the original TableDef and applying the RefreshLink Method.

Further still, when I tried the append of a new TableDef it failed with one of Microsoft's less than helpful error messages. Editing the Connect property of the existing DSN connected table worked without a glitch.

I sometimes wonder how many developers have abandonned DSNless linked tables as too complex when they hit this error after following Microsoft's crazy instructions.

 

[RainLover]

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?

Dave you are very close.

The difference lies in the use of a Unique Index. A unique index is not a key but can replace the Composite Key provided a single primary key is used.

It is that simple.

But I have enjoyed the banter dealing with people who refuse to move into the 20th Century.

 

[RainLover]

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

Yes there is a lot of superfluous code about. Sadly Microsoft does encourage it.

Just look at the code Wizards write.

 

[Galaxiom]

And this has been a beauty.

The best part is that I won.

I disagree entirely.

You claimed that a table cannot have two keys. It can, but only one can be the Primary Key. This is quite a different issue from the use of two fields as a composite key.

The other subplot was your claim that there is never an advantage to using a composite primary key. You need to reread the example I posted because I explicitly showed an important adavantage.

The related table with the composite foreign key can be queried for EmployeeID and WorkDate completely independently of the parent table. The autonumber construction can only work with the EmployeeID and WorkDate by joining to the parent table.

 

[Galaxiom]

But I have enjoyed the banter dealing with people who refuse to move into the 20th Century.

You want use to go back to Access 98?

 

[RainLover]

You missed it by that much.

My claim is that people are still in the Nineth Century.

And it is 97 but that does not matter.

A year ago I did work in 97 for a Qld Government Department as a contractor.

It was there that I saw this over abundance of Composite Primary Keys. 4 Fields was common place, and sometimes more.

This made my job very difficult. Can you imagine what the SQL looks like when they had several of these tables joined in the one Query. It took ages to work out what was going on. To make things even worse it was common place to have one query of several Tables as Sub Queries to Sub Queries.

A single Autonumber would not have solved all the problems but would have made life so much easier.

 

[RainLover]

I disagree entirely.

You claimed that a table cannot have two keys. It can, but only one can be the Primary Key. This is quite a different issue from the use of two fields as a composite key.

Tomorrow is another day. I will address this one then.

 

[Galaxiom]

A year ago I did work in 97 for a Qld Government Department as a contractor.

It was there that I saw this over abundance of Composite Primary Keys. 4 Fields was common place, and sometimes more.

Ah. That explains your particular dislike for composite keys. Like anything they need to be used sensibly and it certainly doesn't sound like they were in that case.

The same people who set that up probably had a hand in the Health Department Payroll System.

 

[Pat Hartman]

Rain,
You're not wrong But, you are being overly zealous and close minded. I use surrogate keys almost entirely even when I have a single candidate key. I don't do it because I believe it is "right", I do it because it is more convenient for coding and allows me to use combo and list boxes to select records. The price I pay, and it is a price, is more complexity in some queries and debugging overhead. As I sit with a half dozen tables open so I can see something besides numbers, I always pine for a natural key to brighten my day. All my tables have primary keys whether they need them or not because having a primary key is "right". Whether it is surrogate or natural, single or composite, is much less important and should be left to how the table will be used. As I said earlier, I never add surrogate keys to junction tables just because I can. I add them ONLY if the junction table has child tables and so at some poiint, I'll probably need a combo to pick it.

You could make the foreign key reference the composite key instead -

This is an absolute no-no. I don't care what Galaxiom says. There are some things that should never happen. Once you put a surrogate key in place, you are committed to it. It is used for all RI, joins, etc. The original candidate key is relegated to "data" status and just because the database engine will allow you to enforce RI on a unique index doesn't mean that you should do it.

 

[ChrisO]

GalaxiomAtHome.

The code you mentioned from Microsoft does not appear to me to have the same functionality as the one line of code you suggest.

[color=green]'//     stLocalTableName: Name of the table that you are creating in the current database[/color]
For Each td In CurrentDb.TableDefs
    If td.Name = stLocalTableName Then
        CurrentDb.TableDefs.Delete stLocalTableName
    End If
Next

Note that the intent of the Microsoft code is to create a Table and hence it allows for the possibility that the Table does not already exist. So they run through the collection to see if it exists and, if it does, then delete it.

Your code assumes the Table already exists but, if that assumption is wrong, will error if it doesn’t.

So it may not be entirely superfluous on the part of the Microsoft code but really just a preference to not use On Error Resume Next in their code.

Chris.

 

[VilaRestal]

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.

All this (thread revived) because one person thanked Pat for explaining it.

Pat and Galaxiom are rightly saying there's no right way. Slight pros and cons to both methods:

Surrogate Key Pros:

• Only one field to join on (but for a pure junction table that's not an issue: usually not joining on the surrogate, mostly joining on the foreign keys)

Surrogate Key Cons:

• An extra field takes up extra space.
• Have to explicitly add the unique index on the other fields (happens automatically if they're PKs).

Combined Key Pros and Cons the opposite of that.

If you prefer to always have a surrogate key Rain, then fair enough but don't make a religion out of it.

I for one agree with Pat, combined keys are easier if there's only two of them and there are no subtables of it. The more fields there are and the more it becomes a data table and/or a parent then the more a surrogate becomes preferable.

 

[VilaRestal]

A good example of WTF code from Microsoft is their sample for modifying the shift key bypass property:

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

Note two functions, one for disable, one for enable, both identical except the value they set the property to.

I think they must get paid by the line of code they write.

And they always do

Dim db As DAO.Database
Set db = CurrentDb()

"Lets replace one perfectly good object variable with another."
And that waste of space appears in so many other peoples' code.

 

[RainLover]

Pat

Thank you for your responce.

And you are right, I have gone over the top.

I did not intend to. But one reply led to another.

Your statement that I am correct means a lot. Thank You.

It is time to put this one to bed.

 

[ChrisO]

VilaRestal.

Well, we could be here all day…

In that link, did you notice the two comment lines?

'This next line disables the shift key on startup.
db.Properties("AllowByPassKey") = False

and

'This next line of code disables the SHIFT key on startup.
db.Properties("AllowByPassKey") = True

I would sooner have no comments at all if they are going to be wrong.

Chris.

 

[Galaxiom]

A good example of WTF code from Microsoft And they always do

Dim db As DAO.Database
Set db = CurrentDb()

"Lets replace one perfectly good object variable with another."
And that waste of space appears in so many other peoples' code.

Well actually, CurrentDb is not an object but a Method of the Application Object.

As such it is a function that returns a pointer to the object:

DBEngine.Workspaces(0).Databases(0)

There are a number of places where this becomes apparent.

Try passing CurrentDb as an object to a function. It won't work.
But you can pass an object set to the value returned by CurrentDb.

Try these lines in the Immediate Window.
This does not work, returning an error "Object invalid or no longer set":

Set tdf = CurrentDb.TableDefs(0): ? tdf.Name

But this ostensibly similar code will work:

Set db = Currentdb: Set tdf = db.TableDefs(0): ? tdf.Name

Microsoft does it universally in their examples because (right or wrong) they believe in consistency. Many developers routinely set a variable to Currentdb because that is what they see Microsoft do but have absolutely no idea why.

My favourite irk from Microsoft is this:

Dim strname as String
Dim strWhere As String
 
strname = "someformname"
DoCmd.OpenForm strName, , , strWhere

Even when there is no where clause used. Talk about cluttering code.