Automatic computation of field from values of other fields during INSERT

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Automatic computation of field from values of other fields during INSERT

This should be basic for SQL experts:

Every time I create a record (INSERT) in my table, I want field 1 to be unique and it will be determined values in three other fields, two which are variable length strings and one which is date type. It works by concatenating the strings (the date is cast as a string, either as a Unix timestamp integer or as readable expression of the date...it's all arbitrary) and then computing the CRC32 value of the concatentation.

field1 = CRC32(concatenation of field2, field3, timestamp? form of field4)

field1 becomes my "document ID" column

But I don't want to write the SQL in the phpMyAdmin interface I am presently using to work on the records every time I do an INSERT/UPDATE. I want it to be automatic. I want the database/table/server to execute the procedure on that table when an INSERT/UPDATE is done, and the field1 (column1) should not even be presented by phpMyAdmin as modifiable to the user when the INSERT/UPDATE interface is presented, since it is computed.

If that is so, all you need to do is create a primary key column called document_id and set it as an auto-increment integer. When you then insert values into the other columns in the table, document_id will be automatically incremented by 1.

Actually that is not all I really want. For example, my table records represented web documents posted to a web site, the fields of the record include document titles and subtitles, dates the document was initially posted and modified, and perhaps even the entire contents of the document itself. I might want to compute the MD5 value of one or more of those fields, and have that automagically put into a hash value (MD5, SHA-1, CRC32...however it is calculated) field in the record during INSERT or UPDATE operations. And I might want to do a REPLACE on records already present to make sure the value is set properly, assuming I change the mode of computation for the field.

If that is so, all you need to do is create a primary key column called document_id and set it as an auto-increment integer. When you then insert values into the other columns in the table, document_id will be automatically incremented by 1.

I am familiar that operation. What I really want the AUTO INCREMENT to do is follow a custom computation, not its x = x + 1 of an int value. Indeed, the computed "doc ID" may not even be an integer.

What is more, I don't want to have to remember the SQL myself for that field on an INSERT/UPDATE. I want it to be part of the marrow of that table when such operations are done. I am guessing that something has to be done to one of the tables in the "mysql" database to get this procedure executed....a trigger of some kind.

A non-integer PK index will slow complex queries. I would recommend making an additional field if you want a combined indexed field for your own purposes. It is also a best practice to not alter the PK once the table is in use and from your explanation it sounds like you are creating a human readable short form field. Is that correct?