Follow

Add Foreign Key

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 Subscribers 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:

  1. Create the Join table.

    api_web?r=myrealm&p=mypass&c=rdbaddtable&table=joinTableExample&table_type=join

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

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

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

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk