Hello Yuri, Friday, August 03, 2018, 12:06:14 AM, Yuri wrote:
> On 8/2/18 3:46 PM, Keith Medcalf wrote: >> You are required to have a UNIQUE index on the PARENT KEYS in a foreign key >> relationship. >> >> Depending on whether the PARENT:CHILD is 1:1 or 1:N you need either >> a UNIQUE (1:1) or regular index on the child key. > Why is index on PARENT KEY in a foreign key relationship required for > inserts? Missing index should slow down deletion of the target record in > FK, but insertions shouldn't need checking if such parent key is already > present or not. Insertion in the parent part of FK checks if the target > exists or not. If it exists, insertion succeeds, if not, it fails. It > doesn't need to check if another parent key already exists. > Yuri I believe David Raymond explained this: with deferred foreign key checks, once there has been a violation, it not only needs to check whether a new row _breaks_ a constraint, it needs to check whether the new row _fixes_ a previous violation. For the latter, while you don't _need_ the index Keith is suggesting, it prevents the slow-down you are seeing. Graham _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
