How do I add a foreign key constraint using SQL? I have looked
at docs. I have put something together that apparently ought to work.
It does not work. gendbc takes my database and generates code that
apparently ought to work, too. It does not work either. In both
cases, the FK constraint is ignored. The column and table constraints
work though.

Could someone please post an example that does work for foreign
keys?

No, Anders, not a stored procedure. I want to know why the SQL
is not working.

Sincerely,

Gene Wirchenko

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

So, you have written a SQL CREATE TABLE command that includes
foreign key constraints that reference primary keys in other
existing tables, but the constraints are not observed when
the data in the tables is manipulated?

I have tons of foreign key constraints in my databases and
they all work fine. But I did not create my tables using
SQL - I used the database designer. Could you post a simple
example of the two SQL create table commands (parent and
child tables) that do not work?

-- TRW

Gene Wirchenko seemed to utter in news:eenme01os828g800kh48pao40v91irunau@
4ax.com:



--
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

[reordered to chronological]

trw7at (AT) ixdot (DOT) netcomdotcom (Tim Witort) wrote:

Quite.

I have done both. (You do mean "Table Designer" not "database
designer", right?) Setup that I do through the Table Designer does
not work though gendbc picks up my design and generates the code I
expect. Its code does not work either though.

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

CLOSE DATA ALL
CREATE DATABASE 'TRY.DBC'
MakeTable_TWO()
MakeTable_ONE()
MakeRelation_1()
insert into two (thekey2,thedata2) values ("11","un")
insert into two (thekey2,thedata2) values ("22","deux")
insert into two (thekey2,thedata2) values ("33","trois")
insert into two (thekey2,thedata2) values ("44","quatre")

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 references two)

***** 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 Inserted Code **********

This is fairly straightfoward. Some of the longer lines may
wrap, and you may have to adjust that. I have added some inserts to
populate the lookup table. After this runs
insert into one;
(thekey,thedata,thelookup);
values ("alpha9","alphabetically","33")
should be accepted and is, and
insert into one;
(thekey,thedata,thelookup);
values ("beta9","befuddlement","55")
should not be accepted but is.

If you check with other values, you can see that the column and
table validation works.

It is surprising how little there is on the Web on this.

Sincerely,

Gene Wirchenko

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

what's your beefs with stored procedures?
-Anders

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

Gene Wirchenko seemed to utter in
news:73aoe0phf82kkm9pm2krj4eoood0satld5 (AT) 4ax (DOT) com:

I looked over this code and don't see why the second insert
should not be accepted. What violation did you expect to
happen there? Are you expecting some error to be raised
because there is no record in table "two" with a primary
key matching the foreign key in table "one" (the "55" value)?

If so, that is not how foreign keys work. A foreign key
simply establishes a persistent relationship between the
two tables. There is no inherant requirement within a
foreign key definition that there be a relatable record
in the other table. That is what referential integrity code
is for. VFP has an RI engine and an interface where you
can establish the rules to apply to the relationship when
records are inserted, updated, or deleted. If you add a
RI rule to the relationship to restrict inserts in the
child table if no matching record in the parent table exists,
you will get the behavior I think you're after.

If you have a problem with the RI code being stored in
the DBC in stored procedures, I don't know what to tell you.
That's the only place VFP can put code that is guaranteed
to be available whenever the DBC is open. I have applications
that have done millions and millions of data RI-envoking
operations over many years and the RI code has proved as
solid as the best code I have written. No, it ain't pretty
code, but it works - day-in and day-out.

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

trw7at (AT) ixdot (DOT) netcomdotcom (Tim Witort) wrote:

[snip]

Exactly.

Permit me to quote from a textbook: "A foreign key must satisfy
referential integrity, which specifies that the value of an attribute
in one relation depends on the value of the same attribute in another
relation." [1] I could find a number of others from the program that
I just graduated from.

If an FK is not required to inherently satisfy RI, then 1) what
is the point of a foreign key constraint and 2) how is a foreign key
constraint a constraint since it is not constraining anything?

The idea that foreign keys are not enforced automatically by the
DBMS is ludicrous. If this really is how foreign keys work in VFP, we
have been ripped off. It does explain why I have been having trouble
with it though.

Why would anyone bother declaring a foreign key constraint if it
does not do anything anyway?

[1] Hoffer, Jeffrey A.; George, Joey F.; and Valacich, Joseph S.
"Modern Systems Analysis & Design / Third Edition". (C) 2002 Prentice
Hall. 0-13-033990-3

[snip]

Sincerely,

Gene Wirchenko

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

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

Declarative is better than code where it can be used as the DBMS
can handle the details. In another related area, we do not
(generally) program Rushmore; we let VFP deal with it.

[snip]

Sincerely,

Gene Wirchenko

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

Gene Wirchenko seemed to utter in news:ne3pe0pspjmt9gofnimbr0ngh44413gqms@
4ax.com:

That is not a very clear definition of a foreign key, of course
who knows what context that sentence was in. Anyway, a foreign
key does not, in and of itself, *enforce* referential integrity.
Who knows what this author meant by "satisfying" RI? That is
just a bad, vague choice of words. But again, maybe they weren't
trying to define a foreign key in the passage you quoted.

A foreign key is a logical database element. Depending on
*how the foreign key is declared* determines how it functions
in the database. This is true in all RDBMSs that I've worked
with from Oracle to Sybase to VFP. Since you just graduated
from a program, I'm sure are familiar with database design and
the difference between "logical" and "physical" database design
elements. Now, in VFP, establishing RI is not done in the
CREATE TABLE statement; it happens to be done using the RI
builder. Other databases *do* allow RI to be setup in the
DDL alongside the foreign key definition. But I don't recall
ever seeing an RDBMS which *required* you to establish an
RI rule or restriction when declaring a foreign key.

You are mixing two terms here. A foreign key is not the same
thing as a foreign key constraint. You can have a foreign key
without a FK constraint. But not vice versa.

VFP does do this. You just declare the constraint in the RI
builder - not in the DDL. From then on, the DBMS takes care
of enforcing it. Nobody has been ripped off. You just need
to learn how VFP implements this.

Again, you are mixing two concepts and terms here. You declare
a FK constraint in VFP in the RI builder and believe me, these
do work. You seem to be implying that, if you have a child table
related by a foreign key to a parent table, then there *must* be
some restriction on how data is inserted, changed, or deleted
in these tables. Well, there isn't. After you've designed and
implemented a few dozen real world databases, you might discover
that things don't fall nicely into your textbook definitions.
This is why RDBMSs provide the flexibility to define foreign
keys with and without constraint rules. Sometimes you *do* want
the deletion of the parent record to leave orphaned child
records. Sometimes you *do* want to insert child records
that are not related to an existing parent record.

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________

trw7at (AT) ixdot (DOT) netcomdotcom (Tim Witort) wrote:

They were.

Declaring the foreign key is the restriction.

Imagine the fun of coming from one of them and finding that VFP
does not respect the clause. Where is it in the documentation for
create table?

No, I am not mixing it. Implied was that the FK is in the table
with a foreign key constraint.

So what is the use of the foreign key constraint if it does not
actually do anything. That is the ripoff. Declare a foreign key
constraint in Oracle with create/alter table, for example, and you
have a foreign key constraint with nothing else required because that
is a constraint.

Yes, and it is called a foreign key constraint.

How difficult is it for a DBMS to implement FK contraints? Why
did Microsoft not do so with VFP? This is plain shoddy.

Bye bye, RI.

Then, they are not child records, are they?

Sincerely,

Gene Wirchenko

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

Gene, you plainly do not want to, or cannot grasp the concepts
in play here. I refuse to waste my time trying to explain
something this basic to someone who refuses to learn. You
asked a question and received very clear answers from
Anders and myself. Your gripe seems more precious to you
than learning how to use VFP. This is not the first time
I've seen you latch onto some issue and refuse to accept
the explanations or help from many experienced database
developers here. I love database design and love to give
help to and receive help from like-minded folks. You have
shown me that you have no intention to be part of this
group. Sad to say, you've joined a precious few posters
in my killfile. Good luck to you.

-- TRW
_______________________________________
My e-mail: t r w 7
@ i x . n e t c o m . c o m
_______________________________________