Re: Is it possible to update Primary Key Columns ?

On 10 Apr 1997 14:35:40 GMT, kevin_at_cpm.com (Kevin Bourrillion) wrote:

>>If no non-updatable set of attributes can be found for a primary key, it
>>is worth considering a _surrogate_ or _artificial_ key (possibly
>>system-generated).
>
>This sounds like what I want to do with a table of mine. How do you
>get a system-generated key? I have a table with five columns, about
>four of which would be required to form a primary key. I'd rather have
>a sixth column be an ID number (eg, sequentially assigned) that is used
>for internal purposes only. Is there an easier way to do it than the
>brute-force method?

Please avoide these, make every effort NOT to use a system generated key. They add no information value to the data collection, therefore they are (by definition) a reduncancy. They are a potential harmful redundancy in that they have to always be 'translated' to something user-intelligible when putting it on a user-interface (a screen, or a report). This causes an extra join in almost every SQL statement that uses this data, which is a performance drain (when considering the system as a whole, not one individual SQL statement).

Just ask ourself the six 'journalistic interogitives' about the real-world object that a row in the table will represent:

What is this thing?
Where is it?
How did it come into being, or change its condition (so as to become within the scope of being represented in the database) When did it get created (or changed)
Who created/changed it?
Why does it exist/Why was it changed?

Then ask yourself similar questions about the electronic representation of that real-world object:

What does the row represent about the real-world object? Where will it be inserted (database instance)? Who will take the initiative record the information used to initiate the insertion of the row?
How will we (the corporation) become aware of the information about the real-world object? How will we want to record it? When (in the real-world objects life-cycle) will we become aware and intrested in the real-world object?
Why are we intrested in this information about the real-world object?

Once you have answered these questions, a 'naturally occuring' unique combination of attributes will most likely make themselves known.

Please, please, please do not create 'artificial keys' just because the DBMS allows it, or you want to be able to say you have done it in a future job interview. Received on Sat Apr 12 1997 - 00:00:00 CDT