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?
ROW_NUMBER() OVER (ORDER BY X) == ROW_NUMBER() OVER (ORDER BY Y). (In mathematical terms, the relationship between X and Y is monotonically increasing.)
CREATE TABLE dbo.MyTable ( X INT NOT NULL ,Y INT NOT NULL ) 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!