In parent-child order, and delete in child-parent order (unless using ONīut that's a good practice anyway IMHO, and does make troubleshooting on In Oracle for example, all FKs are validated immediately at the statementĪnd *can* be deferred, typically to deal with circular references. But that's in the case where FK constraints validation is deferred to Violated during a transaction, as long as the violation is resolved Remember, an FK constraint is allowed to be Therein lays the rub: there is no way to tell which (if any) FKĬonstraint has failed until you have run the operation to the end andĬhecked them all. In case of a 'FOREIGN KEYĬonstraint failed' I'm temporarily fine with a not so fast and memoryĮfficent sqlite, as long, as it helps me, to understand the situationĭon't keep the bag, keep only one integer ID of the first failed The way out may be ly in the words "at once". Memory and helpful constraint failed message. You made crystal clear, that we can't have both at once: fast / less Obviously, the name of the constraint would It's faster and need lesser memory.Īlthough, the plea stays. Malloc().) Removing an element from a bag takes more time thanĪ good explanation. (In particular,Īdding an element to a bag probably involves one or more calls to More, depending on how many elements it holds.) Adding an element toĪ bag takes more time than incrementing a counter. Of all the constraints that have been violated and then removeĮlements from the bag as constraints are resolved.Ī bag takes more run-time memory than a single counter. Necessary to have a bag (a list or hash table or an associative array) To provide information about which constraint(s) failed, it would be At the end, if theĬounter is greater than zero then a "foreign key constraint" error is The current foreign key constraint mechanism uses a single counter.Īs constraints are violated, the counter increments, and asĬonstraints are resolved the counter decrements. It is a substantial change (basically a complete rewrite of the entireįoreign key constraint mechanism) which would negatively impact both Of memorizing the ID of the constraint, and then printing its name in How hard is it to add this information to the message? Is this a matter For this to happen, declare the Id column as either ROWID or INTEGER PRIMARY KEY.This message always leaves the user wondering: "Which constraint?" when working normally with the database, you can ommit the Id in INSERTs and SQLite will generate one for you.your original IDs will be preserved when the database is being rebuilt by using INSERT commands and explicitly specifying the Id!.
that you don't need to specify AUTOINCREMENT.It also means that the Id is only generated automatically if you don't supply one at INSERT. It only determines whether IDs of deleted rows will be reused or not. This means that AUTOINCREMENT is not required for rows to be auto-incremented. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. This is true regardless of whether or not the AUTOINCREMENT keyword is used. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. The documentation for SQLite Autoincrement says:
0 kommentar(er)
