Good idea. I agree the constraints should certainly be in sync between the two files and it shouldn't hurt to tighten the constraints up.
I ran a script to compare vcl.sql and update-vcl.sql. The constraint differences are listed on the following page: https://cwiki.apache.org/confluence/display/VCL/Database+Schema+Constraint+Differences -Andy On Thu, Apr 30, 2015 at 11:38 AM, Josh Thompson <[email protected]> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > I noticed there are some differences in the "ON UPDATE" and "ON DELETE" > parts > of foreign keys between the vcl.sql and update-vcl.sql files. There tend > to > be more keys in update-vcl.sql that have these extra parts of the foreign > keys > set when they aren't specified at all in the vcl.sql file. They need to > be in > sync. > > However, that made me start thinking about when updates and deletes should > be > cascaded or set a field to null. There are quite a number of tables where > items really shouldn't be deleted (resources, log entries, etc). In those > cases, I don't think the deletes should be cascaded because it would add an > extra measure of protection to have foreign keys block deletes that > shouldn't > happen anyway. It does make sense to have deletes cascade or set null for > things like user groups and resource groups that actually can be deleted. > > For updates, almost all of the foreign keys are pointed at id fields from > other tables, which should never get updated. So, in those cases, I don't > think updates should be cascaded, again to provide an extra measure of > protection. I think the OS* tables might be the only places where a > foreign > key is pointed at something other than an id field. In those cases, > having an > update cascade would make sense. > > So, I'm proposing we remove cascaded deletes for fields where records > shouldn't be deleted and remove cascaded updates for fields pointing to id > fields from other tables. Does this sound reasonable? > > Thanks, > Josh > - -- > - ------------------------------- > Josh Thompson > VCL Developer > North Carolina State University > > my GPG/PGP key can be found at pgp.mit.edu > > All electronic mail messages in connection with State business which > are sent to or received by this account are subject to the NC Public > Records Law and may be disclosed to third parties. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v2 > > iEYEARECAAYFAlVCTIgACgkQV/LQcNdtPQNx8gCfdN7bNM1AlQLSJhic0S7gmuHt > xfQAniE0HqBHv5iUKSoUWPnyIinTw481 > =g67y > -----END PGP SIGNATURE----- > >
