[dba-SQLServer] Update Foreign Keys

Francisco Tapia fhtapia at gmail.com
Fri Jul 15 08:57:07 CDT 2011


What a great concept!

Sent from my mobile

On Jul 15, 2011, at 6:47 AM, David McAfee <davidmcafee at gmail.com> wrote:

> Create a junction table for installs.
>
> tblInstalls
> InstalledID (PK, INT)
> LocationID (FK, int)
> MachineID (fk, int)
> Installdate
> EntryDate
> Entryuserid
>
> Every record is an insertion.
> You never have to overwrite data.
> Built in history.
>
> A simple view/sproc using Max() can show the latest location location for a
> given machine or machines at a given location.
>
> HTH,
> David
>
> Sent from my Droid phone.
> On Jul 15, 2011 5:15 AM, "Arthur Fuller" <fuller.artful at gmail.com> wrote:
>> I'd like to poll the readership to ask, "Do you permit FKs to be updated,
>> and if so under what circumstances?"
>>
>> I'm asking because a client and I are discussing a situation where this
> has
>> arisen:
>>
>> A Client may have several locations.
>> A Location has zero or more machines installed.
>> A Machine has related data in at least one table (Assessments and
> optionally
>> Measurements).
>>
>> From time to time the Client may want to move a Machine from one Location
> to
>> another.
>>
>> The client suggested that I simply replace the FK LocationID on the
> Machine
>> record with the LocationID of the new Location. I pointed out that there
> are
>> two possible results to this operation:
>>
>> a) do a Cascade Update on the tables under Machines. This approach
> "destroys
>> history", so to speak, in that the data really no longer applies to the
>> relocated Machine. The Assessments and Measurements no longer apply to the
>> new Location.
>> b) Orphan the Assessments and Measurements. This is unacceptable, IMO.
>>
>> So I suggested that rather than change the Machine's LocationID, we
> instead
>> copy the Machine data (only) to a new row, assigning it the new LocationID
>> and leaving the old row intact, along with its Assessments and
> Measurements
>>
>> In a somewhat related topic, "Do you permit Cascase DELETEs, and if so,
>> under what circumstances?" I'll respond to that one first. The only time I
>> permit this is when using staging tables. For example, a wizard may accept
>> new data into several tables. The last step in the wizard is equivalent to
>> "COMMIT" -- it writes the accumulated data to the "real" tables. There is
>> also a "Cancel" button, which if pressed causes a Cascade Delete across
> all
>> the tables involved.
>>
>> Arthur
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>



More information about the dba-SQLServer mailing list