Defining and Modifying Unique Keys

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Exploring and Changing the Database Structure > Designing Tables in Design Editor >

Defining and Modifying Unique Keys

This topic shows you how to define a unique key in the Design Editor. You can also define a unique key by entering and executing the appropriate SQL statement in an SQL Editor window.

 

When defining a unique key for a table, please consider that if the unique key constraint is applied to an existing column, this column must not contain duplicate values. The column may be nullable, however the NULL value can occur only once.

 

There are several ways to define a unique key in the Design Editor; the procedures are similar to those described for primary keys:

 

Select the Make Unique key command from the context menu of a column in a table design
Drag a column from the Columns section and drop it onto the Keys section of a table design
Click the Create new Key Constraint ic_add-column icon in the Keys section of a table design
Right-click anywhere into the table design and select Insert new | Key | Unique Key from the context menu

 

Regardless of the approach chosen above, the key is added to the Key section of the table design. By default, the name of the new unique key is <generated>, which means its name will be automatically generated by the database. This ensures uniqueness of the name in the database. If necessary, you can override the generated name. To do this, enter a descriptive name in place of <generated>, and press Enter.

 

If you override the generated name with a custom name, the new name must be unique; otherwise, errors will occur when the database change script is executed.

 

As always when making changes to the database structure, a change script is generated which must be executed in order to actually create the unique key in the database. DatabaseSpy performs a check as to whether the content in the constrained column is unique. If the check fails, a dialog box opens displaying a "duplicate key was found" error. The exact contents of the error message depends on the database type.

dbs_create_uk_error

Columns that have a unique key assigned are marked with the unique key ic_unique-key-column icon in the Design Editor and the Online Browser.

 

Viewing unique key constraints

After you have executed the change script, the unique key ic_unique-key-column icon is displayed to the left of the column name in the Columns folder of the Online Browser and when you show the table in the Design Editor. In the Online Browser, the name of the unique key constraint will also show up in the Keys sub-folder of the respective table and will be marked with the unique key ic_unique-key-constraint icon.

ob_UK-constraint


design_unique-key

In the Online Browser, the column definition can be viewed in detail by expanding the unique key entry in the Keys folder.


In the Design Editor, the column the unique key is assigned to is displayed in bold when you select the unique key constraint in the Keys section.

 

Modifying a unique key constraint

You can rename, delete or change the definition of a unique key constraint. Note that the unique key constraint is always deleted first and then recreated with the new definition. The respective SQL statement is generated automatically in the Database Structure Change Script window if you modify the unique key definition in the Design Editor. A unique key cannot be deleted if it is referenced by a foreign key in another table.


© 2019 Altova GmbH