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.

  • 1
    If the optimiser is making bad choices about index usage the problem might be out-of-date statistics.
    – mendosi
    Apr 7, 2017 at 12:41

1 Answer 1


Have you tried the NOCHECK option?


  • I tried that, but that option doesn't seem to influence the necessity of indexes and keys.
    – John
    Apr 7, 2017 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.