Skip to Content
author's profile photo Former Member
Former Member

Primary keys vs unique hg indexes

A high profile SAP/Sybase consultant has the rule of thumb that primary key constraints should never be used saying unique hg indexes are better performing for queries. Knowing that the two are identical in structure, I ran a few benchmark tests. Whether using the primary key or unique hg index, I saw no difference between the two.

Can anyone give me a valid reason to change the primary key constraints to unique hg indexes? v15.4

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Aug 30, 2013 at 06:31 PM

    There is no reason to migrate. In fact, I would stay with a primary key since it not only enforces uniqueness but is used to tie tables together in PK/FK relationships (not only in modeling, but also in IQ).

    They are the same structure beneath the covers. In prior versions (can't remember how long ago) we used to be able to stuff NULL data into a UNIQUE index but not a PK. Perhaps that where the recommendation came from??

    The core design of the ANSI primary key is that only data can be stored. With a NULL value being a lack of data, it cannot be in a PK. And since a UNIQUE constraint and PK are identical in storage/structure in IQ, a UNIQUE constraint must also contain data (no NULLs).

    The only difference between UNIQUE and PRIMARY KEY is that a table can have more than one UNIQUE constraint. Though the columns in each can allow NULL data, you cannot actually load NULL data.

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 30, 2013 at 07:07 PM

    I have seen consultants avoiding PKs because the cleansing processes are not always optimal and the lack of PKs allow bypassing data integrity verification; this is a very dangerous practice that can compromise data quality.

    Sybase IQ automatically creates a unique HG index when you define the column as PK, which is way you do not see any difference in performance.

    As Mark said, better stay with PKs.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.