Changing Primary Keys

Top  Previous  Next

Using the Universal Table Editor you can redefine or add a record identifier to your tables used in your project. This is also referred to as defining Primary Key in database jargon.  

 

Primary keys are very important both to good database design as well as for dbQwikSite.  A Primary Key, in simple terms is the column or group of columns that provide a unique RECORD identifier.  For example a person's name is NOT usually a good record identifier, because you could have two or more "Bobs" stored in your table.  Alternatively a person's TaxID or email, would uniquely identify which "Bob" record we are interested in.  Most databases will prevent you from adding two records that have the same primary key value.  So you can quickly get "stuck" if you choose a primary key that this not really unique to your data.

 

A primary key may consists of several columns.  For example  city name "London" many not be unique as there is a London UK and a London Canada.  However City+Country would uniquely identify a city.  When using multi-column keys, you should make sure that they are as "short" as possible, for example adding "Province" to the above combination does not add any value and would create additional overhead for storage and database searching.  If you find that you are have to specify many columns to achieve a unique identifier, you may want to consider an "system" identifier such as an auto-increment field which assigns a  sequential number to each record.

 

In general rules for good primary keys are:

Must be unique across all records in a table
All values used in the primary key must be available at time of creation of the record.
Keep them short (no extra column if not absolutely needed)
Use system identifiers, where there is no obvious business identifier.
A table can have only one primary key
A table should always have a primary key

 

Some databases will allow you to create tables with no primary key.  While this is valid in some special cases in database design, it is not recommended if you want to use dbQwikSite to generate pages for that table.  dbQwikSite, uses the primary key to pass record references from one page, so without a primary key, your pages may not flow as expected.

 

To Add a Primary Key:

A primary key is really a special index.

Click the Edit Table Index Button

 btn_db_table_edit_Index_001

The New Index Dialog Appears.

Here you name your index, select the columns to include in the index and, select index options, to make this index a Primary Key, you check the Primary Key box, all Primary Keys should be Unique, so also check the Unique box.

dlg_db_table_index_001

 

To Edit a Primary Key

 To edit an existing primary key first select any checked item in the column "Primary Key"

                 grd_db_table_editor_PKs_001

         and either double click or click the edit table index

                  btn_db_table_edit_Index_001

 The above editor appears and you can add and remove columns from your primary key.