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.)

E.g.

``````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!