Working with keys – PB Docs 2018

• 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

• 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

• A primary key (on id)

• Two foreign keys (on cust_id and fin_code_id)

If you cannot see the lines

If the color of your window background makes it difficult 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.

To open the table that a particular foreign key
references:

1. Display the foreign key pop-up menu.

2. Select Open Referenced Table.

To open all tables referencing a particular primary
key:

1. Display the primary key pop-up menu.

2. Select Open Dependent Table(s).

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

To create a primary key:

1. 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 main 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.

2. Select one or more columns for the primary key.

   Columns that are allowed in a primary key

   Only a column that does not allow null values can be
   included as a column in a primary key definition. If you choose a
   column that allows null values, you get a DBMS error when you save
   the table. In DBMSs that allow rollback for Data Definition
   Language (DDL), the table definition is rolled back. In DBMSs that
   do not allow rollback for DDL, the Database painter is refreshed
   with the current definition of the table.

3. Specify any information required by your DBMS.

   Naming a primary key

   Some DBMSs allow you to name a primary key and specify
   whether it is clustered or not clustered. For these DBMSs, the
   Primary Key property page has a way to specify these
   properties.

   For DBMS-specific information, see your DBMS
   documentation.

4. 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.

   Completing the primary key

   Some DBMSs automatically create a unique index when you
   define a primary key so that you can immediately begin to add data
   to the table. Others require you to create a unique index
   separately to support the primary key before populating the table
   with data.

   To find out what your DBMS does, see your DBMS
   documentation.

To create a foreign key:

1. 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 main 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.
   Some of the information is DBMS-specific.

2. Name the foreign key in the Foreign Key Name box.

3. Select the columns for the foreign key.

4. 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).

5. On the Rules tab page, specify any information required by
   your DBMS.

   For example, you might need to specify a delete rule by
   selecting one of the rules listed for On Delete of Primary Table
   Row.

   For DBMS-specific information, see your DBMS
   documentation.

6. 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.

To modify a primary key:

1. 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.

2. Drag the primary key icon and drop it in the Object Details
   view.

3. Select one or more columns for the primary key.

4. 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.

To drop a key:

1. 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.

2. Select Drop Primary Key or Drop Foreign Key from the key’s
   pop-up menu.

3. Click Yes.