In Database Tour software, there are several ways of creating a table in the database:
- Making a copy of existing table
- Exporting existing table to the current or another database
- Using the table creating interface
Here, the last way is described.
So, open the needed database, then choose Table | New... menu. There will be three options available:
Create a table using a special tool
Creating the table in a step-by-step mode.
First, you create an array of fields: names, types, sizes, primary key flags etc. Note that some capabilities are not supported for certain table types; in this case, the corresponding columns of the table of fields will be blocked and shown in gray color.
Then, you specify the table name and click Finish.
If no errors are found, then the specified table will be created and you will see it in the table list to the left of main window; otherwise, you may click Back button, correct erroneous field entries and try again. In the case of continuous error messages, you may go back to the first step, select the third way of creating the table (see 3. below), and generate SQL text, correct it manually to avoid the error(s), and create the table by executing the SQL query.
Working with Fields table is very similar to working with database tables in Database Tour. Here are described the hot keys for several most important actions:
| Add new entry above current row | CTRL + Insert |
| Add new entry in the end of table | Move to the last row and press Down Arrow or TAB (if you are in the last column) |
| Cancel adding new entry | Esc |
| Delete entry | CTRL + Delete |
Create a table using SQL
Creating the table by SQL query.
After selecting this option and clicking Next/Finish button, you will be moved to SQL Editor, in which CREATE TABLE template will be shown.
Another way of getting the same result is to create a new SQL window, click Templates button right above the SQL editor, and select Create Table template from the drop-down list of SQL templates.
After that, just correct the table and field names in this SQL text and click Execute button. If no errors are found, then the specified table will be created and you will see it in the table list to the left of main window.
Create a table using a tool to produce SQL
Here, you have an opportunity to specify field names and table name for the new table, as you do in the first way (see above). But after clicking the Finish button, the new table will not be created. Instead of this, you will be moved to SQL Editor, in which the SQL text will be generated according to the specified field array and table name. And you will need then to click Execute button by yourself to create the table.
If you have enough experience in SQL, you may check the generated SQL and correct it if needed before executing the query; otherwise this will be a good way to learn SQL for creating tables.
Notes
In some databases (for example, folder based databases, opened by BDE), at the second step you will need also to select a type of table to be created (e.g. dBase, Paradox, ASCII).
For folder based databases, specify table name without full file path. In some cases (for example, databases for Microsoft Text ODBC Driver), you need to add a file extension (.txt, .csv etc.).
Each database type has its own rules for naming fields and tables, their own field types etc. Some of them are constantly changing. To learn these rules, please read the documentation for your database. Here are some basic information and common recommendations:
- Maximum size of field names in dBase is 10.
- Maximum size of field names in Paradox is 25.
- Maximum size of field names in Oracle is 128 for version 12.2 and above, and 30 for versions 12.1 and below.
- Maximum size of field names in SQL Server is 128.
- Maximum size of field names in DB2 is 128.
- Maximum size of field names in PostgreSQL is 63.
- Maximum size of field names in MySQL is 64.
- For most database types, try to avoid spaces, special characters like ^, *, ! etc. in the field and table names.
- For most database types, try to avoid non-English characters in field and table names.
- For some database types, you should specify non-zero field size for all field types; but in some database types, you should specify field sizes only for some field types like String, Memo, Bytes etc., and should leave field size as 0 for other field types like Integer, Float, SmallInt etc.
See also
