I have a problem on SQL Server that led me to the question of whether it is possible to define a foreign key that is not enforced, not backed by an index, and does not need the referenced columns to constitute an (enforced) key.
Foreign keys do two things:
- They enforce integrity – which I am willing to give up in my scenario – and
- they can be used by the query planner to drop joins that are guaranteed to
not change the result set's number of rows.
The last thing is what I'd still get – the integrity I'll enforce at the application layer.
(Note that what you can have the database enforce is more limited that what you could have as a droppable join condition. A frequent case is that where you'd want to specify a foreign key with ...foreign key (some_constant, almost_key) references... where some_constant is an illegal immediate constant.)
The reason why I need this is I have a SQL Server specific problem in that it does bad query plans in the presence of such an index when the clustered index is a columnstore index (at least I saw on case of such a bad query plan). Since the feature of combining a clustered columnstore indexes with nonclustered btree indexes is brand-new, I take it that there are still some issues they need to iron out.
However, I'm also curious about whether SQL databases generally always demand foreign keys to be backed by indexes on the foreign and/or the domestic side, or whether there are some that take them as hints but allow the user to enforce.
Best Answer
Have you tried the NOCHECK option?
https://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx