Posts Tagged help needed

Help needed: monotonicity without triggers?

I’ve a problem that’s been gnawing away at me for a few days now. It’s easily stated:

Without using triggers, if X and Y are numeric types, can you enforce a rule that says the numerical ordering of column Y must be the same as column X?

That is, ROW_NUMBER() OVER (ORDER BY X) == ROW_NUMBER() OVER (ORDER BY Y). (In mathematical terms, the relationship between X and Y is monotonically increasing.)



INSERT dbo.MyTable(X,Y)
	VALUES (1,3), (5,17), (13,54), (17, 65)

, and all is well: order(X) == order(Y) == (1,2,3,4).

But if I then do:

INSERT dbo.MyTable(X,Y)
	VALUES (23, 51)

, the INSERT should fail, as order(Y) == c(1,2,3,5,4) != order(X).

Approaches that do not work:

  • A check constraint that calls a user-defined function. It works on INSERT, but not UPDATE.
  • A view with a UNIQUE index on it. In order to define a UNIQUE index on a view, it must already have a clustered index defined on it. But you can’t create a clustered index on a view that contains LEFT JOINs, or window functions like ROW_NUMBER or LEAD/LAG.

Yes, I can achieve the result I want with a trigger; but for a rule that can be explained so simply, it doesn’t feel right.

Someone somewhere has a beautiful solution to this problem, so if you know of any solution (or indeed, can categorically state that it’s impossible to achieve), please let me know in the comments!


, ,

Leave a comment