You need a UNIQUE index on the PARENT KEY because when you operate on a child, you need to be able to look up the PARENT. If there is no index on the PARENT KEY then you have to do a table scan. A table scan of a billion parent records many take quite some time.
Similarly, you need an index on the CHILD KEY because when you operate on a PARENT record, you need to be able to look up the CHILD. If there is no index on the CHILD KEY then you have to do a table scan. A table scan of a billion child records may take quite some time. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: Yuri [mailto:y...@rawbw.com] >Sent: Thursday, 2 August, 2018 17:06 >To: SQLite mailing list; Keith Medcalf >Subject: Re: [sqlite] Violated failed foreign key constraint delays >the rest of transaction ; Some foreign key violations don't trigger >the error at all > >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 > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
