foreign key constraint

[Ivan Smahin]

Hello Bernard,

Monday, January 25, 2010, 1:48:55 PM, you wrote:

> ALTER TABLE Phone(
>     ADD CONSTRAINT 'link_Person_Phone'
>         FOREIGN KEY (PersonPtr) REFERENCES Person(ID)
>         ON DELETE SET NULL
>         ON UPDATE SET NULL )

> The above is from the Valentina wiki.  Is that right?  When I try to
> use the SQL Editor in VStudio, it complains about unexpected tokens.

CREATE TABLE t1 ( id long unique );
CREATE TABLE t2 ( ptr long indexed );

ALTER  TABLE  t2  ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (t2.ptr) REFERENCES
t1(id)  ON DELETE SET NULL ON UPDATE SET NULL;

Really, get a syntax error...

but it works for ...FOREIGN KEY (ptr)...
Ok - will be fixed.



> Can ID in the refrenced table be a primary key of that table?  Can ID
> in the referenced table actually be the RecID of that record in the
> table?

No  -  it is impossible but actually this kind of relation is ObjectPtr
(in  terms of Valentina). So you'd better use ObjectPtr instead of RDB
relation based on RecID as primary key. It will be times faster.

CREATE TABLE t1 ( f1 long  );
CREATE TABLE t2 ( ObjectPtr CONSTRAINT l1 REFERENCES t1 ON DELETE SET NULL );


> I started using binary links but I've given up on those.  They seem to
> cause to many problems.

Many  people  use  them  without  any  problem  - can you provide more
details?


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com