Switch to: V10V9V8V7V6V5

Foreign Key Link

RDBMS consider FOREIGN KEY as a constraint of Table. Valentina Database considers the FK not only as constraint, but also as kind of abstraction Link.

We will name this kind of Link also as “Link on Value”, because records are linked with the help of duplicated Key values. Also we name it as “RDB-Link”.

foreingkey1.jpg

FOREIGN KEY Creation

SQL Example:

CREATE TABLE Person(
    ID ULONG PRIMARY KEY,
    Name String(40) 
);
 
CREATE TABLE Phone(
    Number String(12), 
    PersonPtr ULONG 
        INDEXED 
        CONSTRAINT link_Person_Phone
        REFERENCES Person(ID) 
        ON DELETE SET NULL 
        ON UPDATE SET NULL );

SQL Example for existed Tables:

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

API EXAMPLE:

VLink lnkFK = db.CreateForeignKey( "link_Person_Phone", 
    fldPersonID, fldPersonPtr,
    EVOnDelete.kSetNull, EVOnUpdate. kSetNull );

ON DELETE Action

When you create a FOREIGN KEY you can specify the Action ON DELETE, which defines what should happens with a child record, when you delete its parent record.

ON UPDATE Action

When you create a FOREIGN KEY you can specify the Action ON UPDATE, which defines what should happens with value of FK(PTR) field of a child record when you UPDATE PK field of its parent record.

Link Name

Valentina consider the name of FOREIGN KEY constraint as the Link name. If you have not specify the Link name then Valentina will assign some name automatically, which you can change later.

FOREIGN KEY is able establish 1 : M links. If you mark PTR field to be unique, then you get 1 : 1 Link.

Unique: 1:1 Link

You can specify that FOREING KEY Field is UNIQUE. In this case you get semantic of 1 : 1 Link. Really, you cannot insert into this Field the same ID value of a parent record.

FOREIGN KEY Deletion

To delete FOREIGN KEY Link you need:

in SQL Style do:

DROP CONSTRAINT link_Person_Phone

in API style do:

db.DropLink( "link_Person_Phone" )

Work With Records

Linking New Record

To link a child record to some parent record you need just assign PK value of parent record into FK(PTR) field of child record.

INSERT INTO Phone(Number, PersonPtr) VALUES( '11111', 547 )

Linking Existing Record

To link existing child record to existing parent record you need assign PK value of parent record into FK(PTR) Field:

UPDATE Phone SET PersonPtr = 333 WHERE Number = '22222'

Unlinking Records

To unlink a child record you need set value of PTR Field to be NULL.

UPDATE Phone SET PersonPtr = 0 WHERE PersonPtr = 333