Altova DatabaseSpy 2024 Professional Edition

As with other database object types, you can create unique keys using several approaches. Before adding a unique key to a column, note the following:


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.


While a new unique key is created but not committed to the database yet, its name may appear with the label <generated> in Online Browser and Design Editor. This means that the name will be automatically generated by the database when you commit the change script. For details about support for generated names across various databases, see Generated Names.


Adding a unique key from the Online Browser

1.Right-click a column and select Create Unique Key from the context menu.

2.Click the Execute Change Script dbs_ic_execute_script button in the Database Structure Change Script window.


Adding a unique key from the Design Editor

There are several ways to define a unique key in the Design Editor:


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


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.


Creating unique keys by generating SQL

1.In the Online Browser, right-click the unique key that serves as template for the unique key that is to be created.

2.Select the menu command Show in new SQL Editor | Add from the context menu.

3.In the SQL Editor, change the generated statement as required.

4.Click the Execute dbs_ic_execute_query button or press F5.

© 2018-2024 Altova GmbH