Specifying Metadata for DBMS Tables with Keys

< Day Day Up >

Specifying Metadata for DBMS Tables with Keys

Tables in a database management system often have primary keys, unique keys, and foreign keys.

A primary key is one or more columns that are used to uniquely identify a row in a table. A table can have only one primary key. The column(s) in a primary key cannot contain null values.

A unique key is also one or more columns that can be used to uniquely identify a row in a table. A table can have one or more unique keys. Unlike a primary key, a unique key can contain null values.

A foreign key is one or more columns that are associated with a primary key or unique key in another table. A table might have one or more foreign keys. A foreign key is dependent upon its associated primary or unique key. In other words, a foreign key cannot exist without that primary or unique key.

Note

When specifying metadata for a DBMS table with foreign keys, if you want to preserve the foreign key, you must specify metadata for all of the tables that are referenced by the foreign keys.

For example, suppose that Table 1 had foreign keys that referenced primary keys in Table 2 and Table 3. To preserve the foreign keys in Table 1, you could use the Metadata Importer wizard or a source designer wizard to import metadata for Tables 1, 2, and 3.

< Day Day Up >

Viewing the Data in a Table

After the metadata for a table has been entered, you might want to verify that the corresponding physical table contains the data that you were expecting. Perform the following steps to view the data that corresponds to the metadata for a table:

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Tables folder.
Select the table, then select View View Data from the menu bar. The View Data window displays the column headings, row numbers , and the rows of data in the table. If the column headings are ordered and named as expected, then the metadata for the table is correct.

< Day Day Up >

Viewing the Metadata for a Table

Perform the following steps to view the metadata for a table:

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Tables folder.
Select the metadata for the table, then select File Properties from the menu bar. The properties window for the table is displayed.
Use the tabs in this window to view metadata for the table. Each tab has its own Help button.

< Day Day Up >

Updating the Metadata for a Table

Perform the following steps to update the metadata for a table that is under change management.

On the SAS ETL Studio desktop, select the Inventory tree.
In the Inventory tree, open the Tables folder or the External Tables folder.
Select the table, then select Project Check Out . The metadata for the table is checked out. A check mark is displayed next to the table in the Inventory tree. An icon indicating a checked-out table appears in the Project tree.
Display the Project tree, select the table, and select File Properties from the menu bar. The properties window for the table is displayed.

Note that you must display the table from the Project tree in order to update metadata. Displaying the table from the Inventory tree enables browsing only.
Use the tabs in this window to make changes to the metadata for the table. Each tab has its own Help button. Column metadata is a special case. For details, see "Updating Column and Mapping Metadata" on page 68.
When you are finished updating the metadata, you can check in your changes. In the Project tree, select the repository icon.
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository .

Impact of Updating a Table's Metadata

Keep in mind that a table can be used in multiple jobs. A table can also be used in multiple places in the same job. Accordingly, when you update the metadata for a table, make sure that the updates are appropriate in all contexts where the metadata is used. For example, if you update the columns for Table 1 in one job, the updates would also have to be appropriate for Table 1 in the context of another job.

Updating Column and Mapping Metadata

If the metadata for a source has not yet been added to a job, you can update its column metadata as previously described. If the metadata for a source has been added to a job, the job might have one or more targets and transformations that depend on the current column metadata for the source. In that case, use the steps that are described in "Updating Column and Mapping Metadata" on page 119.

< Day Day Up >

Flylib.com

Category list

Similar pages

Specifying Metadata for DBMS Tables with Keys