0

I'm at a total loss here.

I have a SQL Server database with a central, most critical table, with many, many child tables that have foreign key relationships to it. The foreign key checking is enabled on all tables. I ran EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" just to be sure.

Yet somehow there are records in multiple child tables that have foreign keys referencing 6 records in the parent table that do not exist.

HOW IS THIS POSSIBLE?

I ran DBCC CHECKDB to see if maybe there was database corruption and I got:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'xxxxxDev'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What am I missing here? How can this be? I'm "shook".enter image description here

Jim Burnell
  • 948
  • 3
  • 9
  • 21
  • Could you check if this query returns any rows: select * from sys.foreign_keys where is_not_trusted = 1 – Piotr Palka Mar 06 '20 at 00:06
  • No rows returned by that query – Jim Burnell Mar 06 '20 at 00:08
  • 1
    It's possible foreign key checking was turned off temporarily during insert/update e.g. https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql – Nick Mar 06 '20 at 00:08
  • The thought had occurred to me, but if that were the case, shouldn't EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" fail? – Jim Burnell Mar 06 '20 at 00:10
  • @Piotr yes - didn't see your comment while I was writing mine... – Nick Mar 06 '20 at 00:11
  • 1
    Sounds like corruption or engine bug, I would recommend opening a case with Microsoft. – Piotr Palka Mar 06 '20 at 00:12
  • Can you make a backup of this DB, turn off this constraint and then try to re enable it? – Piotr Palka Mar 06 '20 at 00:15
  • It's a development DB. I'll drop and re-create the constraint... it completed successfully... *smh* – Jim Burnell Mar 06 '20 at 00:19
  • 1
    Could you check if you have any Row-Level Security enabled for parent table? – Piotr Palka Mar 06 '20 at 00:30
  • HAHA! Thank you! I forgot that another developer was experimenting with filter predicates... That's it, I think: ALTER SECURITY POLICY [dbo].[programAccessPolicy] ALTER FILTER PREDICATE [dbo].[programPredicate]([ID]) ON [Mongoose].[Program], ALTER BLOCK PREDICATE [dbo].[programPredicate]([ID]) ON [Mongoose].[Program] – Jim Burnell Mar 06 '20 at 00:35
  • How can I find the definition of [dbo].[programPredicate]? – Jim Burnell Mar 06 '20 at 00:35
  • Found it. Thank you! – Jim Burnell Mar 06 '20 at 00:43

2 Answers2

2

Possible reason for this is a Row-Level Security enabled for parent table.
It may be hiding rows from your query.

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
0

Thank you "Piotr" (https://stackoverflow.com/users/10542581/piotr) for the answer.

A developer had added a predicate to the security policy for that table (row-level security) that was hiding the rows. He never removed it, and I forgot it was there.

I thought I was going nuts...

Jim Burnell
  • 948
  • 3
  • 9
  • 21