Foreign key has problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Foreign key has problem

Author

Topic

jpham
Starting Member

19 Posts

Posted - 2007-02-07 : 18:27:09

Hi All,
I have problem to create the foreign key on one of my table and
I would like to find out which row is violated the rule but
I don't know what is the good way to do it. For
example the tableA (Parent table) has primary key as Col1, Col2
and the tableB(child table) has Col1 and Col2 and I want
to create the foreign key of Col1 and Col2 of TableB(child) to
reference to parent table(TableA) but I have problem because
some rows in TableA(Col1, Col2) didn't exist in TableA. How
to find all these rows in TableB that violated this rule.
Thanks In advance,
JP

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-02-07 : 23:53:48

You want to create foreign key relation in the middle
i mean u have data in your tableb

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 00:01:54

[code]Select t1.*
from TableB t1
LEFT JOIN TableA t2
on t1.col1 = t2.col1 and t1.col2 = t2.col2
Where t2.Col1 IS NULL and t2.Col2 IS NULL[/code]

OR

[code]Select *
from TableB t1
Where not exists(Select * from TableA t2 where t1.Col1 = t2.col1 and t1.col2 = t2.col2)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jpham
Starting Member

19 Posts

Posted - 2007-02-08 : 12:43:15

Thank you so much! It worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources