The Relational Add Foreign Key command allows you to assign a Foreign Key to a table that does not yet have a Foreign Key defined. The Relational Add Foreign Key command requires the following arguments: Command, Realm, Password, Table Name, Foreign Key Table Name.
api_web?r=myrealm&p=mypass&c=rdbaddforeignkey&table=mytable&fk_table=foreignkeytable
The command is 'rdbaddforeignkey', the realm is 'myrealm' and the password is 'mypass' and the table to update is 'mytable' by assigning the table 'foreignkeytable' as the foreign key. This command results in updating the table properties to set the foreign key.
Prior to version 11, the new table was created with a default Foreign Key set to the Contacts table. In v11 and newer, you must set the Foreign Key after the table is created. A table cannot be activated until a Foreign Key has been defined.
Foreign Keys to Join Tables
Join tables have a unique requirement when assigning Foreign Keys, as you also must define the Flat tables to join. Use the Add Foreign Key command to setup the joined tables as well as the main foreign key table. The joined tables must be added before the main foreign table. The steps to create the keys to a Join table are:
- Create the Join table.
api_web?r=myrealm&p=mypass&c=rdbaddtable&table=joinTableExample&table_type=join
- Create the Unique ID field for the Join table.
api_web?r=myrealm&p=mypass&c=rdbaddfield&table=joinTableExample&fieldname=joinUnique&fieldtype=number&unique=1
- Add at least one Flat table as a Joined Table:
api_web?r=myrealm&p=mypass&c=rdbaddforeignkey&table=joinTableExample&fk_table=flatTableExample&table_type=join
- Finally, add the main Foreign Key:
api_web?r=myrealm&p=mypass&c=rdbaddforeignkey&table=joinTableExample&fk_table=regularTableExample&table_type=join
*Join table support added in v13.0
Example
Using all commands and arguments (please note the command is wrapped for display purposes only):
api_web?r=myrealm&p=mypass&c=rdbaddforeignkey&table=purchase_history&fk_table=subscribers&table_type=regular
Successful results return:
SUCCESS: Foreign Key to subscribers created for purchase_history
Results
The Add Relational Foreign Key command outputs either a SUCCESS or FAILURE message.
Success
The Add Relational Foreign Key command success message returns the SUCCESS message and includes the name of the table and the foreign key relationship. The result format is:
SUCCESS: Foreign Key to [fk_table_name] created for [table_name]
Failure
The Add Relational Foreign Key command will fail if a table doesn't exist, already has a Foreign Key assigned, or invalid data is defined. The result format is:
FAILURE: Cannot add the FK to the table because the table already has a FK = [fk_table]
FAILURE: FK Table name is invalid or is not an Active table: [table_name]
Arguments
The Relational Add Foreign Key command supports the following arguments:
Name |
Req? |
Description |
Argument |
Command |
Yes |
Defines the command to use. |
cmd=rdbaddforeignkey c=rdbaddforeignkey |
Realm |
Yes |
Name of the realm to run the command. This is required, and the command will fail if the realm and password do not match. |
realm=[realm_name] r=[realm_name] |
Password |
Yes |
Authentication code for API access. This is required, and the command will fail if the realm and password do not match. |
pwd=[password] p=[password] |
Relational Table Name |
Yes |
Name of the relational data table to update. This is required. |
table=[table_name] |
Foreign Key Table Name |
Yes |
Name of the table to associate as the Foreign Key. This is required. |
fk_table=[fk_table_name] |
Table Type |
|
Type of the data table being associated to (Regular or Join). Flat tables do not support Foreign Keys. |
table_type=[regular, join] DEFAULT==regular |
Released in v11.1.0