By default, when creating 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, you will then have to define a primary key manually. The instructions below show how to add a primary key in Design Editor. In addition, you can always enter and execute the appropriate SQL statement in an SQL Editor window.
1.If a table already has a primary key, no primary key may be defined when you show a table in a Design Editor window.
2.The column on which the primary key is defined must not be nullable (that is, the Nullable check box next to it must be cleared in the table design). If you attempt to create a primary key on a nullable column, a warning dialog box is displayed.
There are several ways to define a primary key in the Design Editor:
•Select the Make Primary 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 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 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 icon at the bottom of the Key column and select Create Primary Key from the menu that pops up.
Regardless of the approach chosen above, the primary key is added to the Key section of the table design.
By default, the name of the new primary 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.
|Note:||In MySQL and MariaDB databases, the name of the primary key constraint is always PRIMARY. In SQLite, custom key names are ignored by the database; therefore, the primary key displayed in the Online Browser of DatabaseSpy appears with the name <generated>.|
To add the primary key on multiple columns, click the Add Column to Key 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.
Columns that have a primary key assigned are marked with the primary key icon in the Design Editor and the Online Browser.