I don't think it's a bug.
|----- Indicates that the match field is intended to match a single record, such as an auto-entered value
Get(UUID) is officially not an auto enter value controlled by the field settings, it is rather a field that that can hold
more than one value, like a value list. Usually a text field, which should be used for uuid anyway.
The field is populated by an auto enter calculation, therefore not an auto entered value.
I never look at those line ends much anyway. You shouldn't look at filemaker graph as an ER diagram.
The various symbols for the Relationship Graph are listed in the Help. "Working with the relationships graph" - "Other relationships graph elements".
|--- = Indicates that the join cannot be made. For example, a match field is missing or is an unindexed field (such as an unstored calculated field or a global field), or the table closest to the indicator is missing or unavailable
Since you used an auto-enter serial in the first instance, FileMaker knows it will be unique. Get ( UUID ) is simply an auto-enter calculation. Obviously, UUID implies that it will be unique, but FileMaker doesn't know that, so it guesses duplicates are allowed.
Validate the field unique and you'll see the symbol change.
To add to this, It's a many to many because UUIDs are stored as text. In FileMaker, relating two text fields will always result in the many to many line being displayed, since there is no auto-increment text type in the field definitions (only auto-increment serial in the number type).
A text field can store many things, like numbers, unique UUIDs or return delimited value lists. All can be used in valid relationships, but like Mike mentions above, FIleMaker doesn't know it's unique.
Same thing goes for 1:1 relationships. It will always show as 1:many since the child table foreign key is not defined as unique.
thank you for the deep explanation and to recap I'm safe using get uuid for primary keys. Is there any benefits using serial numbers vs uuid?
cross compatibility with other systems that aren’t expecting UUIDs as keys. For instance most MySQL databases are still running primary keys as incremental serials.
Flip side being the annoyances associated with serial IDs, mostly with importing between copies (such as when upgrading a solution). So it's situational. Our SQL Server apps, for example, use UUIDs, so we tend that direction.
Thanks once again, It's my fist time using UUID as PK, just want to make sure i'm heading in the right direction.
While in theory there's nothing wrong with using UUID as PK, there are lots of practical reasons not to do it. Here are some:
- In a columnar report it will take too much space. Even on a normal layout.
- You can't search for a range, like PartNo 1500...1999
- When a client calls for details regarding an invoice, having to answer "0104B9AF-570E-45F3-B74B-6B84BC56B29B" to the question "what is your client number" or "what is the invoice number" is prone to errors and unpleasant
- In our specific case, we're sending invoices via XML files to the insurances; the XML schema allows only 35 chars for an unique identifier which we build by putting together patient ID and invoice ID and we couldn't do that with 2 36-char long IDs.
When one side of a relationship has a | and the other a< it means the bar side is a global field. One value for all records in the table/occurrence. When both sides have >< it means you have a standard text or numeric field. These include standard serial numbers (text or numeric ) and UUIDs. By the way a uuid can be in either a text or numeric field, it is a hexidecal number.
These days I use UUIDs for all primary keys. However I will add a standard serial number/display ID when the user has to see it. This field is not used in relationships but can be used for display and searches.
You do have the same serial number issues when importing data. After all it is a serial number field.
You shouldn’t be worrying about most of this. Standard relational theory stipulates that a key field (a) carries no meaning, and (b) is not exposed to the user. So:
- No exposing it on reports
- No searching for it by range (because it contains no meaning, and shouldn’t be exposed to the user anyway)
- …Same thing.
The only one of these that therefore should be a concern is the last one (having to do with XML). In that case, you could use an MD5 hash instead. As Bruce mentions, if you need an invoice number or client number or the like, then use a separate field.
thanks once again on the right path for sure.
30 years ago I used to have professors, and they were delighted listening to that kind of theory. Was useful to pass exams.
30 years later, I got clients.
Summa summarum, if we get a project for the NSA I will obfuscate the PK's, if it comes from Joe's garage who wants to handle his clients, repairs and parts, I'll gladly use the PK's as identifiers, too. Let's be realistic.
Standard relational theory stipulates that a key field (a) carries no meaning, and (b) is not exposed to the user.
To pick nits, standard relational theory stipulates no such thing. A primary key only needs to be (1) unique and (2) immutable. It can carry meaning, be exposed to the user, even be a combination of multiple fields. In practice, making relational keys meaningless and inaccessible to end users avoids temptations to violate 1 & 2, but they are not themselves the rules. There are even situations where it's a good idea to making meaningful and user-visible keys that still comply with the actual rules.
That said, in this case, I agree that keeping separate primary key fields and user-visible IDs separate is the appropriate way to go. It's important to maintain an independent linking between entities in your schema when integrating with other systems to avoid confusion and enable troubleshooting.