Composite primary keys

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

 

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

 

[Galaxiom]

I don't care what Galaxiom says. There are some things that should never happen.

What I actually said was:

Few practices lauded as "never" in database design actually turn out to be so universally unacceptable as portrayed by the detractor.

Emphasis added.

 

[ChrisO]

Why create a variable at all?

Print CurrentDb.TableDefs(0).Name

More on the subject.

Apart from testing code, I can not remember the last time I had to create DAO database or DAO recordset object pointers. The vast majority of people still do it but all they are really doing is early binding to a reference that can be broken.

What happens on the www is; when you ask people why they late bind something they give you all the good reasons. When you ask them why they don’t late bind DAO they say, umm, well, ah, well you can’t umm, ah. I once reduced an MVP to him saying “I would never dream of creating a database without a reference to DAO”. My reply to that was that I don’t really care what he dreams about. I did not get a further reply.

On my SkyDrive site I currently have 23 demos and not one of them uses any references at all, including DAO.(Apart from the two I can’t remove, and I would if I could.) The Chess game has more code than you can poke a stick at and the passing of the Current Record through OpenArgs still does not need a reference set.

-----------

Another question:-
Why test code in an artificial environment (the debug window) where the code will not be actually running? Is it faster? Do people really write code that fast? Is faster really better? How much time do they spend going back to test their fast code in a real environment?

Set db = Currentdb: Set tdf = db.TableDefs(0): ? tdf.Name
That works in the debug window (Even without a DAO reference)
But it doesn’t work in the VBA editor (With or without a DAO reference)
Db is not declared so it won’t work unless we turn off Option Explicit.
But who wants to turn off Option Explicit in order to test their code?

-----------

I think we all get set in our ways. But I don’t think we get that old that we can’t think.


Chris.

 

[Galaxiom]

Why create a variable at all?

Print CurrentDb.TableDefs(0).Name

You missed the point. It wasn't meant to be a practical piece of code but a demonstration of something that works and something that doesn't when referencing CurrentDb.

Why test code in an artificial environment (the debug window) where the code will not be actually running? Is it faster?

It provides a fast rudimentary test. That is one of the reasons that window is provided.

Moreover if one is in the middle of a real job it is better done in the Immediate Window than to add something to the project and accidentally save it.

Anyone experienced in the use of the Immediate Window will know what it is about and how its use differs from the editor. Anyone who doesn't and cares about the subject will ponder it for a bit, try some things, learn something and might even start using it. Win-win either way.

Db is not declared so it won’t work unless we turn off Option Explicit. But who wants to turn off Option Explicit in order to test their code?

Exactly why I used the Immediate Window. And who wants to type out a whole procedure complete with variable declarations?

Chris, while I have utmost respect for your knowledge of this subject, sometimes I think you just like to be nit-pickingly critical just for the sake of it. Perhaps you could try looking at the context of a post instead of being so focussed on what you can knock down.

I think we all get set in our ways. But I don’t think we get that old that we can’t think.

Some people don't need to be old to get set in their ways and some have never thought about anything much at all. I have always endeavoured to avoid getting set in my ways and you will generally find that I am quite conversant with multiple perspectives on any subject because I do think about the whys. My family brought me up to look for information and to think for myself.

 

[ChrisO]

>>You missed the point.<<

Did I really, or do you think I missed the point? I don’t mind if someone thinks I missed the point but I regard it as quite arrogant when someone tries to elevate their own belief to the status of a fact.

I believe you can write quite well and so make few allowances for what you write.
I am of the opinion that you write what you mean. I am also of the opinion that people who can craft a well worded answer should not flip that well crafted answer like a coin. Then if the well crafted answer comes down in their favour they accept it. But if it comes down in a way they did not intend they deny it, or say nothing.

That was also part of the answer given by ‘modest’ in the link I posted. Well crafted arrogance which could not be defended, so they ran away.

But that was 2006 and so here is a more recent one on a different subject. http://answers.microsoft.com/en-us/...abase-to/2bbd354a-8dee-49d4-aab4-e2e812d50d40

Not an inconsiderable talent but misused the <chuckle> in my opinion.
But look at the work it creates in order to try and explain the situation which had already been stated. And after going to the trouble of explaining it again look at the response…none.

Over the last 10 years, while I have been on the www, there have been many such occasions. So many it just isn’t funny anymore. There are some people who can dish it out but just can’t take it. They don’t know how to simply say ‘I stuffed up’. It simply does not appear to be in their vocabulary. It seems their perception of themselves outweighs all else. For the vast majority of the ‘names’ on the www, self promotion is rampant. The www is almost an industry devoted to self promotion where demotion is not tolerated. Be particularly careful at UA; the rejection of truth has many guises.

To be fair, there are also times that one finds a decent person (actually two in this link).
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/259447ac-2f0b-4aa7-b77c-f3594cea278f
It’s a long read but you may get my point.


Partial quote:-
>>“sometimes I think you just like to be nit-pickingly critical just for the sake of it.”<<
No! I would say:-
“I try to be critical if I think it lends some insight”

Such things as “Global variables are not reset on Error they are reset on Reset.”
That sort of critical statement lends insight and discriminates Error from Reset.
It may at the time be thought of as pedantic or overly critical. But over time the penny starts to drop and people may start to see the difference. If people continue to merge Error and Reset into some sort of collective blurry mirage then little is gained. But if people can clearly see the difference they can then use that difference in their favour.

That is just one example of being critical, there are many. But the point is the same; without the critique the facts remain blurred.

I have no expectation whatsoever of changing the world, but I will continue to chip away at it.

Chris.

 

[VilaRestal]

Let's try and keep this friendly and not take people's assertions personally.

I would say your original points Galaxiom were a little bit nitpicky themselves (the examples were quite obscure) so a bit hypocritical to accuse Chris of that. What's more, they appear to be wrong:

"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."

OK, I did. This code in a module:

Option Compare Database
Option Explicit

Public Function DBtest(ByRef db As DAO.Database) As String
    DBtest = db.TableDefs(0).Name
End Function

and this in a form:

Option Compare Database
Option Explicit

Private Sub Command0_Click()
    MsgBox DBtest(CurrentDb)
End Sub

Lo and behold: it works fine.

And as Chris says, CurrentDb.TableDefs(0).Name, works fine too. (And in fact the code sample you posted as a MS WTF, the deleting tables loop, is an example of Microsoft using CurrentDb in just this way and not consistently referencing it with db.)

I'm willing to believe that there may be cases where the method returning an object pointer won't do. But I've not seen an example yet. Just something in the immediate window that, as Chris says, is hardly relevant to the code I would ever write.