To set or unset keys manually, right-click the column in the table and select Set as Key and select Primary, Foreign, or None

4. Save the data foundation by clicking the Save icon in the main tool bar.

Note:

You cannot set keys for alias tables. Alias tables inherit keys from the original table.

The next time you use the Detect Keys command, the keys defined in the database tables override the keys set manually for a table. You can set an application preference so that if no keys are detected, the keys that you set manually in a data foundation table are kept.

Related Topics

•About table keys

•Setting preferences for the Data Foundation Editor

9.7 About table row counts

Detect row counts

The number of rows in database tables can be detected and stored in the data foundation. Row counts are used to detect cardinalities in the absence of table keys.

When you detect rows counts, the number of rows for the selected tables is counted and stored.

Note:

Column filters are not applied when detecting row counts.

You can also set estimated row counts for tables. This can be useful if you are working with a reduced sample of data, but want queries to be optimized for the size of the production data. The row count that you set is replaced by the detected row count when you do a detect row count for that table.

The Detect Row Count command on the Detect menu lists the current row counts for all tables in the data foundation. From this list, you can set row counts and detect row counts for a selection of tables.

To detect row count for one table, right-click the table header in the data foundation view and select Detect > Row Count. The row count for the selected table is updated. To select multiple tables, click the table headers while holding down the CTRL key.

Count rows

Use the Count Rows command on multiple tables linked by joins to see the number of rows returned by the resulting query. Column filters are applied.

To count rows returned in a query, select tables in the data foundation view in one of the following ways:

• Right-click a table and select Select Related Tables.

• Click the table headers while holding down the CTRL key.

Then right-click a table in the selection and select Count Rows.

9.8 About joins

A join is a condition that links tables in the data foundation. A join restricts the data returned when the two tables are queried.

Tables that are joined usually have a parent-child relationship. If tables are not joined, then a query run on the two tables can return a result set that contains all possible row combinations. Such a result set is known as a Cartesian product and is rarely useful.

Joins are defined by linking a column in one table to a column in a second table. You can insert joins into the data foundation, or detect joins automatically.

The following sections describe the types of joins you can create.

Equi-joins

An equi-join is the join type created by default between two tables. An equi-join links tables based on the equality between the values in the column of one table and the values in the column of a second table. In a normalized database, the columns used in an equi-join are often the primary key from one table and the foreign key in the other.

Self-restricting joins

A self-restricting join is when the two tables are the same. Self-restricting joins are used to define column filters. For more information on column filters, see the related topic.

Theta joins

When there is no obvious direct column to column relationship between two tables, you can use a theta join. A theta join links tables based on a relationship other than equality between two columns. It is used to link a value to a range of values. For example, an order date in one table is joined to a date between start date and end date in a second table.

Outer joins

An outer join can be used to link tables when one table contains rows that do not have a match in the common column of the other table. Unlike an equi-join, an outer join returns all rows, regardless of whether or not there is a matching value in the joined table.

A left outer join returns all rows in the first (or left-hand side) table, even if they have no match in the second table.

A right outer join returns all rows in the second (or right-hand side) table, even if they have no match in the first table.

A full outer join returns all rows from both tables, with null values when there is no match.

Shortcut joins

A shortcut join is a join that provides an alternative path between two tables. Shortcut joins improve the performance of a query by not taking into account intermediate tables, and so shortening a normally longer join path.

Shortcut joins are not taken into account to define contexts, but only to decrease the number of joins whenever possible.

Related Topics

•Inserting and editing a join

•Detecting joins

•Inserting a column filter

•About contexts

9.8.1 Inserting and editing a join

1. Open the data foundation in the editor by double-clicking the data foundation name in the Local Projects View.

2. Do one of the following:

Command Option

Right-click the join line in the data foundation view and select Edit Join.

To edit an existing join

Select the Insert Join command from the Insert menu in the data foundation view.

To insert and edit a join

3. To define the first side of the join, select the table from the list in Table 1, and then select the column