Table of Contents
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”.
FOREIGN KEY Creation
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;
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.
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'
To unlink a child record you need set value of PTR Field to be NULL.
UPDATE Phone SET PersonPtr = 0 WHERE PersonPtr = 333