While doing some pre-checking on a table before adding a new Foreign Key, we were querying to see how many current rows would violate the new key. This is a fairly active database with near constant inserts on the tables in question.
The FK would be from MessagePatientIdentifier.MessageID to Message.MessageID.
The query we used was as simple as could be:
select * from MessagePatientIdentifier as mpi where MessageID not in (select MessageID from Message)
The issue that we are seeing, is that rows are being returned from MessagePatientIdentifier
MessagePatientID | MessageID 553bde76-47d4-4ec3-96d1-b5d2e98931e1 | 7d45464d-8cc4-4a2e-8828-020722165b39
in this case and when you then
select * from Message where MessageID = 7d45464d-8cc4-4a2e-8828-020722165b39, the record does in fact exist.
We then moved on and tried the same query, but on a table that DOES have a FK in place, referencing Message table in the same way. The same results...the query is reporting that Child table records exist without parent the corresponding parent table (Message) record.
MessageRecipID | MessageID 26d6d632-87b3-407e-aeb0-04552981e5f8 | 750f0fb4-3e6c-485d-996e-f061f8caa360
Then, once again if you
select * from Message where MessageID = 750f0fb4-3e6c-485d-996e-f061f8caa360, this returns the record.
This data comes in via stored procedures from a Mirth Server as well as through a BizTalk WCF-SQL send port. The proc inserts the message record, gets the new
uniqueidentifier as an output variable and then uses that to call auxiliary stored procs to insert into both the
Is this expected behaviour and I'm just not educated on the inner workings of SQL? Technically I believe BizTalk runs everything in a transaction, so it shouldn't get out of sync, even if it did where would it get the MessageID value if not from the Message insert?
What am I missing here?