Results 1 to 3 of 3

Updating unique index and ROW_NUMBER doesn't work?

  1. #1
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197

    Updating unique index and ROW_NUMBER doesn't work?

    Hi all,



    so here is a sql server problem for the t-sql guys out there (I hope there are any).

    Current state:
    I have a table used to realise an n:m dependency between two other tables, that holds some additional information for that dependency. So there are the two FK fields and the additional information fields. Any combination of the two FK fields can exist more than once in the table, as the additional informations can have more then one value. To be able to do this, the primary key is not set over the two FK fields but over an additional ID auto increment column. One of the additional fields however holds an ordering value for the additional informations for each combination of FKs:

    ID FK1 FK2 OrderingNr ...
    1 1 1 1
    2 1 1 2
    3 1 1 3
    4 1 2 1
    5 1 2 2

    The requirement here is that every OrderingNr is unique for each combination of FK1 and FK2, what is enforced by using a unique index over FK1, FK2 and OrderingNr.

    Problem description:
    When rows are deleted from the table, I want to recalculate the OrderingNr field, so that the numbering doesn't have a hole. Deleting record with ID 2 should result in an update of OrderingNr from record with ID 3 to get the value 2.
    ID FK1 FK2 OrderingNr ...
    1 1 1 1
    3 1 1 2
    4 1 2 1
    5 1 2 2

    I build a little update query to do this by using ROW_NUMBER to getting the correct number while not messing the order, but when I run the query it tells me that I try to insert an already existing key in the unqiue index over FK1, FK2 and OrderingNr and I don't get why (see TEST1 from sql). I even tried to update the OrderingNr to none existing numbers by making them negative and change them back to positive values afterwards (TEST2 from sql script), but it still complains about none unqiue values. As you can see in the WANTED OUTCOME select query, there should be no violation against the unique index anyway. Any ideas are welcome!

    SQL Appendix
    Code:
    -- SETUP
    CREATE TABLE dbo.[T_Test](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        FK1 [int] NOT NULL,
        FK2 [int] NOT NULL,
        OrderingNr [int] NOT NULL,
     CONSTRAINT [PK_T_DokumentMerkmal] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [IX_T_Test] ON dbo.[T_Test]
    (
        FK1 ASC,
        FK2 ASC,
        OrderingNr ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    INSERT INTO dbo.T_Test (FK1, FK2, OrderingNr) VALUES (1, 1, 1), (1, 1, 2), (1, 1, 3), (1, 2, 1), (1, 2, 2)
    DELETE FROM dbo.T_Test WHERE FK1 = 1 AND FK2 = 1 AND OrderingNr = 2
    
    -- WANTED UPDATES
    SELECT ID, FK1, FK2, OrderingNr, NewOrderingNr
    FROM (SELECT     ID, FK1, FK2, OrderingNr, ROW_NUMBER() OVER (PARTITION BY FK1, FK2 ORDER BY OrderingNr) AS NewOrderingNr
            FROM     dbo.T_Test) AS t 
    WHERE t.FK1 = 1 AND t.FK2 = 1
    
    -- TEST 1
    UPDATE dbo.T_Test SET OrderingNr = t.NewOrderingNr
    FROM (SELECT     ID, FK1, FK2, OrderingNr, ROW_NUMBER() OVER (PARTITION BY FK1, FK2 ORDER BY OrderingNr) AS NewOrderingNr
            FROM     dbo.T_Test) AS t 
    WHERE dbo.T_Test.FK1 = 1 AND dbo.T_Test.FK2 = 1
    
    -- TEST 2
    UPDATE dbo.T_Test SET OrderingNr = t.NewOrderingNr
    FROM (SELECT     ID, FK1, FK2, OrderingNr, -ROW_NUMBER() OVER (PARTITION BY FK1, FK2 ORDER BY OrderingNr) AS NewOrderingNr
            FROM     dbo.T_Test) AS t 
    WHERE dbo.T_Test.FK1 = 1 AND dbo.T_Test.FK2 = 1
    
    UPDATE dbo.T_Test SET OrderingNr = -OrderingNr
    WHERE dbo.T_Test.FK1 = 1 AND dbo.T_Test.FK2 = 1
    
    -- CLEANUP
    DROP TABLE dbo.T_Test

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I do not know Transact or SQL server and to top it off, my eyes hurt. So I did not take the time your post deserves to read every word. But one question did pop up for me. Is there a rule set by SQL server via a compound key? Perhaps there is a table you are not considering that holds a key value. And then, can you distinguish who is telling you about this index issue? Is it Access or is it SQL?

    Hope I am not way off base but, at least your thread gets a bump.

  3. #3
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Thanks for watching at it. I'm glad that someone took the time, as I know this forum is more about access then sql server. To answer your questions: the rule is set by a compound index IX_T_Test as shown in the given sql code. The problem is fully reproduceable with the code I provided, so there is no other constraint or table involved. The issue is definitely server sided as I get the error message when executing the code in the server management studio. The error explicitly mentions the IX_T_Test index as the one that has double values. I could give the full error message, but it's german. Perhaps I can provide the error number tomorrow, as it is language independent.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-12-2013, 06:11 AM
  2. Replies: 2
    Last Post: 12-01-2012, 07:10 AM
  3. Replies: 1
    Last Post: 08-19-2012, 09:55 PM
  4. Creating a unique index on two fields
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-03-2011, 10:15 AM
  5. Eerror No unique index found
    By newtoAccess in forum Database Design
    Replies: 4
    Last Post: 12-10-2010, 08:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums