is unique an index or a field?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

is unique an index or a field?

Arnaud de Montard
When I check the unique property of a field, 4D adds an index to that field. Same when I define a primary key. So I'm wondering if being unique is an index property instead of a field property… But in system tables, that property exists twice:
  _USER_COLUMNS.UNIQUENESS
  _USER_INDEXES.UNIQUENESS
Some light?

--
Arnaud de Montard


**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: is unique an index or a field?

Jeffrey Kain
In older versions of 4D it was possible to create a field with 'unique' but not indexed. This causes 4D to have to do full table scans for every insert/update in order to validate uniqueness. In a large table, this is a huge performance problem. Clearly a competent developer who understands the basics of how databases work would never do this on a large, frequently updated table, but 4D still let you fall into that trap.

Starting with 13.5, 4D no longer allows this.

--
Jeffrey Kain
[hidden email]

> On Feb 4, 2017, at 2:14 PM, Arnaud de Montard <[hidden email]> wrote:
>
> When I check the unique property of a field, 4D adds an index to that field. Same when I define a primary key. So I'm wondering if being unique is an index property instead of a field property… But in system tables, that property exists twice:

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: is unique an index or a field?

Arnaud de Montard

> Le 4 févr. 2017 à 21:23, Jeffrey Kain <[hidden email]> a écrit :
>
> In older versions of 4D it was possible to create a field with 'unique' but not indexed. This causes 4D to have to do full table scans for every insert/update in order to validate uniqueness. In a large table, this is a huge performance problem. Clearly a competent developer who understands the basics of how databases work would never do this on a large, frequently updated table, but 4D still let you fall into that trap.

Hi Jeffrey,
that was not my question, I understand the performance problem and that 4D has to index a field to ensure it's unique with decent speed (although in small tables it seems a bit excessive).

Reading indexes in system tables I was surprised first to find 2 UNIQUENESS columns, in field and index. Thinking better, I guess field.unique is just a kind of trigger to create an index.unique.

Then I said myself "well, if the index has a uniqueness column, I should be able to set that somewhere". But uniqueness is missing in the create index dialog. It seems index.unique can only be inherited form field.unique or from a primary key definition.

--
Arnaud de Montard



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: is unique an index or a field?

Keisuke Miyako
I might be wrong, but I think the idea of unique index was never followed through,
it just exists in the command syntax but does nothing as far as I know.

c.f.

http://doc.4d.com/4Dv16/4D/16/CREATE-INDEX.300-3201313.en.html
ACI0088408      CREATE UNIQUE INDEX command does not work

> 2017/02/05 8:21、Arnaud de Montard <[hidden email]> のメール:
> Thinking better, I guess field.unique is just a kind of trigger to create an index.unique.



宮古 啓介
セールス・エンジニア

株式会社フォーディー・ジャパン
〒150-0043
東京都渋谷区道玄坂1-10-2 渋谷THビル6F
Tel: 03-6427-8441
Fax: 03-6427-8449

[hidden email]
www.4D.com/JP

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: is unique an index or a field?

Arnaud de Montard

> Le 5 févr. 2017 à 05:50, Keisuke Miyako <[hidden email]> a écrit :
>
> I might be wrong, but I think the idea of unique index was never followed through,
> it just exists in the command syntax but does nothing as far as I know.

OK, thank you. After writing my previous mail, i verified that when field is unique, index is always unique too: always true.

--
Arnaud de Montard



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************