Defining a Foreign Key for a View or Table Resource
In a relational table, a foreign key is a column that matches the primary key column in another table. Suppose that column X in Table A relates to column Y in Table B and column Y is a primary key in Table B, the foreign key appears in Table A.
Foreign key relationships indicate that if you join A.X = B.Y, there is exactly one row in Table B found for each row in Table A. This information is a useful hint for join operations.
In Studio, the Foreign Keys panel in the view and table editor lets you create a definition that acknowledges and allows use of any foreign keys in the data source. For each foreign key you want to define, first you need to select the column that would be the foreign key, and subsequently identify the corresponding primary key column in the parent table.
The processes for defining a foreign key and testing the creation of a foreign key are as follows:
To define a foreign key
1. Open a view or table.
The view used here has two tables—orderdetails and orders—from the data source ds_orders (/shared/examples/ds_orders).
The projected columns are orderdetails.ProductID, orderdetails.UnitPrice, and orders.ShippingMethodID. See the Grid panel and Columns panels in the view editor.
2. Click the Foreign Keys tab in the editor to open the Foreign Keys panel.
3. Click the Add button (in the bottom left section of the editor).
4. In the input window that opens, type a name (for example, FK_ProductID) for the foreign key that you want to define, and click OK.
The columns that are available for you to mark up as a “foreign key column” are listed in the section Available Columns. These columns are exactly the same columns selected for projection in the view or table. See the Columns panel of the editor.
5. Select a column that you want to identify as a foreign key.
In this example, ProductID is selected.
Specify the parent table that contains the primary for this foreign key column. The parent table is also known as the referenced table.
6. Click the Browse button, and locate the parent table in which ProductID is the primary key.
In this example, the parent table is:
/shared/examples/ds_inventory/products
7. Use the forward arrow button to move ProductID from Available Columns to the Foreign Column section.
The columns in the Parent Table are displayed in a drop-down list in the Primary Column section, and the primary key column is visible.
8. Select the primary key column (ProductID) in the drop-down list (in the Primary Column section) and save the resource. Now, the view has a column (ProductID) identified as a foreign key.
Repeating this process, you can define as many foreign keys as you need.
To test the creation of the foreign key
1. Publish the resource. For details on publishing, see
Publishing Resources. You can also verify that the foreign keys you defined are accessible to external client applications.
2. Open the system table /services/databases/system/ALL_FOREIGN_KEYS, and view its contents.
3. Right-click on:
Data Services/Databases/system/ALL_FOREIGN_KEYS
4. Select Show Contents.
Or, open Data Services/Databases/system/ALL_FOREIGN_KEYS, and click Show Contents. The foreign keys you defined are listed in the Result panel.