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”.

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 );

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.

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.

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.

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.

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" )

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 )

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'

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

UPDATE Phone SET PersonPtr = 0 WHERE PersonPtr = 333