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

[Josh Thompson <jo...@ncsu.edu>]

-----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-----

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-----
>
>