Dbo: How to add SQL constraints?
Added by Rob Farmelo over 14 years ago
Hi,
I find that I'm able get a session.add() to succeed on an object that has an un-initialized (null?) FK, even though there is a foreign key constraint produced for the table by Dbo. I'd like to prevent Dbo/sqlite3 from allowing this kind of operation to succeed.
By adding the "NOT NULL" constraint manually, I was no longer able to add a record with a null key.
Is there a way to programmaticly get Dbo to specify a FK to be "NOT NULL" or do I need to do something like do perform an 'alter' operation via session.execute()?
Is there some reason why Dbo does not specify this by default for FKs?
Thanks,
Rob
Replies (7)
RE: Dbo: How to add SQL constraints? - Added by Rob Farmelo over 14 years ago
I'd also like to add the following constraints for FKs:
ON DELETE CASCADE
ON UPDATE CASCADE
Turns out Sqlite3 doesn't support 'alter column', so I'd like to know if there's a way to have Dbo add these to the SQL. I can probably work around this by not using Dbo to create my schema, but any suggestions would be appreciated.
Thanks,
Rob
RE: Dbo: How to add SQL constraints? - Added by Koen Deforche over 14 years ago
Hey Rob,
I've added this in latest git. You can now add NotNull, OnDeleteCascade, OnUpdateCascade (and OnDeleteSetNull, OnUpdateSetNull) flags to belongsTo() and hasMany(/* ManyToMany */) relations. These methods have only an effect on database schema creation.
Regards,
koen
RE: Dbo: How to add SQL constraints? - Added by Rob Farmelo over 14 years ago
Koen,
I'm unclear about what I'm allowed to do to an object before it's persisted.
This becomes an issue when I need to set an FK before adding an object to the DB to satisfy
constraint checking.
Can I set the value of the dbo::ptr FK in an object before I persist it? What would be the syntax?
I'm still trying to wrap my head around the dbo::ptr...
If not, should I be able to persist the object without setting the FK then within the same
transaction, set the FK and finish with commit? I'm doing that now and it seems to work, but sqlite docs
say that you have to add a qualifier to the FK description SQL to enable "deferred" constraint checking
which checks at the end of the current transaction. I don't see that sql coming from Dbo and am confused
as to why it's working for me.
Lastly, how to I get the ID of a persisted object? I can't seem to figure out the id() method.
I really appreciate all the speedy, high quality responses you've been providing as I work through
Dbo! Thanks for bearing with me on some of the straight C that stuff that I'm struggling with too!
Rob
RE: Dbo: How to add SQL constraints? - Added by Koen Deforche over 14 years ago
Hey Rob,
These are fair questions. The process of issuing SQL insert/update/delete statements to the database, which reflects changes you make to objects, is called "flushing". Flushing is postponed until either you explicitly ask for it, until a query is run, or until the current transaction is committed.
That means that the order in which you make updates to Wt::Dbo objects does not matter that much --- Wt will automatically flush the changes as a batch and in the order which maximizes chances to satisfy foreign key constraints. I guess we should be more explicit about this behavior.
A dbo::ptr<> class really satisfies pointer semantics: you can set them using '=' at any time, and this has no instant effect on the database, until the current changes are being flushed.
I'm not sure what the issue can be with the id() method, it works like this:
dbo::ptr<A> a = ...;
std::cerr << a.id() << std::endl;
Regards,
koen
RE: Dbo: How to add SQL constraints? - Added by Rob Farmelo over 14 years ago
Hi Koen,
Thanks for the explanations. That's how I understood transactions to work but was seeing a case were it didn't seem to be holding. I've since concluded that I had a problem with my logic and all is working as expected now.
As I was not using the right syntax for a.id(). I'm still getting used to the idea of the smart pointer :)
Best regards,
Rob