I was modelling a game of chess and came up with this representation for the chess board..
X TINYINT NOT NULL CHECK(X BETWEEN 1 AND 8)
Y TINYINT NOT NULL CHECK(Y BETWEEN 1 AND 8)
I then created a view that returns a single column displaying the chess notation for these corordinate eg. a1,a2..h8
I obviously have another table (Moves) which has the current Piece and what square it currently is on and I would have liked to enforce the "square" value via RI between the GameBoard and Move.
I quickly realised that I had made that classic logical/physical mistake in regards to this. ie: In the logical model Square is a type with 2 representations, X,Y and Notation. In the end I simply added a rather lengthy CHECK constraint on the square column in the Moves table...
But it did raise may interest..
Is there anything in the relational model that prohibits using a view in a RI constraint?
Don't have my C J Date books to hand, but I don't think there is. The foreign key "references" constraint is really just a convenient shorthand for a database constraint like "FORALL child EXISTS (parent WHERE parent.pk = child.parent_fk)". I don't recall any prohibition on using views in database constraints in general, and can't see any reason for there being any.