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

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.



To Edit a Primary Key
To edit an existing primary key first select any checked item in the column "Primary Key"
       and either double click or click the edit table index
The above editor appears and you can add and remove columns from your primary key.