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