Altova DatabaseSpy 2024 Professional Edition

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


1.You can add a primary key to a table only if the table does not already have one.

2.The column on which the primary key is defined must not be nullable (that is, the Nullable property must be turned off in the Properties window).


While a new primary 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.


Note the following:


In MySQL and MariaDB databases, the name of the primary key constraint is always PRIMARY.

In SQLite, the primary key displayed in DatabaseSpy appears with the name <generated> and you can rename it. However, after committing the changes, the primary key name will still be displayed with an arbitrary name, due to database limitations.


Creating primary keys from the Online Browser

To add a primary key on a single column:


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

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


If you need to create a primary key on multiple columns, see Composite Primary Keys.


Creating primary keys from the Design Editor

By default, when you create a new table in DatabaseSpy's Design Editor, a primary key is automatically defined on the first column. This option can, however, be disabled in the Design Editor Options, and in this case you can define a primary key manually.


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


Right-a click a column in a table design and select Make Primary key from the context menu.

Drag a column from the "Column" section and drop it onto the "Key" section of a table design. To create a primary key on multiple columns, first select multiple columns while holding the Shift key pressed, and then drag them into the Keys section. This menu is also displayed if you click the Create new Key Constraint ic_add-column icon in the Key section of the table design.

Right-click anywhere into the table design and select Insert new | Key | Primary Key from the context menu. Alternatively, expand the Keys section, click the Create new Key Constraint ic_add-column icon at the bottom of the Key column and select Create Primary Key from the menu that pops up.


The primary key is added to the "Key" section of the table design.


To add the primary key on multiple columns, click the Add Column to Key ic_add-column button (at the bottom of the "Columns" column) and choose a column from the drop-down list that opens. To remove a column from a primary key, right-click the column (in the "Columns" column), and then select Delete selected objects 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 primary key in the database.


Creating primary keys by generating SQL

1.In the Online Browser, right-click the primary key that serves as template for the primary 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