Creating and Editing Keys

Go Up to Developing the Logical Model

The purpose of keys in a database is to enforce unique values in an entity and provide a means of sorting the tables to access entity data faster.

In a relational database, there are four types of keys:

• Primary Key: A primary key or unique key is a candidate key to uniquely identify each row in a table. A primary key comprises a single column or set of columns where no two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table can have many unique keys but at most one primary key. A unique key must uniquely identify all possible rows that exist in a table and not only the currently existing rows, such as social security numbers. A primary key is a special case of a unique key in that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. The values in a unique key column may or may not be NULL. Unique keys as well as primary keys can be referenced by foreign keys.
• Alternate Key: Alternate keys are the equivalent to unique index or unique constraints in the physical model. You can enforce alternate keys with unique indexes or unique constraints, depending on the database platform
• Inversion Entry: An attribute or set of attributes that does not uniquely identify every instance of an entity, but which are frequently used for access. The database implementation of an inversion entry is a non unique index.
• Foreign Key: A primary key or non-key attribute that is inherited from another entity.

ER/Studio Data Architect can reverse-engineer key definitions that are not primary key or unique constraints, from a database. You can create primary, inversion, or alternate keys using the Key Editor, which is accessible from the Entity Editor.

For more information, see the following:

• Create a Primary Key
• Create an Alternate Key or Inversion Entry
• Create a Foreign Key/Relationship

Create a Primary Key

1. In the Data Model Explorer, expand the Logical Main Model, expand the Entities node, and then double-click the entity to which you want to add keys.
2. To change an existing attribute to a primary key, in the Entity Editor, click an attribute you want to make a key and then click Edit.

To create a primary key based on a new attribute, the Entity Editor, click Add and define the entity properties as required.

1. On the Attributes tab, click Add to Primary and then click OK and exit the Entity Editor.

Create an Alternate Key or Inversion Entry

1. In the Data Model Explorer, expand the Logical Main Model, right-click the Entities node, and then double-click the entity to which you want to add keys.
2. To change the properties of an existing attribute, In the Entity Editor, click an attribute you want to make a key and then click Edit.

To create an alternate key or inversion entry based on a new attribute, in the Entity Editor, click Add and define the entity properties as required.

1. On the Keys tab, click Add.
2. In the Key Editor, type a name for the key and select the type of key you want to create.
3. From the list of Available Keys, which are all the attributes of the entity, double-click the attributes you want to add to the key.
4. You can reorder the attributes by selecting an attribute and then pressing Up or Down until the attribute is in the desired position within the list of Selected Keys.
5. Click OK to accept the attribute selections and then click OK again to affect the changes to the entity.

Attachment Bindings tab: Bind an external piece of information, or attachment to the key. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.

Note:

• You can prevent a key from being implemented when generating a physical model or SQL, by selecting Logical Only on the Keys tab.
• To modify the properties of a foreign key attribute, you must alter the corresponding primary key column in the originating parent table.
• To preserve domain consistency, ER/Studio Data Architect only lets you change the column name of foreign keys. To modify other properties of a foreign key column, you must alter the corresponding primary key column in the originating parent table.

Create a Foreign Key/Relationship

Foreign keys are created when you establish relationships between entities. The primary key of the parent object becomes the foreign key of the child object. Foreign keys are not displayed in the entity boxes in the Data Model Window when using Filtered IE (No FKs) but are displayed using all other notation types. For more information, see Working with Relationships.

Specify FK Attribute Name and Datatype

By default, the foreign keys inherit their names from the primary key of the parent object; however, you can choose to provide another name for the foreign key using the Logical Rolename.

1. Double-click the entity you want to change. ===
2. Click the foreign key in the attributes list and then click Edit.
3. Enter a Logical Rolename and then select Synchronize Column Rolename with Logical Rolename.
4. Click Save.

The child now displays the logical rolename of the foreign key.

Similarly, you can specify a different datatype for the foreign key.

1. Double-click the entity and from the Entity Editor, select the foreign key from the attributes list and then click Edit.
2. Select Edit Foreign Key Datatype and then make the necessary changes to the datatype.
3. Click Save.

See Also

• Developing the Physical Model
• Common Tasks
• Creating and Editing Entities and Tables