Maintaining Keys

Problem

You want to view, add, or update keys for a table.

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. Then perform the following tasks as needed:

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.

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.

Keys Tab with a Foreign Key Selected

The default name for a foreign key is foreignTableName.currentTableName, where foreignTableName is the name of the table where the foreign columns were originally created, and currentTableName is the name of the current table. In the preceding display, the foreign key is named AUTHOR.BOOKS, because the foreign columns originate in the AUTHOR table, and the current table is the BOOKS table.

When a foreign key is selected in the Keys pane, the following values are displayed in the Details pane:

Foreign Key Column displays the column or combination of columns in the current table that references the corresponding column or combination of columns in another table. In the preceding display, the foreign key column is named author, which is the name of a column in the BOOKS table.
Length displays the length of the Foreign Key Column.
Unique Key Column displays the corresponding column or combination of columns in the other table. In the previous display, the unique key column is named personid.
Unique Key Table displays the name of the other table: AUTHOR.

Add a Primary Key or a Unique Key

In general, to create a primary key or a unique key, you select one or more columns in a table and specify them as a key. Typically, the creation of keys is carefully planned, so you know which table and columns to select. Perform the following steps to add a primary key or a unique key:

Access the Keys tab on the properties window for the desired table. For example, you want to create a primary key for the AUTHORS table.
Select New from the toolbar, and select Primary Key or Unique Key. Alternatively, right-click Primary Key or Unique Key in the Keys pane, and select New. A column selector window displays.
Select one or more columns in the current table that are appropriate for the key that you want to create. For example, the AUTHOR table has a column named personid, which uniquely identifies each author in the table. This is a good column to use as the primary key. The following display shows the selection of the personid column in the AUTHOR table.

Selecting a Column for a Primary Key
Click OK to save the selected columns in the metadata for a key. The new key is displayed in the Details pane.
Click OK to save the key.

Add a Foreign Key

To create a foreign key, which is a key in one table that references a corresponding key in another table, first select the other table that has the corresponding key. Then combine key columns in the current table with the corresponding key columns from the other table, and specify this combination as a foreign key. Typically, the creation of a foreign key is carefully planned, so you know which tables and columns to select. Perform the following steps to add a foreign key:

Access the Keys tab on the properties window for the table that requires a foreign key. For example, if you want to create a foreign key in the BOOKS table that references the primary key column in the AUTHORS table, then open the properties window for the BOOKS table.
Right-click Foreign Key in the Keys pane, and select New. A table selector window displays.
Select the other table with the column or columns that you want to reference in the current table. In the current example, select the AUTHORS table. Then, click OK to save your selection. The Select Partner Key window displays. A default partner column in the selected table is displayed in the Partner Key Columns field.

Foreign Key Column Not Yet Selected
If the default partner key column is not appropriate, use the Key selector to select a different key in the other table. Otherwise, accept the default. For example, in the preceding display, the default partner key column is the primary key column in the AUTHORS table: personid. You want to reference this column in the BOOKS table.
Use the selection arrow in the Foreign Key Columns field to select a column whose values should be linked to the partner key column. For example, the BOOKS table has a column named author whose values match the values in the personid column. The following display shows the combination of the personid column and the author column.

Foreign Key Column Selected
Click OK to save the selected columns in the metadata for the foreign key. The new key is displayed in the Details pane.
Click OK to save the key.

Update the Columns in a Key

To add, delete, or change the order of columns in a primary key or unique key, select the key in the Keys pane, and then use the controls in the Details pane, such as the Add button, the up and down arrows, and so on. The only change you can make to a foreign key in the Details pane is to select a different foreign key column.

Delete or Rename a Key

To delete or rename a key, right-click the key in the Keys pane and select Delete or Rename.

Note: You cannot delete a primary key or a unique key that has a foreign key association. Deleting a key that is referenced by a foreign key breaks the table that contains the foreign key. You must delete the foreign key from the other table before you are permitted to delete the primary key or unique key in the current table.