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!

Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: