how to find records that violate referential integrity

Question

I have two tables that should be in a one-to-many relationship but there seems to be some records on the many side of the table that are preventing the relationship from being created. Violates referential integrity.

Since there are lots of records in both tables is there a way to query to see which records are in the many side, but not in the one side?

**Ex.**

Table 1: (one side)
(pk)AccountId


Table 2: (many side)
(pk)UserId
(fk)AccountId  <--  Some accountId's are not in Table 1

@AaronBertrand - the problem stated is that the foreign key creation failed — D.Shawley, Jun 06 '12 at 19:57
Sorry, missed the buried text "preventing the relationship"... the code sample makes it look like the foreign key constraint is already there. — Aaron Bertrand, Jun 06 '12 at 19:58

Zhenny · Accepted Answer · 2012-06-06T20:06:14.590

11

select *
from table2 t2
where not exists(
    select 1
    from table1 t1
    where t1.AccountId = t2.AccountId
)

edited Jun 06 '12 at 20:06

answered Jun 06 '12 at 19:52

Zhenny

811
6
9

score 4 · Answer 2 · answered Jun 06 '12 at 19:52

4

select a.*
from Table2 as a
where not exists (select null from table1 as b where b.AccountId = a.AccountId);

answered Jun 06 '12 at 19:52

Jeremy Holovacs

22,480
33
117
254

score 3 · Answer 3 · answered Jun 06 '12 at 19:52

3

SELECT table2.UserId, table2.AccountId
FROM table1 RIGHT JOIN table2 ON table1.AccountId = table2.AccountId
WHERE table1.AccountId IS NULL;

http://sqlfiddle.com/#!3/5b8e30/4

answered Jun 06 '12 at 19:52

Ruben

2,488
1
18
22

2

I highly recommend re-writing as a LEFT OUTER JOIN. RIGHT JOINs are not intuitive for most users. – Aaron Bertrand Jun 06 '12 at 19:56
Thank you for your advice. For me RIGHT JOIN is anti-(LEFT JOIN). LEFT OUTER JOIN is not intuitive for me in this case. – Ruben Jun 06 '12 at 19:58
2

Well you are the exception in my experience. Are you writing the answer for you or for the rest of the community? – Aaron Bertrand Jun 06 '12 at 19:59
For the rest of community, of course. If community will check answer and see it is working, they will use RIGHT JOIN and it will become more intuitive for them. – Ruben Jun 06 '12 at 20:00
Bwahahahaha ok. Things don't become magically intuitive just because they get copied and pasted and the right result comes out. – Aaron Bertrand Jun 06 '12 at 20:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12217/discussion-between-ruben-and-aaron-bertrand) – Ruben Jun 06 '12 at 20:03
I agree with Aaron. Stick to LEFT or LEFT OUTER form. Nor am I going to pointlessly debate this. – Thomas W Dec 31 '15 at 01:03

score 2 · Answer 4 · answered Jun 06 '12 at 19:54

2

FROM Table2 t2
WHERE t2.AccountId not in (SELECT t1.AccountId FROM Table1 t1)

Or if you prefer a join...

FROM Table2 t2
  LEFT JOIN Table1 t1
  ON t2.AccountId = t1.AccountId
WHERE t1.AccountId is null

answered Jun 06 '12 at 19:54

Amy B

108,202
21
135
185

1

You *should* prefer a join or, better yet, EXISTS. `NOT IN` is never a good practice, because in this case (or in other cases where folks might learn from this and apply in their own scenario), the column in the subquery could be NULLable, which returns unknown, which makes not in false, which means you'll return all rows. – Aaron Bertrand Jun 06 '12 at 19:56
@Aaron Bertrand : A nullable primary key... Surely you jest? – Amy B Jun 06 '12 at 19:57
not the point. Did you read my whole comment? Also since the foreign key doesn't exist yet, how can you be sure the primary key already exists on both tables? – Aaron Bertrand Jun 06 '12 at 19:57
Sure, you made a great non-applicable comment. Also, I'm quite aware of the issue you raise and have a good answer regarding it here: http://stackoverflow.com/questions/296146/sql-query-question-select-not-in/296158#296158 – Amy B Jun 06 '12 at 20:00
Non-applicable? Why? Because you know for a fact that every future reader that sees your answer is going to have a non-nullable column in the subquery? – Aaron Bertrand Jun 06 '12 at 20:01
@AaronBertrand yes, if they are querying for referential integrity. – Amy B Jun 06 '12 at 20:02
You're totally missing the point, either on purpose, or because I haven't explained myself well enough. Sorry about that. – Aaron Bertrand Jun 06 '12 at 20:03
`NOT IN`, in this sort of usage, can be a serious performance hit in just a slightly more complex theory. Best to avoid this practice. – Jeremy Holovacs Jun 06 '12 at 20:05
@AaronBertrand, SQL Server does not allow you to have a nullable PK, so that may be a moot point. – Jeremy Holovacs Jun 06 '12 at 20:05
Optimizer takes both queries and generates the same plan. Either syntax is fine. – Amy B Jun 06 '12 at 20:06
@JeremyHolovacs no, but you can have a nullable unique constraint and point a foreign key at that. But in any case that wasn't my point - future readers (aside from the OP) might not have PKs or might be interested in cases where the 2nd table doesn't have a value from the first table that's not a PK at all. – Aaron Bertrand Jun 06 '12 at 20:09
@AaronBertrand, true, but your comment may have confused them further; it seemed to imply it was not only possible but fairly common to have a nullable PK. – Jeremy Holovacs Jun 06 '12 at 20:11
1

@JeremyHolovacs The point of my comment was simply that NOT IN is a bad pattern. Period. I think there is now plenty of griping and moaning for future readers to be able to make up their own mind. – Aaron Bertrand Jun 06 '12 at 20:12
@DavidB, In this case it will; it's a very simple query. As I said, if it gets a little more complex, the `IN` clause has a very different behavior from the antijoin. It will collect all permutations possible inside the parenthesis and evaluate them against the left side of the WHERE clause. This can get bad. Quickly. – Jeremy Holovacs Jun 06 '12 at 20:19
@Jeremy Holovacs It is my preference to only create queries in which I have some shot at understanding what the optimizer will do with it, rather than rely on voodoo principles (such as: never subquery). I realize I have limitted the complexity of the queries I can build with this approach, but that can be a good thing. – Amy B Jun 06 '12 at 20:29
1

@DavidB, it's all fun and games until people start complaining about a 20 minute query that should return almost instantly. it's a bad practice, and in most real-use cases, significantly less efficient. Suggesting that an efficient antijoin is voodoo... well I don't know how I can respond to that, but I'd expect your DBA's would give you some crap about it :) – Jeremy Holovacs Jun 06 '12 at 20:35
@JeremyHolovacs antijoin isn't voodoo - avoidance of subquery is. Those practitioners of that voodoo can go back to sql server 2003 or whatever gimp MySql optimizer that has that kind of problem. – Amy B Jun 06 '12 at 20:52

score 0 · Answer 5 · answered Sep 02 '21 at 20:50

0

In SQL SERVER you can use this command:

 DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

answered Sep 02 '21 at 20:50

Juan Bosco Rodríguez

36
4

how to find records that violate referential integrity

5 Answers5

Linked