Euphoria
Ticket #686:
EDS Table Unique Key Requirement
-
Reported by
Etienne
Jul 06, 2011
I am not sure I understand why the "key" part of a table record has to be unique among keys.
For example why prevent records as follows:
Key, Data. 4, {"four"}. 5, {"five"}. 5, {"another five"}. 6, {"six"}.
Will this somehow break the binary search or record insertion/deletion code?
Unless I am missing something, this restriction severely curtails the usefulness of EDS.
If the programmer wishes that in a particular application, the keys in a table be unique, he/she can write code to first search for the existence of the key before inserting a new record.
The suggestion that the key can be some meaningless record counter or something of the sort ignores, in my view, the traditional purpose of an index key - which is to find a record, fast.
One might ask: "If multiple instances of a keys were allowed, how does the programmer know when the desired record is found?" The answer would be: The programmer allowed the instances in the first place when he/she could have prevented them as was mentioned earlier. A simple traversing of the matching records, examining the "data" part, can reveal the desired record. Or perhaps all records with matching keys are actually desired.
It would be nice not to be forced to use some external database system, when simply lifting this restriction would permit the use of the Euphoria-oriented EDS.
Regards Etienne
Details
1. Comment by mattlewis
Jul 06, 2011
I suspect that the unique constraint will not be lifted. However, there's no limit on what you use for a key, so {5, "five"} and {5, "another five"} could be the actual keys.
2. Comment by DerekParnell
Jul 06, 2011
EDS is a simple database system and as such does not have some of the more complex built-in functionality that some other systems have.
Allowing non-unique keys would have implications that add a new layer of complexity to the system. For example, if two records have the same key, how would you fetch them? Using the current API, you would always have to do a fetch of the first (or only) record, then do a 'get next' type read for the other matching records until you read one with a different key.
Most DB tables require unique keys and it is really a rare thing to have a table with non-unique keys. So, to paraphrase your suggestion ...
If the programmer wishes that in a particular application, the keys in a table be non-unique, he/she can write code to first search for the existence of the key before appending a new record to an existing record. Thus in your example data, the records would look like ...
4, {"four"}. 5, {{"five"},{"another five"}}. 6, {"six"}.
When formatting your records like this, it is a a simple matter of reading in ALL the 'records' with the same key in one fetch call.
3. Comment by Etienne
Jul 06, 2011
The suggestion that my proposed table: 4, {"four"}. 5, {"five"}. 5, {"another five"}. 6, {"six"}.
can instead be: 4, {"four"}. 5, . 6, {"six"}.
is workable, I think.
Thanks for the responses.
Regards Etienne
4. Comment by Etienne
Jul 06, 2011
That should read: 4, {"four"}. 5, . 6, {"six"}.
is workable.
5. Comment by bill
Oct 09, 2011
Given what Derek says. The only way you could allow duplicate keys is with a row_id (as in Oracle).
As something has to be unique ley it be the key.
A reverse index (say name to key) is really what is being requested.
6. Comment by ChrisB
Nov 24, 2022
I agree - the key should be unique, you can do what you want with the rest of the fields.