In a project I am working on, I have 2 tables that I must link. These tables are based on imported data that is beyond my control. There is a series of fields that would make an excellent primary key except the data is alphanumeric, i.e. 66925E100R00. Any thoughts out there to make this work?
To be perfectly honest, it is generally good practice to institute your own primary keys. If the external information is beyond your control, what happens if the third party completely changes their database schema and produces a duplicate record or a new key that's in an unexpected format? I have a strong personal preference to defining and utilizing your own internal primary keys and retaining the supplied key for reference purposes only.
Nothing wrong with using theres aside from that, all my humble opinion.
Teddy's right. You should enforce data integrity for those fields you find if they are supposed to be unique, but the best idea is to create a new ID field - perhaps using using an autonumber data type. Use that as your primary key.
That is unless the data you have imported consists of multiple tables already with their own foreign and primary keys.
Well, first of all,Access help says: "You cannot create a primary key using columns with a text, ntext, image, or bit data type." and that is a cut and paste right from Access help, troubleshooting primary keys. As to making my own key field, I don't see how it would help with matching the 2 tables to each other. Any more ideas?