Creating, defining, and modifying table fields

Every database table contains fields in which store data. One data item is found in each field. All fields pertaining to one subject are referred to as a record, and all records are stored in tables. With Paradox, you can manage database tables by adding, defining, and modifying table fields.

In this section, you'll learn about

  adding and deleting fields
  
  defining and removing keys
  
  locating and repairing key violations
  
  renaming fields
  
  converting field types
  
  working with numeric data formats
  
  changing field order
  
  adding and displaying graphics in a field
  
  displaying and closing memo and BLOB windows
  
This section also includes a reference topic that contains additional information about table fields.

Adding and deleting fields

When you create a table, you can add and delete fields contained within the table. It is important to note that when you add or delete a field in a table, you alter any forms, reports, or queries associated with the table.

To add a field

1	Click Format  Restructure table.

2	Click the Filed roster tab.

3	Click a name in the Field Name column.

4	Press INSERT.

5	Type a name.

6	Choose a field type from the Field type list box.

7	Click in the Size column and choose a field size from the Size list box.

This option is not available for all field types.

8	Specify validity checks for the valid field.

Notes

  If you add a new field that has a default validity check on it and choose to enforce the validity check on data, Paradox creates the new field and places the default value in each record of the table. If you define a default validity check on a field that contains data, Paradox does not overwrite the data with the new default value. For more information about validity checks, see "Applying validity checks" and "Applying picture string validity checks."
  
  Paradox does not automatically add new fields to forms, reports, or queries associated with the table; you must add the table fields yourself.
  
To delete a field

1	Click Format  Restructure table.

2	Click a name in the Field Name column.

3	Press CTRL + DELETE.

4	Click Save.

Note

  When you delete a field from a table, Paradox unbinds the field from forms and reports. Since a deletion can cause data loss, Paradox lets you confirm the deletion or cancel the operation.
  
Defining and removing keys

You can define primary or composite keys to link tables and to sort records. A key is a required element for any table that is to be linked to other tables in a relational database. You cannot assign a key to memo, formatted memo, graphic, OLE, binary, logical, or bytes fields. You can also remove keys. However, if you are removing keys, you may cause a key violation. For more information about key violations, see "Locating and repairing key violations."

[VentGraphic 1, 0, ]Primary keys

When you designate a primary key, it must be placed on the first field in the table, and it must be unique. This means that it must consist of data that could not be duplicated. For example, it is not a good idea to use Last Name as a primary key in an employee table, because you might have more than one employee with the same last name. Employee No., a number unique to each individual employee, is a better choice. Using a unique field for the primary key ensures that tables can be linked without error. If you specify Employee No. as the primary key in one table, you can relate that table only to another that also has Employee No. as a field. In the first table, Employee No. is the primary key; in the second table, Employee No. is the foreign key. For example, if you create a key for the Last Name field of the sample contacts.db table, you are organizing the table by the values in the Last Name field, as shown in the following figure.

[VentGraphic 1, 0, ]Composite Keys

When you create a composite key, Paradox creates a primary composite index which organizes the records by the first field of the key, according to the table's structure, and then the next field, and so on. The following figure shows the contacts.db table with a composite key made up of the Last Name and First Name fields. Records 31 and 32 illustrate the composite key by sorting the records alphabetically by first name.

When you define a key field, Paradox makes it the primary index and sorts records on it. If you choose to define a composite key, you must include enough fields to ensure the uniqueness of each table record. A composite key has to handle all cases of duplicate data; therefore, you need to define a field that identifies one and only one record of the table.

For more information about defining key fields, see "Guidelines for defining key fields."

To define a primary key

1	Click Format  Restructure table.

2	Click the Field roster tab.

3	Drag a field to the top of the Field roster page.

4	Click in the key column of the field.

Note

  For more information about defining a primary key, see "Guidelines for defining key fields."
  
To define a composite key

1	Click Format  Restructure table.

2	Click the Field roster tab.

2	Click in the key column of the fields you want to define as the composite key.

Notes

  The fields that make up the composite key must be unique for each record of the table. The composite key must begin on the first field in the table and must include sequential fields.
  
  For more information about defining a composite key, see "Guidelines for defining key fields."
  
To remove a key

1	Click Format  Restructure table.

2	Click the key icon in the key column.

Notes

  If you remove a key, you must also either delete any secondary indexes or convert them to non-maintained indexes. For more information about secondary indexes, see "Defining and deleting secondary indexes."
  
  If you remove a primary key or key within a composite key that has other keys located beneath it, Paradox displays an error message when you try to save. To correct the error, ensure that all key fields are the first fields in the table.
  
Locating and repairing key violations

If you are removing keys, you may cause a key violation. A key violation means data already entered into the table violates the rules established by the key. A key violation may also occur when adding a primary key to a table that was previously unkeyed or had different keys. It can also occur if you rearrange fields so that the key fields are no longer the first consecutive fields in the table. 

Removing one or more fields from a composite key may cause duplicate values in the remaining key field(s) and also cause a key violation. If this happens, the duplicate records are placed in a temporary table called keyviol.db which you can locate in the PRIVATE folder.

If you cause a key violation, you can repair it by changing the records in keyviol.db so they comply with the key requirements, and then add them back to the original table.

To locate key violations

1	Click File  Open  Table.

2	Choose the drive where the PRIVATE folder is stored.

3	Double-click keyviol.db.

Note

  If there is already a keyviol.db table, Paradox adds a number to the new keyviol table, so it might appear as keyviol1.db or keyviol2.db. Paradox can create up to 100 temporary tables of the same name. The first table is not numbered and the last is number 99.
  
To fix a key violation

1	Click Tools  Utilities  Add.

2	Choose the drive and folder where the keyviol.db table is stored.

3	In the Add records in dialog box, choose the drive and folder where the table you want to repair the key violation is stored.

4	Change the records in the table so they comply with the key requirements.

Renaming fields

Paradox lets you rename table fields. However, if you edit a field name in a table and that field name appears on any associated design documents, Paradox reconciles the change the next time you open the design document. If the field is labeled in the design document, Paradox does not update the label of the field to the new name. If you have calculated fields in the design document that include the original field name, Paradox deletes the calculated field from the design document when you rename the field.

To rename a field

1	Click Format  Restructure table.

2	Click the Field roster tab.

3	Double-click a name in the Field name column.

4	Type the new field name.

Converting field types

Once you create a table, you may decide that you need to change a field type for given field. For example, you have an Employee table that contains a Salary field. When the initial table structure was created, the number type was applied to the Salary field. You later decide that the money type would be a more appropriate type for the Salary field. Therefore, the table needs to be restructured to convert the Salary field to the money type. With Paradox, you can convert a table field type. However, if the table contains data, you may encounter data format conflicts when you attempt to convert the field type.

You can convert fields to alpha, number, money, short, and long integer fields. You can also convert to auto-increment and date fields. For a list of compatible field types, see "Compatible Paradox field types".

Alpha field conversions

When you convert a field to an alpha field, you may encounter data format conflicts, because all formatting and other definitions associated with the original field type are lost. In addition, you must specify a field size if the data in the field contains more characters than the newly specified length of the alpha field, you can trim the data or move records containing such data to the problems table.

Number, money, short, and long integer field conversions

In a Paradox table, you can convert a money, long integer, BCD Binary Coded Decimal, auto-increment, or short field to a number field. Moreover, you can convert among all of these field types without losing data, except when a value is too large for a short field or includes decimals. In that case, you can either trim the values or have Paradox write records containing those values to the temporary problems table.

You can convert an alpha field to a number field if it contains data that is consistent with a number. If the data in the field is inconsistent with a number field, you must do one of the following:

  have Paradox place the records in a problems table
  
  delete the inconsistent data, and then make the conversion
  
  insert a new field, and delete the original field, which causes you to lose all data
  
Auto-increment field conversions

You can convert number, short, and long integer fields to auto-increment fields without losing data if the number, short, or long integer field is the table's single-field primary key. This ensures that the data to be converted to an auto-increment field meets the requirements of being unique and sorted in ascending order.

Date field conversions

You can convert alpha and timestamp fields to date fields. Paradox saves any invalid data in a problems table. If a record contains data in a field that cannot be interpreted as a date, Paradox removes the record and writes it to the temporary problems table. If you customize the date format using the Borland Databse Engine BDE Administrator, date values are converted according to your customized settings.

The following are examples of the alpha strings that can and cannot be converted to dates:

Can be converted	Cannot be converted

11-Nov-18	Veteran's Day

1.01.2000	New Year's Day, the year 2000

13.06.80	Herb's 29th birthday

7/04/1776	July 4, 1776

25-Dec-1066	Christmas Day, 1066

3/30/91	The 30th of March, 1991

To convert a field type

1	Click Format  Restructure table.

2	Click a field's type in the Type column.

3	Choose a field type from the Type list.

Notes

  If the change causes data loss, Paradox prompts you to confirm it. If you confirm the change, Paradox writes the records containing data that could not be converted to a temporary table called problems.
  
  If you convert between an alpha field and a date, number, short, or money field, make sure the settings in your Windows Control Panel match the settings in IDAPI.CFG.
  
Working with numeric data formats

Paradox provides standard data formats which you can apply to your money, number, date, time, timestamp, and logical. fields. You can also define custom formats for the fields. For example, you may want to have a certain number of decimal places displayed, or you may want to change the currency symbol. You can modify and delete the custom formats you create.

When you specify a data format, it does not change the data or how Paradox stores it. It only changes the way Paradox displays the data type.

To specify a numeric format

1	Right-click a numeric field, and click Properties.

2	Click the Format tab.

3	Choose a format from the Format list.

Notes

  You can also specify a number format in the Borland Database Engine Administrator.
  
  You can change the display of numbers, currency, times and dates in Paradox, and other Windows applications, by using the Windows Control Panel, Regional Settings. What you specify in Windows will be reflected in Paradox and becomes the predefined data format for databases, controlling how data displays in tables, forms, and reports.
  
To create a custom numeric format

1	Right-click a numeric field, and click Properties.

2	Click the Format tab.

3	Click Create new format.

4	In the Create number format dialog box, click Create.

5	Type a new name in the Name box.

6	In the Number format area, specify the settings for the format.

7	Click Add format.

Tip

  You can change the display of numbers, currency, times and dates in Paradox, and other Windows applications, by using the Windows Control Panel, Regional Settings. What you specify in Windows will be reflected in Paradox and becomes the predefined data format for databases, controlling how data displays in tables, forms, and reports.
  
To modify a custom numeric format

1	Right-click a numeric field, and click Properties.

2	Click the Format tab.

3	Click Create new format.

4	In the Create number format dialog box, choose a custom numeric format from the Existing formats list box.

5	Click Change.

6	In the Number format area, specify the new settings for the format.

7	Click Accept.

You can also	

Change the name of the format	Type a new name in the Name box.

Use a Windows Control Panel default format for a particular option	Right-click the format option's list box, and click a format.

To delete a custom numeric format

1	Right-click a numeric field, and click Properties.

2	Click the Format tab.

3	Click Create new format.

4	In the Create number format dialog box, choose a custom number format from the Existing formats list box.

5	Click Delete.

Changing field order

When you create a table, you can rearrange the field order. However, you cannot move fields in a way that violates the rules for key fields. For example, a field defined as a primary key field must be the first field in the table.

To change field order

1	Click Format  Restructure table.

2	Click the number of a field, and drag it to a new position.

Note

  You cannot move fields in a way that violates the rules for key fields. For more information about rules for key fields, see "Guidelines for defining key fields."
  
Adding and displaying graphics in a field

Paradox lets you create graphic fields in tables and add graphics to the fields. You can display a picture or graphic that is a scanned image, line art, or graphic file created in a painting or drawing application. When you place a graphic in a table, you might not be able to see all of it. However, you can size the graphic to fit the field.

To create a graphic field

1	Click Format  Restructure table.

2	Click the Field roster tab.

2	Click the Type column.

3	Choose Graphic from the Type list box.

To add a graphic to a graphic field

1	Open a graphic file in its source application.

2	Select the graphic.

3	Copy it to the clipboard.

4	In Paradox, click File  Open  Table.

5	Choose the file and folder where the table is stored.

6	Press F9.

7	Click a graphic field.

8	Click Edit  Paste.

Notes

  When you insert a graphic into a graphic field, Paradox converts the graphic to a .bmp file. It is recommended that you only insert 24 bit bitmaps.
  
  You cannot edit a graphic in Paradox; you must edit it in its source application.
  
Tip

  You can place bmp, .pcx, .tif, .gif, jpg or .eps graphic files directly into a graphic field without opening the graphic's source application by clicking a graphic field, then clicking Edit  Paste from, choosing the file and folder where the graphic is stored.
  
To display a graphic

1	Click a graphic field.

2	Click View  Field view.

Tip

  You can also display a graphic by double-clicking a graphic field.
  
To size a graphic to fit the field

1	Right-click a graphic field.

2	Click Properties.

3	Click the Magnification tab.

4	Choose Best fit from the Magnification list.

Displaying and closing memo and BLOB windows

Paradox lets you display fields that contain a memo or Binary Large Objects (BLOBs). When you add content to the field, the amount of text or graphic that you can see or read depends on the size of the cells in the table. However, using a different view, you can display the entire field content. You can then close the memo or BLOB window and return to the table.

To display a BLOB field

1	Click a BLOB field.

2	Click View  Field view.

Note

  Field types that can contain BLOBs include binary, memo, formatted memo, graphic,, and OLE fields.
  
Tip

  You can also display a BLOB field using the keyboard by selecting a BLOB field and pressing F2.
  
To display a memo field

1	Click a memo field.

2	Click View  Memo view.

Notes

  Paradox stores memo, formatted memo, and graphic fields in a separate file using the .mb extension for Paradox tables, or a .dbt extension for dBASE tables, but not in the table itself. A Paradox table contains a portion of the field which you specify when you create the field, plus a pointer to the .MB file. Paradox retrieves values from the .mb file when displaying memos and formatted memos.
  
  If you are working with a dBASE memo field, Paradox does not store any memo data in the .dbf file. Therefore, when you disable the Complete display option on dBASE memo fields, you do not see any of the memo. Instead, you see a marker indicating that the memo field contains data. When you click the field, Paradox displays the memo value from the .dbt file.
  
Tip

  You can display a memo field using the keyboard by selecting a memo field and pressing SHIFT + F2.
  
To display a complete memo field

1	Press F9.

2	Right-click a memo field, and click Properties.

3	In the BLOB options area, enable the Complete display check box.

Notes

  Paradox stores memo, formatted memo, and graphic fields in a separate file using the .MB extension for Paradox tables, or a .DBT extension for dBASE tables, but not in the table itself. A Paradox table contains a portion of the field which you specify when you create the field, plus a pointer to the .MB file. Paradox retrieves values from the .MB file when displaying memos and formatted memos.
  
  If you are working with a dBASE memo field, Paradox does not store any memo data in the .dbf file. Therefore, when you disable the Complete display check box for dBASE memo fields, you do not see any of the memo. Instead, you see a marker indicating that the memo field contains data. When you click the field, Paradox displays the memo value from the .dbt file.
  
To display a complete BLOB field

1	Press F9.

2	Right-click a BLOB field, and click Properties.

3	In the BLOB options area, enable the Complete display check box.

Note

  If you click on an individual BLOB field, Paradox displays the content of the field. However, the amount of the graphic that you can see depends on the size of the cells in the Paradox table.
  
To close a memo or BLOB window

  Press F2.
  
Note

  If you click on an individual memo field, Paradox displays the contents of the field. However, the amount of text that you can see depends on the size of the cells in the table.
  
Reference: Table fields

The following topic contains reference information pertaining to table fields.

Guidelines for defining key fields

Observe the following rules when defining key fields:

  A table can have only one key. This key can be made up of one or more fields.
  
  You cannot assign a key to memo, formatted memo, graphic, OLE, binary, logical, or bytes fields.
  
  If a key is defined as a single field, that field must be the first field in the table.
  
  If you identify more than one field as a key field, you create a composite key. These fields, taken as a group, must be unique for each record of the table. The composite key must begin on the first field in the table and must include sequential fields.
  
  Each value in the field must be unique. This ensures you do not have duplicate records in the table.
  
  You can leave only one record in the key field blank because subsequent blank fields are considered duplicates and records containing them are not accepted.
  
  The key establishes the default sort order for the table. Paradox sorts the table's records based on the values in the field(s) you define as the table's key.
  
Predefined money and number formats

You can apply any of the following formats to number or money fields.



Format	Description

Windows $	Applies the currency symbol and format that you defined in the Windows Control Panel

Windows #	Applies the number format that you defined in the Windows Control Panel

Fixed	Displays number values with two decimal places. Trailing zeros are displayed, but thousand separators are not used. Negative numbers are preceded by a minus sign (-).

Scientific	Displays number values in exponential notation (with four decimal places), as a decimal number from 1 to 10 multiplied by a power of 10. Negative numbers are preceded by a minus sign (-).

	All number formats use scientific notation to display numbers that are too big to fit. The scientific format always uses scientific notation.

General	Displays number values with up to two decimal places if the number includes a decimal value. Trailing zeros and thousand separators are not displayed. Negative numbers are preceded by a minus sign (-).

Comma	Displays number values with two decimal places. Trailing zeros are displayed and thousand separators are used and displayed, as a comma. Negative numbers are displayed in parentheses.

Percent	Displays numbers followed by the percent sign (%). For example, the value .5 is displayed as 50.0%. Thousand separators are not used. Negative numbers are preceded by a minus sign (-).

Integer	Displays whole numbers only. Decimal values are rounded when you convert to the Integer format. If you convert to a format that displays decimals, they are returned. Thousand separators are not used. Negative numbers are preceded by a minus sign (-).

DBNumeric	Uses the number format settings from your BDE configuration settings

Predefined date, time, timestamp and logical formats

You can apply any of the following date, time, timestamp and logical formats.

Predefined date formats

Format	Description

DB Date	Uses the date format settings from your BDE configuration settings. One digit is used for months and days 1 through 9, and two digits are used for the year in the format month/day/year (for example, 1/5/58).

ISO Date	(International Organization for Standardization.) Displays dates using four-digit numbers for the year, followed by two-digit numbers for the month and day, each separated by a period (for example, 1958.01.05).

mm/dd/yy	Displays dates using two-digit numbers for the month, followed by the day, followed by the year, with each separated by a forward slash (for example, 01/05/58).

Windows Long	Uses the long date format you define in the Windows Control Panel, Regional Settings Properties dialog box (for example, Sunday, January 5, 1958)

Windows Short	Uses the short date format you define in the Windows Control Panel, Regional Settings Properties dialog box (for example, 5/1/58).

For each format, a two-digit yy value from 00-50 (2000-2050) is assumed to be in the twenty-first century. Dates entered from 51-99 (1951-1999) are assumed to be in the twentieth century. To override the default date assumption, use a four-digit display, and specify all digits of the year. For all other centuries, you must specify all four digits.

Predefined time formats

Format	Description

Windows Time	Uses the time format you define from the Windows Control Panel, Regional Settings Properties dialog box

hh:mm:ss am	Displays two digits of hours, minutes, and seconds, separated by colons, and followed by "am" or "pm"

DBTime	Uses the time format settings from your BDE configuration settings

Predefined timestamp formats

Format	Description

Win. DateStamp	Uses the date and time formats you define in the Windows Control Panel, Regional Settings Properties dialog box

hh:mm:ss am mm/dd/yy	Displays hours, minutes, and seconds (2 digits each), separated by colons, and followed by "am" or "pm" and the month, day, and year

DBTimestamp	Uses the timestamp format settings from your BDE configuration settings

Predefined logical formats

The format list for a logical field shows the following predefined formats:

  Male/Female
  
  True/False
  
  Yes/No
  
These formats let you choose what values to accept as true and false in the logical field. 




