The following screen shows the Execution Report page for the sample standardization:
Display 4.7 Execution Report
Note that you can click Report: Metadata Update Details to display the report. This
report contains a list of the columns involved in the actual standardization process. The
Non-Standard Columns Report and the Report: Metadata Update Details are located in
the following location: <User location>\CST\<Folder with timestamp>.
You can also review a log of the standardization process. Finally, click Finish to close
the Column Standardization Tool wizard.
Maintaining Keys
Problem
You want to view, add, or update keys for a table.
104 Chapter 4 Working with Tables
Solution
You can use the Keys tab in the properties window for a table to maintain keys. See
“Understanding Keys in SAS Data Integration Studio” on page 105. Then perform the
following tasks as needed:
“View Keys” on page 105
“Add a Primary Key or a Unique Key” on page 107
“Add a Foreign Key” on page 108
“Update the Columns in a Key” on page 109
“Delete or Rename a Key” on page 109
Tasks
Understanding Keys in SAS Data Integration Studio
SAS Data Integration Studio enables you to manage the following types of keys:
primary key: a column or combination of columns that uniquely identifies a row in a
table. A table can have only one primary key.
unique key: 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.
foreign key: a column or combination of columns in one table that references a
corresponding key in another table. A foreign key must have the same data type as
the key that it references.
Primary keys and unique keys are often used in table joins. A foreign key is used to
create and enforce a link between the data in two tables. A link is created between two
tables such that the column or columns that hold a primary key value or a unique key
value in one table are referenced by a column or columns in a second table. The column
or set of columns in the second table is a foreign key.
Note: Some databases, such as Oracle and DB2, support foreign key references to
columns in the same table.
View Keys
To display information about keys that have been specified for a table, access the Keys
tab on the properties window for the table. On the Keys tab, the Keys pane on the left
lists all of the keys that are associated with the current table. Click a key in the list to see
information about it in the panes on the right: the Details pane and the Associated
Foreign Key Tables pane. The following display shows the Keys tab for a table named
AUTHOR. A primary key named AUTHOR.Primary is selected on the left. Information
about this key is shown on the right.
Maintaining Keys 105
Display 4.8 Keys Tab with a Primary Key
The default name for a primary key is currentTableName.Primary, where
currentTableName is the name of the current table, and Primary is a literal string.
For example, the default name for the primary key in the AUTHORS table is
AUTHOR.Primary.
The default name for a unique key is currentTableName.UniqueKeyN, where
currentTableName is the name of the current table, UniqueKey is a literal string, and
N is an iteration number added to the end.
When a primary key or a unique key is selected in the Keys pane, then the columns that
are specified for that key are displayed in the Details pane. In the preceding display, the
primary key consists of the personid column in the AUTHOR table.
The Associated Foreign Key Tables pane displays any foreign keys that are associated
with a primary key or unique key that is selected in the Keys pane. The name of the
foreign key and the name of the table that contains the foreign key are displayed. In the
preceding display, the primary key AUTHOR.Primary is referenced by a foreign key in
the BOOKS table.
The following display shows the Keys tab for the BOOKS table, the table that contains
the foreign key that was referenced. The BOOKS table has two keys: a primary key
named BOOKS.Primary and a foreign key named AUTHOR.BOOKS, which is selected
on the left. Information about the foreign key is shown on the right.
Display 4.9 Keys Tab with a Foreign Key Selected
106 Chapter 4 Working with Tables

Get SAS Data Integration Studio 4.9 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.