Re: foreign key differences between vcl.sql and update-vcl.sql

[Andy Kurth <an...@ncsu.edu>]

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 <jo...@ncsu.edu>
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-----
>
>