Howdy,
I've been trying to figure out what is meant by "external key" in LMCE's databases (EK_* attributes). This wiki page looks cut off before getting to the meat:
http://wiki.linuxmce.org/index.php/Naming_of_Foreign_Keys%2C_Indirect_Keys%2C_External_KeysGoogling around has left me a little confused. Is it just a table extension where the external key matches the primary key of the original table with a 1:1 mapping between tables? Is it a substitution or surrogate key?
An "external key" in a database is a key to a table (eg. "tableA") that is stored in a separate table (eg. "tableE") , with the data in the tableA key field just a reference to the actual key datat in the corresponding tableE field. It's like a foreign key, but the key is not a field in a separate table that contains any other data than the keys. It's a way of normalizing tableA even further, so tableA keys can be manipulated without referencing tableA at all. Reindexing keys doesn't have to access tableA, nor does changing the keys. Performance can improve if the keys are changed relatively often, compensating for the extra lookup to the external table when the keys are read. And programs can safely access the key table without touching the rest of the data associated with the key, for access control or just risk management (eg. if keys are expendable but the rest of the data isn't, an access to the key that could corrupt its table can be limited to just the key table, not the rest of the data that uses those external keys). External keys also allow techniques where multiple data tables each use the same external key table, for performance, consistency and other reasons.
