I am starting to dig into databases (contra free tables). How do
I enforce the PK being unique? What expression do I use for my
validation rule?

While I am at it, how do I enforce FK being valid?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Make an index on the PK and make it a Primary type index. Those must be
unique.

Fred
Microsoft Visual FoxPro MVP


"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

[reordered to chronological]

"Fred Taylor" <ftaylor (AT) mvps (DOT) org!REMOVE> wrote:

Thank you. I only looked at the index part of the field tab and
missed that other tab.

Now what about integrity on FKs (foreign keys)?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

A stored procedure that opens the parent table if needed and then does a
Seek().
-Anders

"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

[reordered to chronological: Top posting is very difficult to follow
in the technical discussion.]

"Anders Altberg" <x_pragma (AT) telia (DOT) com> wrote:

[snip]

I am trying to avoid stored procedures, particularly for
something that should not need it.

What about the foreign key constraint? I have put this in, but
it appears to get ignored when I try adding records. (gendbc does
recognise it in an existing database table and generates the code for
it though.)

I might well be doing it wrong. One problem point: I really do
not see why there has to be an index on the FK column, but even having
created that index, FK integrity checking does not work.

This is my gendbc-generated code:

********** Start of Included Code ***********
************************************************** *******
* *
* * 07/06/04 TRY.DBC 09:12:31
* *
* ************************************************** *******
* *
* * Description:
* * This program was automatically generated by GENDBC
* * Version 2.26.67
* *
* ************************************************** *******


CLOSE DATA ALL
CREATE DATABASE 'TRY.DBC'
MakeTable_TWO()
MakeTable_ONE()
MakeRelation_1()

FUNCTION MakeTable_TWO
***** Table setup for TWO *****
CREATE TABLE 'TWO.DBF' NAME 'TWO' (THEKEY2 C(3) NOT NULL, ;
THEDATA2 C(10) NOT NULL)

***** Create each index for TWO *****
SET COLLATE TO 'MACHINE'
ALTER TABLE 'TWO' ADD PRIMARY KEY THEKEY2 TAG THEKEY2

***** Change properties for TWO *****
ENDFUNC

FUNCTION MakeTable_ONE
***** Table setup for ONE *****
CREATE TABLE 'ONE.DBF' NAME 'ONE' (THEKEY C(10) NOT NULL CHECK
RIGHT(TRIM(thekey),1)="9" ERROR 'Key should end with "9".', ;
THEDATA C(20) NOT NULL CHECK LEN(TRIM(thedata))=>10
ERROR "Data should be at least ten characters long.", ;
THELOOKUP C(3) NOT NULL)

***** Create each index for ONE *****
SET COLLATE TO 'MACHINE'
INDEX ON THELOOKUP TAG FK
ALTER TABLE 'ONE' ADD PRIMARY KEY THEKEY TAG THEKEY

***** Change properties for ONE *****
ALTER TABLE 'ONE' SET CHECK
LEFT(LTRIM(thekey),1)=LEFT(LTRIM(thedata),1) ERROR "Data should start
with the same non-blank character that the key starts with."
ENDFUNC

*************** Begin Relations Setup **************

FUNCTION MakeRelation_1
ALTER TABLE 'ONE' ADD FOREIGN KEY TAG FK REFERENCES TWO TAG THEKEY2
ENDFUNC


FUNCTION DisplayStatus(lcMessage)
WAIT WINDOW NOWAIT lcMessage
ENDFUNC
********** End of Included Code **********

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Gene ? and Primary Keys ?
in most of my existentence in this realm - the two don't fit into a Venn
diagram .

but - in the meantime [big boy] -
look for newid() function in the classlibs for 6/7/8/9
and go forth .
--
PS - welcome to the database container .
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL

"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

Hullo Big Gene ...
another THING .
for Primary Keys / Foreign Keys to WORK ?
you have to enforce referential integrity in the database container .
and *that* is always, I mean always [did I mention always? ] handled
via RI code in the DBC in triggers for insert / delete / update .
study the helpfile in the pirated IDE your boss has.
You'll be fine .
regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL

"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

Will this help?
http://www.craigberntson.com/Articles/kb006.htm

--
Craig Berntson
MCSD, Visual FoxPro MVP
Author, CrysDev: A Developer's Guide to Integrating Crystal Reports
www.craigberntson.com
email iamcraig (AT) craigberntson (DOT) com (remove iam)
Salt Lake City Fox User Group
www.slcfox.org


"Gene Wirchenko" <genew (AT) mail (DOT) ocis.net> wrote

"swdev2" <wsanders.bob (AT) bob (DOT) efgroup.com> wrote:

It happens that I do use PKs and have for over twenty years. It
is simply that they have been app-enforced. This was the correct
decision at the time it was made but is now being revised.

As a review of Venn diagrams, below is an example of two mutally
exclusive sets:

+-----------------------+

As you can now see, they do fit into a Venn diagram.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

"Craig Berntson" <iamcraig (AT) craigberntson (DOT) com> wrote:

No, as I got PKs fine. Now, the trouble is FKs.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.