Finding columns that do not match existing primary key

Question

I'm trying to add a Foreign Key to a table, but database, Sql Server 2005, does not like it.

It says that columns do not match an existing primary key or unique constraint.

How can I find the columns in question so I can delete/change them and add the foreign key?

Answer 1

Do a left join to the parent table on the key in question, then examine the values in the child table where the value in the left-joined parent table are null.

For example, if this was your schema...

table1:
    myKey int primary key,
    ...other columns...

table2:
    otherKey int primary key,
    myKeyFromTable1 int
    ...other columns...

You'd do this:

select distinct
    t2.myKeyFromTable1

from table2 t2

left join table1 t1 on t1.myKey = t2.myKeyFromTable1

where t1.myKey is null

That would give you the distinct values in table2 that wouldn't have a corresponding parent in table1.

Answer 2

SELECT 
   ForeignKey 
FROM 
   FK_TABLE f 
LEFT JOIN 
   PK_TABLE p ON f.ForeignKey = p.PrimaryKey 
WHERE 
   p.PrimaryKey = NULL

That should do it.

ForeignKey = the column you want to make into a foreign key
PK_TABLE = the table you want the foreign key to reference
PrimaryKey = the column ForeignKey will be a foreign key to.

Answer 3

SELECT *
  FROM FK_Table
 WHERE ForeignKey NOT IN (SELECT PrimaryKey FROM PK_Table);

This works as written for single-column keys. It can also work for multi-column keys if your DBMS allows the notation:

SELECT *
  FROM FK_Table
 WHERE (FK_Col1, FK_Col2) NOT IN (SELECT PK_Col1, PK_Col2 FROM PK_Table);

Not every DBMS supports this, though. This formulation with NOT EXISTS should work most places:

SELECT *
  FROM FK_Table
 WHERE NOT EXISTS (SELECT 1
                     FROM PK_Table
                     WHERE FK_Col1 = PK_Col1 AND FK_Col2 = PK_Col2
                  );

Answer 4

So you have TableA with a column X, and TableB with column Y. And you want to make Y a foreign key, such that all values of TableB.Y are values in TableA.X, correct?

To do so, TableA.X needs to have either a primary key or a unique constraint on it. It sounds like that is not the case. Make TableA.X unique first, then define your FK from TableB.Y referencing TableA.X