Primary key on free DBF tables

[steve4king]

I think directly related to this thread. <thread184-1075152>
But it seemed to have conflicting answers:

Is it possible using free tables, to enforce uniqueness of a pseudo primary key (since there can be no primary key in free tables)

I know programmatically, I can check prior to inserting, but on a more basic level, can I dissallow duplicate values as if it had a primary key?

thanks

-Steve W.

 

[Dan Freeman]

You can create a CANDIDATE key on a free table. It will enforce uniqueness.

 

[steve4king]

ALTER TABLE CUST ALTER COLUMN CUST_NUM UNIQUE

returns: "function argument type or count is incorrect"

I see in the MSDN library that a candidate key should accomplish this and should be possible in free tables.
Yet the post I linked above states that a candidate index will allow duplicates while only indexing the first occurance (not useful to me)

Is my syntax off? Is this actually possible?
(My database is composed of free tables without an sql engine)

 

[Olaf Doschke]

You are confusing candidate indexes and unique indexes, because the keyword unique of the alter table command will make a candidate index on the field. Unique has a different meaning in vfp than it has in sql obviously.

Bye, Olaf.

 

[Mike Lewis]

Steve,

The error message has got nothing to do with the candidate key. The problem is that you need to specify the data type of the column that you are altering (even if it is the same as it was before). If you look at the Help for ALTER TABLE, you'll see that the data type is mandatory.

So, something like the following should fix it:

ALTER TABLE CUST ALTER COLUMN CUST_NUM [b]N(5)[/b] UNIQUE

As far as the UNIQUE is concerned, this does indeed create a candidate key, which is what you want. As Olaf say, you are confusing the SQL user of UNIQUE with the way that the native Foxpro language uses it.

Finally, you say that you don't have an "SQL engine". An SQL engine is built into VFP, and the language supports all common SQL commands (albeit with some occasional non-standard syntax).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

you can also use another syntax, to add a key, as you don't really alter a column:

ALTER TABLE CUST ADD UNIQUE CUST_NUM TAG xuCUST

That way you can also specify expressions instead of a field name only, and you specify a TAG name for that index.

Bye, Olaf.

 

[steve4king]

Perfect, thanks

-Steve

 

[Mike Lewis]

Steve,

Perfect, thanks

What's perfect? You've had several suggestions. If one of them worked, it would help other people to know which one.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[steve4king]

The one you suggested worked for me Mike.
Thanks for that, I did not notice in the MSDN documents that the datatype was necessary.

ALTER TABLE CUST ALTER COLUMN CUST_NUM C(6) UNIQUE

 

[Mike Lewis]

Glad it worked, Steve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk