CODEWITHSUNDEEP
sqlcascading-deletes
VansFannel
VansFannel

Reputation: 45961

foreign keys without on delete cascade

I have two tables:

Challenge:

challengeID INTEGER PRIMARY KEY
...

And

UserChallenge:

ID INTEGER  PRIMARY KEY
challengeID INTEGER NOT NULL
...
FOREIGN KEY(challengeid) REFERENCES challenge(challengeID)

If I have one row on challenge table, with challengeID = 1, and one row on UserChallenge table with challengeID = 1.

What would happen with UserChallenge table if I delete the row from the Challenge? May I use ON DELETE CASCADE with UserChallenge.challengeID?

Thanks.

Upvotes: 0

Views: 5354

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127476

You could do a test...

If you delete the row from Challenge, the constraint on challengeID in UserChallenge gets violated. You have to delete all referencing records in this table as well, or update these records using another (correct) value or use a NULL. You could also use ON DELETE CASCADE when you want to delete the referencing records.

Upvotes: 2

sjngm
sjngm

Reputation: 12871

With DELETE CASCADE removing a record from Challenge causes all child records in UserChallenge to be deleted.

Maybe this helps: http://en.wikipedia.org/wiki/Foreign_key#Referential_Actions

Upvotes: 1

Related Questions