Working with keys

Why you should use keys

You use primary and foreign keys to enforce the referential integrity of your database. That way you can rely on the DBMS to make sure that only valid values are entered for certain columns instead of having to write code to enforce valid values.

For example, suppose you have two tables called Department and Employee. The Department table contains the column Dept_Head_ID, which holds the ID of the department's manager. You want to make sure that only valid employee IDs are entered in this column. The only valid values for Dept_Head_ID in the Department table are values for Emp_ID in the Employee table.

To enforce this kind of relationship, you define a foreign key for Dept_Head_ID that points to the Employee table. With this key in place, the DBMS disallows any value for Dept_Head_ID that does not match an Emp_ID in the Employee table.

For more about primary and foreign keys, consult a book about relational database design.

What you can do in the Database painter

You can work with keys in the following ways:

Look at existing primary and foreign keys
Open all tables that depend on a particular primary key
Open the table containing the primary key used by a particular foreign key
Create, alter, and drop keys

Working with keys in an UltraLite database You cannot create, alter, or drop keys in an UltraLite database.

Viewing keys

Keys can be viewed in several ways:

In the expanded tree view of a table in the Objects view
As icons connected by lines to a table in the Object Layout view

In the following picture, the Department table has two keys:

A primary key (on dept_id)
A foreign key (on dept_head_id)

Figure 16-5: Primary and foreign keys in Object Layout view

The sample shows the Department table in the Object Layout view. It has a list of three columns. The dept _ id column is connected by a line to a P icon to show it is the primary key. The dept _ head _ id column is connected to an F icon to show that it is a foreign key.

If you cannot see the lines If the color of your window background makes it hard to see the lines for the keys and indexes, you can set the colors for each component of the Database painter's graphical table representation, including keys and indexes. For information, see “Modifying database preferences”.

Opening related tables

When working with tables containing keys, you can easily open related tables.

To open the table that a particular foreign key references:

Display the foreign key pop-up menu.
Select Open Referenced Table.

To open all tables referencing a particular primary key:

Display the primary key pop-up menu.
Select Open Dependent Table(s).

PocketBuilder opens and expands all tables in the database containing foreign keys that reference the selected primary key.

Defining primary keys

You can define primary keys for database tables with PocketBuilder. However, you cannot define a primary key for a table that already has one, unless you first drop the existing primary key.

To create a primary key:

Do one of the following:
- Highlight the table for which you want to create a primary key and click the Create Primary Key drop-down toolbar button in PainterBar1
- Select Object>Insert>Primary Key from the Database painter menu or New>Primary Key from the pop-up menu
- Expand the table’s tree view, right-click Primary Key, and select New Primary Key from the pop-up menu
The Primary Key properties display in the Object Details view.
Select one or more columns for the primary key.

Columns that are allowed in a primary key Only a column that does not allow NULLs can be included as a column in a primary key definition. If you choose a column that allows NULLs, you get a DBMS error when you save the table.
Right-click on the Object Details view and select Save Changes from the pop-up menu.

Any changes you made in the view are immediately saved to the table definition.

Defining foreign keys

You can define foreign keys for database tables in PocketBuilder.

To create a foreign key:

Do one of the following:
- Highlight the table and click the Create Foreign Key drop-down toolbar button in PainterBar1
- Select Object>Insert>Foreign Key from the Database painter menu or New>Foreign Key from the pop-up menu
- Expand the table’s tree view and right-click on Foreign Keys and select New Foreign Key from the pop-up menu
The Foreign Key properties display in the Object Details view.
Name the foreign key in the Foreign Key Name box.
Select the columns for the foreign key.
On the Primary Key tab page, select the table and column containing the Primary key referenced by the foreign key you are defining.

Key definitions must match exactly The definition of the foreign key columns must match the primary key columns, including datatype, precision (width), and scale (decimal specification).
On the Rules tab page, specify the rule that you want applied on delete of primary table row.

The default rule applied is “Disallow if Dependent Rows Exist (RESTRICT)”.
Right-click on the Object Details view and select Save Changes from the pop-up menu.

Any changes you make in the view are immediately saved to the table definition.

Modifying keys

You can modify a primary key in PocketBuilder.

To modify a primary key:

Do one of the following:
- Highlight the primary key listed in the table’s expanded tree view and click the Properties button
- Select Properties from the Object or pop-up menu
- Drag the primary key icon and drop it in the Object Details view
Select one or more columns for the primary key.
Right-click on the Object Details view and select Save Changes from the pop-up menu.

Any changes you make in the view are immediately saved to the table definition.

Dropping a key

You can drop keys (remove them from the database) from within PocketBuilder.

To drop a key:

Highlight the key in the expanded tree view for the table in the Objects view or right-click the key icon for the table in the Object Layout view.
Select Drop Primary Key or Drop Foreign Key from the key’s pop-up menu.
Click Yes.