
/*
The Optimizer uses trusted foreign key constraints only for the 
purpose of removing redundant joins, and not to detect contradictions.

As you have seen, check constraints are instead used to detect 
contradictions, as long of course as the query is not autoparameterized.
*/

-- can't be tempdb
use pubs
go

drop table Test1, Test2
go

CREATE TABLE dbo.Test1
      (KeyColumn int NOT NULL,
       CONSTRAINT PK_Test1 PRIMARY KEY (KeyColumn)
      );
CREATE TABLE dbo.Test2
      (KeyColumn int NOT NULL,
       FKColumn int NOT NULL,
       CONSTRAINT PK_Test2 PRIMARY KEY (KeyColumn, FKColumn),
       CONSTRAINT FK_Test2 FOREIGN KEY (FKColumn)
                           REFERENCES Test1 (KeyColumn)
      );

SELECT   *
FROM     Test2
WHERE EXISTS
 (SELECT *
  FROM   Test1
  WHERE  Test1.KeyColumn = Test2.FKColumn); 
go

SELECT   Test2.*
FROM     Test2, Test1
WHERE  Test1.KeyColumn = Test2.FKColumn; 
go