This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19. The C language interface to SQLite Version 2 Upgrading SQLite, Backwards Compatibility Locking And Concurrency In SQLite Version 3 NULL Handling in SQLite Versus Other Database Engines I'm fine with putting pragmas all over my code.An Introduction To The SQLite C/C InterfaceĬ-language Interface Specification for SQLite (Just to be sure, this isn't a feature request. Which wouldn't break existing code (at least I don't see how it could break existing code) and would allow future code to just enable support for foreign key constraints for all connections. can be changed for \ / is set at data- \ PRAGMA foreign_keys -> stored option -> support disabled The stored option could be implemented as shown in the diagram below. ( File Format Changes in SQLite)Īnd I do not see any major problem adding this option to the file format while leaving the foreign_keys pragma intact. There have been enhancements to SQLite such that newer database files are unreadable by older versions of the SQLite library. I can see how your first problem contributes to not having this option stored in the database file, but problems two and three should have already been taken care of by the fact that support for foreign key constraints is disabled by default, right? And introducing changes to the database file format (like whether or not support is enabled) while leaving the default behavior unchanged shouldn't be of great concern either, because Unfortunately they hadn't, so now we have to handle the more complicated case. If FOREIGN KEYs had been implemented when the database format was devised the problem would not exist. And you don't know what that means either, because you're a user not a programmer. So it just reports "Unknown error 787." to you. But the software doesn't know what that error is or how to handle it because it was written before error code 787 existed. You try to delete a row (which happens to be a parent row with RESTRICT set) and the SQLite API returns 787. And it was written before FOREIGN KEYs were implemented. Suppose you are running a general database tool which allows you to view databases, insert and delete data, etc. Changing that would require people to rewrite their software.Ī new error message which old software didn't handle, because it didn't exist. So there's no obvious place in the database file format to store whether they're ON or OFF. Why is this option based on the connection? And are there any "reasonable" scenarios where it is useful to allow one connection to insert ghost references while disallowing this for other connections?įOREIGN KEYs were introduced long after the database format was designed. Maybe someone can share some insights on this subject. If I create a database which relies on foreign key constraints, shouldn't this feature be enabled for each and every connection to that database? I am having trouble coming up with a scenario where having support for foreign key constraints as an option for the connection is of any use. More precisely, I am wondering why support for foreign key constraints is an option for the database connection and not an option for the database file. Though I understand the reason behind disabling this feature by default, I do not understand why it is implemented the way it is. According to the section "Enabling Foreign Key Support" in the documentation the support for foreign key constraints must be enabled manually for each database connection via PRAGMA foreign_keys = ON
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |