trouble with foreign key

Question

trouble with foreign key

krypto69

SSChampion

Points: 13604
More actions
August 26, 2010 at 10:38 am

#226230

I have this statement that breaks due to a foreign key constraint. having a tough time figuring out how to get around it:
delete from Security_LoginActivity
where
UserClientMetaDataId in
(
select
a.UserClientMetaDataId from Security_LoginActivity a
inner
join Security_UserClientMetaData b on a.UserClientMetaDataId = b.UserClientMetaDataId
where
LoginTraceId is not null
)
delete
from Security_UserClientMetaData
where
LoginTraceId is not null and LoginTraceId
< (select min(LoginTraceId) + 50000 from Security_UserClientMetaData
where LoginTraceId is not null
)
Error is:
Msg 547, Level 16, State 0, Line 9
The DELETE statement conflicted with the REFERENCE constraint "FK_Security_UserClientSignature_Security_UserClientMetaData". The conflict occurred in database "ASPNETDB", table "dbo.Security_UserClientSignature", column 'UserClientMetaDataId'.
The statement has been terminated.
Works fine if I delete the FKey

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

It looks like the metadata table needs to be deleted first.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

krypto69 (8/26/2010)
...
The DELETE statement conflicted with the REFERENCE constraint "FK_Security_UserClientSignature_Security_UserClientMetaData". The conflict occurred in database "ASPNETDB", table "dbo.Security_UserClientSignature", column 'UserClientMetaDataId'.
The statement has been terminated.
Works fine if I delete the FKey

The answer is in Error:

Security_UserClientSignature references UserClientMetaDataId in Security_UserClientMetaData.

Therefore youn should delete Security_UserClientSignature rows which references UserClientMetaDataId which you want to delete. Or, if allowed, UserClientMetaDataId must be set to NULL in Security_UserClientSignature for these rows.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]