Maintaining Keys
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.
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.
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:
-
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.
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:
-
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.
-
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.
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.
Copyright © SAS Institute Inc. All rights reserved.