Viewing 16 posts - 1 through 16 (of 16 total)
  • Programmer/DBA question
  • Earl
    Free Member

    I’m working on a system that has a table with the following indexes:

    Index 1. PK
    AttrubuteA
    AttrubuteB
    AttrubuteC

    Index 2. Unique
    AttrubuteA
    AttrubuteB

    I want to remove the unique constraint on Index 2 as its causing problems –
    occasionally.
    But I’m trying to understand why the original programmer made Index 2 unique. Any ideas?

    GlennQuagmire
    Free Member

    The first question to ask is what makes a unique row in the table.

    GrahamS
    Full Member

    Does seem a bit odd. If Index2 uniquely identifies a row with two atttibutes then that sounds it should be the primary key. If it doesn’t then it can’t be unique.

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    dissonance
    Full Member

    Any ideas?

    Because they thought it would be required to enforce the business logic.
    Not a particularly helpful answer but without knowing the business specifics its impossible to answer.
    How is it causing issues?

    GlennQuagmire
    Free Member

    And just to add, if you remove the unique constraint from index 2 then it is superfluous and can be removed completely.

    petec
    Free Member

    developers love indexes (indices? -indexes just sounds wrong to me). That and functions. I bleeding hate functions; saved 35 mins on one process changing from a function to a simple table lookup.

    i have one table in one DB that has 150Gb of data and 560Gb of indexes. They refuse to entertain the thought of removing them, despite me showing they’re not used.

    Which brings me to my main point (sorry, rambling). Check whether the index is being used here; if not, get rid. If it is, you can tell what is using it, and then see whether that should.

    Earl
    Free Member

    The PK does uniquely identify the record – 100% sure of that.

    On the system I’m working on, a index is required if one wants to retrieve in that seq (sounds crazy but its true – I think its a throwback from AS400 days). Its not necessary there for performance. So Index 2 is probably used somewhere in the application/business logic. However I can’t see why it need to be unique.

    I think the original developer made a boo boo.

    GlennQuagmire
    Free Member

    The PK does uniquely identify the record – 100% sure of that.

    In which case index 2 is enforcing a different constraint to the PK and it should be removed.

    The existing PK will still utilise the index if only column A and B are provided.

    Earl
    Free Member

    What problems is it causing?

    The majority of the time – jut AttributeB alone is enough to be a alternate key. Once in a very blue moon – its not.

    Were looking at making a change to the system where the blue moon could happen much more often.

    Index 2 has been on the system for 15 years at least. It has caused problems in the past – and there have been bandaids and work-arounds applied. No one has suggested to just remove the unique constraint – because no one has taken the time to try and understand it….until now.

    Earl
    Free Member

    Thanks all – I’ll recommend the unique constraint be removed.

    GlennQuagmire
    Free Member

    I’ll recommend the unique constraint be removed.

    Remove the index completely – it’s not needed.

    petec
    Free Member

    if you need it for the retrieval of data in sequence (DB2?), are you sure that data sequence doesn’t care about the uniqueness of C, but needs it for A and B? Without knowing DB2 too well, it’s hard to tell, but it is superfluous from a T-SQL aspect.

    Earl
    Free Member

    I hear ya but can’t do that – Somewhere in the system it will be hard coded to use Index 2. If I remove it the app will fail to execute irrespective of whether the DB uses it or not for the fetch.

    Cheers.

    leffeboy
    Full Member

    Is it possibly not being used as an index but is rather a cheat to ensure you only ever have one value of attribute C for a pair of attribute A & B? If you remove it then you have the possibility of more than one value of C and maybe that is the no no?

    Stevet1
    Free Member

    But I’m trying to understand why the original programmer made Index 2 unique. Any ideas?

    Have you read the documentation.

    mahowlett
    Free Member

    Been a long time since I had anything to do with DB2 but, could it be that when the table was originally set up it was thought that A,B and C were required to make a row unique, but at a later date it was realised that actually only A, and B were required. If it’s a large table changing the primary key of a table can be very time consuming and could mean down time or delays you couldn’t afford, but adding a new index that effectively enforces A and B as a unique key for a record would be done in the background and not have any significant affect on the systems performance while it was being created…

Viewing 16 posts - 1 through 16 (of 16 total)

The topic ‘Programmer/DBA question’ is closed to new replies.