Creating and modifying tables

Paradox lets you create and modify database tables.

In this section, you'll learn about

  creating and opening tables
  
  applying a new block size to a table
  
  saving tables
  
  copying tables
  
  deleting tables
  
  emptying tables
  
  renaming tables
  
  moving, resizing, and rotating columns and rows in tables
  
  rearranging table fields
  
  viewing tables
  
This section also includes a reference topic that contains additional information about tables.

Creating and opening tables

Before creating tables in Paradox, you need to plan your table structure because well organized tables are the foundation of a successful relational database. When you are in the planning stage, it is important that your tables contain only the information that you need. For more information about planning a table structure, see "Table planning."

Each table should contain data about one entity, or subject, in a database. For example, you can create a table about an entity called customer. The customer table should include all vital information about the entity or, in this case, a customer. The information about the entity customer is called an attribute, which is represented by a column in a table. The customer table can include attributes such as customer number, name, address, and phone number. It is also important that the attributes are not duplicated within a table or between other tables.

Paradox lets you create tables by applying a new structure or by borrowing the structure of an existing table. When you borrow a table's structure, you can specify which table properties you want to borrow. For example, you can borrow a table's primary key or validity check definitions.

When you are ready to create the table, you must first define the fields. A field is a column of information in a table. A collection of related fields makes up one record. Field definition involves not only choosing a name for the field, but also specifying a field type and size. For more information about defining fields, "Creating, defining, and modifying table fields." You must also decide whether the field will be a key field for the table. For more information about key fields, see "Guidelines for defining key fields."

You can also open existing tables and tables that contain a password.

To create a blank table

1	Click File  New  Table.

2	Click Blank.

3	In the Table type dialog box, choose a table type from the Select type list box.

4	Click OK.

5	In the Create Paradox table dialog box, type a field name in the Field Name column and click OK.

6	Click a field in the Type column, and choose a field type from the list box.

If you choose a field type that requires a size, click a field in the Size column and choose a field size from the list box.

7	Specify validity checks for the valid field.

If you want to create a primary or composite key, click in the key column.

8	Click Create to save the table.

Notes

  For a detailed description of each field type, see Paradox field types in the Reference section of the online Help.
  
  For more information about validity checks, see "Validity checks."
  
  You can create a Paradox table using Local SQL by specifying the .db file extension when naming your table. If you omit the file extension for a local table name, the table created is the table type specified in the Default Driver setting in the System page of the BDE Configuration Utility.
  
To create a table with a borrowed structure

1	Click FileNewTable.

2	Click Blank.

3	In the Table type dialog box, choose a table type from the Select type list box.

4	In the Create Paradox table dialog box, click Borrow.

5	In the Select borrow table dialog box, choose the drive and folder where the table is stored.

6	Click the filename.

7	In the Options area, enable any of the following check boxes:

  Primary index - borrows the primary key from the table
  
  Validity checks - borrows the validity checks from the table
  
  Lookup table - borrows the lookup table assignments from fields in the table
  
  Secondary indexes - borrows secondary indexes from the table
  
  Referential integrity - borrows referential integrity relationships from the table
  
To open a table

1	Click FileOpenTable.

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

3	Double-click the filename.

To open a table with a password

1	Click FileOpenTable.

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

3	Double-click the filename.

4	Type the master password in the Password for box.

You can also	

Add a master password to the Paradox passwords list. 	Click Add.

Delete a master password from the Paradox password list.	Click Remove.

Delete all master passwords from the Paradox passwords list.	Click Remove all.

Notes

  For more information about creating passwords, see "Creating modifying and deleting a master password."
  
  When you add a master password to the password list, this allows you to gain access to any table that recognizes that password during your current Paradox session without having to re-enter the password.
  
Applying a new block size to a table

If you created a table in Paradox, the block size that is applied to the table should not be modified in Paradox. However, you can change the table's block size by changing the default block size, creating a new table, and then adding the old table's structure and records to the new table.

The first step in the process is to specify a new table block size in the BDE Administrator. After choosing a new block size, you need to create a blank table by borrowing the structure of the table to which you want to apply the new block size. You can then add the table records from the old table to the new table.

To apply a new block size to a table

You must perform the three following procedures in order to apply a new block size to a table.

To change the block size of a table in the BDE Administrator

1	Click Start on the Windows taskbar, click Settings  Control panel.

2	Double-click the BDE Administrator icon.

3	Click the Configuration tab.

4	Double-click the Drivers subfolder in the Configuration folder.

5	Double-click the Native subfolder in the Drivers folder.

6	Click Paradox in the Native subfolder.

7	In the Definition area, click in the Block size box.

8	Choose a block size from the Block size list box.

9	Click Object  Apply.

Notes

  You need to restart Paradox for the new Block size to take affect.
  
  The block size you specify in the BDE Administrator will be applied to all the tables you create in Paradox until the change is reverted.
  
To create a new table using the old table's structure

1	Click FileNewTable.

2	Click Blank.

3	In the Table type dialog box, choose Paradox 7, 8, 9.

4	In the Create Paradox table dialog box, click Borrow.

5	In the Select borrow table dialog box, choose the drive and folder where the original table is stored.

6	Click Open.

7	In the Create Paradox table dialog box, click Create.

8	Type a new name for the table in the File name box.

9	Click Save.

To add records from the old table to the new table

1	Click Tools  Project viewer.

2	Right-click the old table in the Project viewer file panel, and click Add.

3	In the Add records in dialog box, choose the drive and folder where the new table is stored.

4	Click Add.

Saving tables

Tables are automatically saved when you create them. However, if you restructure a table, you can save the table to retain the changes.

To save a table

1	In the Restructure Paradox table dialog box, click Save as.

2	Choose the drive and folder where you want to store the table.

Copying tables

Paradox lets you copy tables while keeping the table structure intact. If you copy a table to a network, Paradox must acquire a read lock on the original table and an exclusive lock on the copy. A read lock allows a user to open and read a table, but not modify it. An exclusive lock prevents users from viewing, changing, or locking the table. Therefore, no user can change the contents or the structure of the table you're copying.

To copy a table

1	Click Tools  Utilities  Copy.

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

3	Click the file name.

4	Click OK.

5	In the Copy to dialog box, choose the drive and folder where you want to store the copied table.

6	Type the table name in the File name box.

Notes

  If you copy to an existing table, there can be no locks open on that table. If there is a record lock, write lock, or exclusive lock on the table you're copying, you won't be able to make the copy until the lock is removed.
  
  The table elements are copied only when you copy the table to another table of the same type. In other words, they are copied only when you copy a Paradox table to another Paradox table or a dBASE table to another dBASE table.
  
Tip

  If the directory has an alias, you can choose it from the Alias list box. The dialog box displays the name and files of the new directory. However, when you copy a table from an external alias to a new location, select another alias before choosing another location.
  
Deleting tables

Paradox allows you to delete a table file and all the files that are related to that table. For example, one table in Paradox may involve more than one DOS file containing a table's primary index, secondary indexes, validity checks, referential integrity, or BLOB data.

You cannot delete a table that is identified as the parent in a referential integrity relationship. You must first delete the referential integrity from the child table, empty the child table, or delete the child table. For more information about referential integrity, see "Defining referential integrity."

Once you delete a table, you can't undo the deletion. Therefore, before deleting a table, be sure that the table isn't being used by any forms, reports, or queries. The forms, reports, or queries that depend on the table are not deleted when the table is deleted.

If you are deleting a table on a network, Paradox must acquire an exclusive lock on the table, which means no user can access the table in any way. Also, if there is a lock of any type open on the table, you must wait until it's released before you can delete it. This means you cannot delete a table that is open on your desktop.

To delete a table

1	Click Tools  Utilities  Delete.

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

3	Click the filename.

4	Click Delete.

Notes

  If you use the DOS DELETE command or delete files in Windows Explorer, you may not be deleting all your table's related files. The Paradox Delete command, however, deletes all files correctly.
  
  Before deleting a table, make sure that the table window, and any of its associated document windows, are closed.
  
Tip

  If the directory has an alias, you can choose it from the Alias list box. The dialog box displays the name and files of the new directory. However, when you copy a table from an external alias to a new location, select another alias before choosing another location.
  
Emptying tables

You can empty tables to remove all records from a table, leaving only the table's structure, including keys, indexes, and validity checks, intact. You can empty Paradox, dBASE, and SQL tables.

If you empty a table that is on a network, Paradox must acquire an exclusive lock on the table. This means that no user can access the table in any way. If there is a lock of any type open on the table, you must wait until it is released before you can empty it.

To empty a table

1	Click Tools  Utilities  Empty.

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

3	Click the filename.

4	Click Empty.

Notes

  You cannot empty a table that is identified as the parent in a referential integrity relationship. You must first delete the referential integrity, from the child table, or delete the child table.
  
  When you empty a dBASE table, all records in the table are marked as deleted.
  
Tip

  If the directory has an alias, you can choose it from the Alias list box. The dialog box displays the name and files of the new directory. However, when you copy a table from an external alias to a new location, select another alias before choosing another location.
  
Renaming tables

Paradox lets you rename existing tables. However, if you rename a table, you automatically rename any open forms and reports associated or bound with it. If you open the associated forms and reports in their design windows while you rename the table, Paradox automatically modifies them with the new table name. The next time you open an unbound object, Paradox asks you to supply the name of the table to which you'd like it to be bound. Saving the forms and reports to make the change permanent.

You cannot rename a table that is identified as the parent table in a referential integrity relationship. You must delete the referential integrity by restructuring the child table or deleting the child table. In addition, you cannot rename a table to change its type. A Paradox table must be renamed as a Paradox table, and a dBASE table must be renamed as a dBASE table.

Before renaming a table, Paradox must acquire an exclusive lock on the table. This means that no user can access the table in any way. If there is a lock of any type open on the table, you must wait until it is released before you can rename it.

To rename a table

1	Click Tools  Utilities  Rename.

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

3	Click the filename.

4	Click OK.

5	Type the new table name in the Filename box.

Note

  If you rename an object with an existing object's name, Paradox deletes the existing object.
  
Tip

  When renaming a table, you can also type a full path in the Filename bar. This renames the object and moves it to a new location simultaneously.
  
Moving, resizing, and rotating columns and rows in tables

Modifying tables and rows changes the way tables look. You can resize move, resize, and rotate columns. You can also resize rows and the column header.

To move a column

1	Select the column heading.

2	Drag the column heading to a new position.

The pointer changes to  as you move the mouse.

Note

  If you require information about keyboard shortcuts, see "Keyboard shortcuts."
  
Tips

  You can move a column to the right using the keyboard by selecting a field in the column you want to move and press ALT + right arrow.
  
  You can move a column to the left using the keyboard by selecting a field in the column you want to move and press ALT + left arrow.
  
To resize a column

1	Position the pointer over the vertical grid line.

The mouse pointer changes to  , which allows you to resize the column.

2	Drag the vertical grid line to the left or right.

Note

  If you require information about keyboard shortcuts, see "Keyboard shortcuts."
  
Tips

  You can increase column width using the keyboard by selecting a field in the column you want to resize and press CTRL + ALT + Right Arrow.
  
  You can decrease column width using the keyboard by selecting a field in the column you want to resize and press CTRL + ALT + Left Arrow.
  
To rotate a column

1	Click a record in the column you want to move.

2	Press CTRL + R.

Notes

  Paradox moves the column to the last position on the right of the table and shifts all other columns one position to the left.
  
  If you require information about keyboard shortcuts, see "Keyboard shortcuts."
  
To resize rows

1	Position the pointer over the horizontal grid line.

The mouse pointer changes to  , which allows you to resize the row.

2	Drag the horizontal grid line up or down.

Notes

  You can't individually resize table rows. When you resize rows, all the rows are resized at the same time.
  
  If you require information about keyboard shortcuts, see "Keyboard shortcuts."
  
Tips

  You can increase row height using the keyboard by selecting a field in a row and press CTRL + ALT + Down Arrow.
  
  You can decrease row height using the keyboard by selecting a field in a row and press CTRL + ALT + Up Arrow.
  
To resize the column header

1	In the first column of the table, position the pointer over the column header line.

The mouse pointer changes to  , which allows you to resize the column header.

2	Drag the column header line up or down.

Tips

  You can increase table header height using the keyboard by pressing CTRL + ALT + SHIFT + Down Arrow.
  
  You can decrease table header height using the keyboard by pressing CTRL + ALT + SHIFT + Up Arrow.
  
Rearranging table fields

Once a table is created, you can rearrange the order of fields in a table. When you change the field order and view the table data in an alternate format, such as a form or a report, the new field order is not applied.

To rearrange table fields

1	Open a table.

2	Click FormatRestructure table.

3	Click the Field Roster tab.

4	In the No. column, click the number of the field to move.

5	Drag the field to the new position.

Note

  You cannot move fields in a way that violates the rules for key fields. For example, a field defined as the key field must be the first field in the table.
  
Viewing tables

Once a table is created, you may need to reference its structure. For example, if you are creating a child table, you may need to find the parent table's primary key. Paradox lets you display the structure of a table, including any key, validity check, index, table lookup, or dependent tables.

When you create a large table that contains numerous fields that cannot be viewed all at once, you can set a lock on columns so that they remain in view. As you scroll to the right to view additional fields, the columns you locked remain in view. The horizontal scroll lock displays in the bottom left corner of the table window to indicate that the columns are locked.

To view table structure information

1	Click ToolsUtilitiesInfo structure.

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

3	Click the filename.

4	Click Open.

Note

  You cannot make changes to the table structure in the Structure information dialog box.
  
To set the table horizontal scroll lock

1	Position the pointer over the scroll lock until the pointer changes to a double-headed arrow.

2	Drag the scroll lock to the right of the column(s) you want to lock.

Note

  By default, the horizontal scroll lock is set to the left of the first column in the table so that all columns move as you scroll to the right.
  
Reference: Table planning

Planning is the first step in creating a table. You need to decide what type of data you want the table to contain and how you want to lay it out. When you plan a table, keep the following guidelines in mind:

1	Keep field entries simple.

Keeping field content simple allows for more flexible data maintenance and easier querying. For example, if you break an address into separate fields for street, city, and state, you can easily query on these specific field values. This is where designing a database table differs from designing a spreadsheet. If you need to display the data in a spreadsheet-like format, you can create a crosstab of the table's data.

2	Be complete.

Include fields for all the information you think you'll need, but don't clutter the table with information you don't need.

3	Use small tables.

If you have a lot of information to organize, it's generally better to place the information in several small, related tables rather than in one all-encompassing table.

4	Keep the tables familiar.

It's often best to create tables that correspond to the objects, such as forms and files, that you are currently using.

5	Avoid redundancy.

Don't duplicate information within a table or between different tables. 




