Field Validation At Table Level

[ongoma]

Dear Friends


I wish to enforce a field rule on inventory [stockf.dbf]file. the file has stock code,stock description and stock category. The entry made in the the stock category must exist in a [st_cat]-categories table.

How would this be done using the SET CHECK caluse in ALTER TABLE-SQL command? Or any other method?

Answers will be highly appreciated

Ongoma

 

[Mike Lewis]

Ongoma,

If you want to build the rule into the DBC (either via SET CHECK or interactively in the table designer), you need to create an expression. The expression should return .t. if the category exists in the stock categories table, otherwise .f.

Obviously, your expression should use either SEEK() or LOOKUP() to determine if the category exists. If you can't write a simple expression to perform the test, write a function instead, then write an expression that calls the function.

Note that all of the above only applies if your inventory table is in a DBC. If it's a free table, you can't do this.

You asked about other methods. Do you mean other than via the DBC? If so, you can build the test into your user interface, for example in the Valid event of the relevant control.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

the check on existence of a certain record in another table is normally done via referential integrity checks. You need to store a primary key of the table, in which the existence of a reecord ios checked as a foreign into the table, from which the ralation to the checked table is starting.

Thn the coding is simply done by the referential integrity builder. This is then executed via insert/update/delete trigger, not via CHECK (field or table rule).

Another referential integrity check may be to check if an orderitem references an existing stock and then you may need an additional check, not only if the stock record exists, but if there is enough stock. That needs additional programming, but you can have some expression like "stockcheck() .AND. __ri_insert_orderitem()" as the insert trigger of that orderitem table.

The difficulty to do this at datbse level is, that the check may be insufficient, if there are many parallel orders of the same stock items. Each single order may be okay, but summed up the exceed the stock.

That's why it's better to use referential integrity for existence checks only and use restricting triggers mostly.

You can restrict insert or update of a stock, when it's stock category does not exist, you may also restrict delete of a stock category, as long as a stock referencing that category exists. But you may also define a cascading delete of all stock of a certain category, if their stock category is deleted.

 

[Olaf Doschke]

sorry, submitted too early.

If you limit referential integrity checks on existence of the referenced records, the checks on having enough stock should be done in the business logic, in the application.

Referential integrity check has a performance hit, but if you want it, it's a good idea to do it at database level, becuase then even administrative data manipulations can't break this rules.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

Thn the coding is simply done by the referential integrity builder. [/code]

I have to disagree with. I find the referential integrity builder clumsy and awkward, and the code it generates is verbose and buggy. I've also heard of cases where a large database, with many RI checks, generates so much RI code that it breaks the 64 K limit on the size of a procedure file.

I don't disagree with your general point about using RI checks and triggers, but I would advise anyone who needs it to write the RI code by hand.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Hi Mike,

you may change the code, the ri builder uses as a template. As the ri builder app is part of the xsource.zip: ...xsource\VFPSource\builders\RIBUILDR

And there are third party builders, that produce much better readable and working code.

I also assume, the ri code of vfps original builder is OLEDB compatible. That is limiting the use of some commands. And maybe that is the reason besides some other things, that the generated code is not easily understandable.

For third party ri builders take a look at:

http://fox.wikis.com/wc.dll?Wiki~NewRi~VFP

ftp://ftp.prolib.de/Public/VFP/
universal thread donload id 9923 (tax ri builder)

Bye, Olaf.

Bye, Olaf.

 

[ongoma]

Sorry guys, I have not checked this thread for some hours


MikeLewis,

I greatly appreciate your responses. My tables are in DBC and I'll take your piece of advice. I was thinking on those grounds of SEEK() but was not just sure whether it was the best way to go. I want to enforce most of the rules at the database level so that I dont have to worry about the database losing integrity due to unchecked entries.

Thanks so much Mike

-----------------------------------
Olaf,

Thanks for your eye opening response. I'll try it out. Although some things are best enforced at application level but thanks nonetheless

Thanks guys.


Ongoma