Need for Reliable Primary Key Fields

[T-Square]

I have a solution that I have been managing at a distance for quite some time, and I have consistently run into the following problem:

My client informs me of a glitch in the system. I test it out on my copy of the database and fix it. I send out a clone of the database to the client, which they use to load in their data. However, because I am using an old version of the same file, my serial number is lower than the last number used by the client. When they start adding new records, Filemaker happily reuses the serial numbers ***EVEN THOUGH I HAVE DEFINED THE FIELD AS REQUIRING UNIQUE NUMBERS***.

Given that I am trying to use FM's purported relational capabilities, and this field forms the basis of several joins to other files, I am DEEPLY DISTURBED that FM allows duplicate ID numbers.

I imagine that I could write into my load script a reset serial number check, but that's just annoying to have to do in every file I create.

This shortcoming has screwed up my client's system several times in the past, and makes it extremely difficult for me to maintain an ongoing development process.

Is there any way to create a field that UNIQUELY identifies a particular record in the database (i.e., a "Primary Key" field, as defined by every other rdbms on the planet)?

[ernst]

Hi Sunfish,

Take a look at

http://www.fmforums.com/threads/showflat.php?Cat=&Board=articles&Number=41246&page=1&view=collapsed&sb=5&o=&fpart=1

Regards,

Ernst.

[T-Square]

Ernst--

Thanks for the referral. I am looking through those posts, and I STILL don't understand why it is so damned difficult for FM simply to provide what its software implies is possible--i.e., a unique field definition that ACTUALLY REQUIRES A UNIQUE ENTRY.

Is that SO much to ask?

David

[Kurt Knippel]

Well, yes and no. Filemaker has taken user customizability to the extreme and left things like this up to the developer to implement as needed.

[ernst]

Hey Sunfish and hello captain,

It would have been nice if Filemaker had an (overidable) warning for this kind of conflicts during import.

But automatically renumbering all serialnumbers AND automatically updating all related fields in records that imported to related files would be a bit much asked (and maybe even impossible), I think.

That's why you'll always need either a smart import routine (tricky to built if a lot of relationships with other files exist) or a sort of 'random' serialnumber like shown in the example. This is not Filemaker specific i.m.o.

Regards,

Ernst.

[T-Square]

Kurt--

I would have to say this wasn't really an issue of customizability, but of doing what is customarily done. Pick up ANY book on relational database design, and they ALL tell you you have to have a unique Key field.

Think about it: how useful is a database system where you link tables via a unique field--only to have duplicate IDs pop up? All your links are corrupted, there's NO WAY to untangle the mess, and it only gets worse if you delete one of the duplicate entries, and FM happily cascades the deletes for BOTH clients because of the cross-up. (You DO enforce referential integrity, don't you?)

It's crazy to call this customizability. If banks or the government were to use such a database, you'd be pretty pissed off when they lost your money, forgot to send you your tax refund, arrested you for someone else's crimes, etc....

FM includes a checkbox that states "Unique"--but this doesn't actually enforce Uniqueness. So, what's it there for?

And Ernst, my problem isn't with importing per se--it's with the fact that FM doesn't check the uniqueness of a unique serial number before using it.

There.

David