Hi
How do i create a primary key index from two two or more columns in visual fox pro 9? The columns may be of different types
+1
A:
Compound indexes should be strings, so use the appropriate function (STR(), DTOS(), etc.) to convert the field before concatenating it. See the MSDN documentation for more details.
Ignacio Vazquez-Abrams
2009-12-28 10:45:20
Thanks, I got it now.
Prasad
2009-12-28 11:38:40
One additional caveat when converting dates (if applicable), as Ignacio mentioned, use DTOS() which will convert to a string in YYYYMMDD format. Depending on "SET CENTURY ON/OFF" in VFP, the century may only apply 09 instead of 2009 and give false finds during seeks.
DRapp
2009-12-28 14:29:33
DRapp - the DTOS() function is not affected by the SET CENTURY setting. It is one of the reasons this is better for indexing. The caveat is that you need to include the full century when doing a SEEK.
Rick Schummer
2009-12-28 19:57:09
A:
Another word of caution is to make sure you never trim the character representation of any of the columns included in the keys.
Something else you should be aware of is that referential integrity code generated by VFP is sometimes not clean or designed to work well with concatenated keys. Code is a lot simpler for surrogate keys (single meaningless column, normally integer or GUID). It might be too late in the design for you to consider this, but I will put it out here just in case it is still in the design stage or still a practical change to make.
Rick Schummer VFP MVP
Rick Schummer
2009-12-28 19:53:13