REFERENCE LIBRARY
LEARNING CENTER

Working with Databases

To continue with this content, please log in with your Data Access ID or create a new account.
Cancel Data Access ID
You may not be authorized to see this content. Please contact Data Access Europe for more information.
Cancel Data Access Europe
You are not authorized to see this content.
Cancel Data Access Europe

DataFlex courses

Information

Working with Databases
Introduction (0m 38s)
Creating your first table (6m 31s)
Next lesson:
Define related tables
Cancel

Working with databases

Lesson 3: Creating your first table

With the workspace prepared, the next step it to create a table.

  1. From the ‘Table Explorer’ on the left, select the ‘Create New Table’ icon from its top menu.
  2. From the ‘Create New Table’ window that opens, select the desired ‘Connection id,’ which is ‘cookbook’ for this table.
  3. Add a ‘Table Name.’ “Recipe” will be the name of this first table. Select ‘OK.’
  4. Add field/column names:
  5. FIELD 1 - Name: RecipeID, Type: int, Size: 10, DataFlex Type: Numeric
  6. This RecipeID will be the unique identifier.
  7. From the ‘Column Properties’ panel on the right, set the ‘is identity’ to “True” to allow SQL to provide the identities. This makes it so every new record will automatically get a unique RecipeID.
  8. FIELD 2 - Name: KitchenID, Type: int, Size: 10, DataFlex Type: Numeric
  9. This field will be the Foreign Key, which means it will define the relation to the Kitchen table.
  10. FIELD 3 - Name: Name, Type: varchar, Size: 50, DataFlex Type: ASCII
  11. FIELD 4 - Name: Rating, Type: tinyint, Size: 2, DataFlex Type: Numeric
  12. FIELD 5 - Name: HowToMake, Type: varchar(max), Size: 4095, DataFlex Type: Text
  13. FIELD 6 - Name: PreparationTime, Type: tinyint, Size: 3, DataFlex Type: Numeric
  14. FIELD 7 - Name: Created, Type: date, Size: 10, DataFlex Type: Date
  15. Note: When using an SQL database the tables shows the SQL type as ‘Type,’ and the ‘DataFlex Type’ it maps to. For example, ‘tinyint’ is seen by DataFlex as a ‘Numeric’ column. When using the embedded database, the DataFlex Types are the actual field types.
  16. Add indexes. From the ‘Indexes’ panel (mid-screen):
  17. Each table needs a unique index, which is typically the first index listed.
  18. Select ‘Add Index’ from the top of the panel.
  19. ‘Index 1’ is automatically added to the ‘Indexes’ list, and ‘RecipeId’ is added to the column list making it a unique index.
  20. Optional: Select ‘Index 1,’ and from the ‘Properties’ panel on the right change the ‘sql primary key’ to “True” to make it work faster with SQL.
  21. Note: The key icon added to the table alongside ‘RecipeId’ shows that it is now the primary key, and its ‘Main Index’ has been set to 1 because that is the main index for that field. Indexes other than the primary key are used to sort the data.
  22. Again, select ‘Add Index’ from the top of the panel creating ‘Index 2’, and select ‘Name’ for its column. This index will be sortable.
  23. Again, select ‘Add Index’ from the top of the panel creating ‘Index 3’, and select ‘Rating’ for its column.
  24. Add a second column to Index 3, ‘Name.’
  25. Note: Indexes must be unique. For example, if two identical names are entered an error will be returned.
  26. Save the table. 
  27. The ‘Output’ panel at the bottom will show the creation of the table, the assignment of the primary key, the other indexes, and the intermediate file in the data folder.
  28. View the table by selecting ‘Recipe’ from the ‘Table Explorer’ on the left. The table will be empty.

  29. For example purposes, some test records are added. 
©2024 Data Access Worldwide, all rights reserved
Privacy policy Cookies About dataaccess.com Online Documentation