Creating and Deleting Tables
Go Up to Using Table Type Datasets
Some table type datasets let you create and delete the underlying tables at design time or at run time. Typically, database tables are created and deleted by a database administrator. However, it can be handy during application development and testing to create and destroy database tables that your application can use.
Creating tables
TTable and TIBTable both let you create the underlying database table without using SQL. Similarly, TClientDataSet lets you create a dataset when you are not working with a dataset provider. Using TTable and TClientDataSet, you can create the table at design time or runtime. TIBTable only lets you create tables at run time.
Before you can create the table, you must be set properties to specify the structure of the table you are creating. In particular, you must specify
- The database that will host the new table. For TTable, you specify the database using the DatabaseName property. For TIBTable, you must use a TIBDatabase component, which is assigned to the Database property. (Client datasets do not use a database.)
- The type of database (TTable only). Set the TableType property to the desired type of table. For Paradox, dBASE, or ASCII tables, set TableType to ttParadox, ttDBase, or ttASCII, respectively. For all other table types, set TableType to ttDefault.
- The name of the table you want to create. Both TTable and TIBTable have a TableName property for the name of the new table. Client datasets do not use a table name, but you should specify the FileName property before you save the new table. If you create a table that duplicates the name of an existing table, the existing table and all its data are overwritten by the newly created table. The old table and its data cannot be recovered. To avoid overwriting an existing table, you can check the Exists property at run time. Exists is only available on TTable and TIBTable.
- Indexes for the new table (optional). At design time, double-click the IndexDefs property in the Object Inspector to bring up the collection editor. Use the collection editor to add, remove, or change the properties of index definitions. At runtime, clear any existing index definitions, and then use the AddIndexDef method to add each new index definition. For each new index definition, set the properties of the Data.DB.TIndexDef object to specify the desired attributes of the index.
- The fields for the new table. There are two ways to do this:
- You can add field definitions to the FieldDefs property. At design time, double-click the FieldDefs property in the Object Inspector to bring up the collection editor. Use the collection editor to add, remove, or change the properties of the field definitions. At runtime, clear any existing field definitions and then use the AddFieldDef method to add each new field definition. For each new field definition, set the properties of the Data.DB.TFieldDef object to specify the desired attributes of the field.
- You can use persistent field components instead. At design time, double-click on the dataset to bring up the Fields editor. In the Fields editor, right-click and choose the New Field command. Describe the basic properties of your field. Once the field is created, you can alter its properties in the Object Inspector by selecting the field in the Fields editor.
Note: You can't define indexes for the new table if you are using persistent field components instead of field definition objects.
To create the table at design time, right-click the dataset and choose Create Table (TTable) or Create Data Set (TClientDataSet). This command does not appear on the context menu until you have specified all the necessary information.
To create the table at run time, call the CreateTable method (TTable and TIBTable) or the CreateDataSet method (TClientDataSet).
Note: You can set up the definitions at design time and then call the CreateTable (or CreateDataSet) method at run time to create the table. However, to do so you must indicate that the definitions specified at run time should be saved with the dataset component. (by default, field and index definitions are generated dynamically at run time). Specify that the definitions should be saved with the dataset by setting its StoreDefs property to True.
Tip: If you are using TTable, you can preload the field definitions and index definitions of an existing table at design time. Set the DatabaseName and TableName properties to specify the existing table. Right click the table component and choose Update Table Definition. This automatically sets the values of the FieldDefs and IndexDefs properties to describe the fields and indexes of the existing table. Next, reset the DatabaseName and TableName to specify the table you want to create, canceling any prompts to rename the existing table.
Note: When creating Oracle8 tables, you can't create object fields (ADT fields, array fields, and dataset fields).
The following code creates a new table at run time and associates it with the DBDEMOS alias. Before it creates the new table, it verifies that the table name provided does not match the name of an existing table:
var
TableFound: Boolean;
begin
with TTable.Create(nil) do // create a temporary TTable component
begin
try
{ set properties of the temporary TTable component }
Active := False;
DatabaseName := 'DBDEMOS';
TableName := Edit1.Text;
TableType := ttDefault;
{ define fields for the new table }
FieldDefs.Clear;
with FieldDefs.AddFieldDef do begin
Name := 'First';
DataType := ftString;
Size := 20;
Required := False;
end;
with FieldDefs.AddFieldDef do begin
Name := 'Second';
DataType := ftString;
Size := 30;
Required := False;
end;
{ define indexes for the new table }
IndexDefs.Clear;
with IndexDefs.AddIndexDef do begin
Name := '';
Fields := 'First';
Options := [ixPrimary];
end;
TableFound := Exists; // check whether the table already exists
if TableFound then
if MessageDlg('Overwrite existing table ' + Edit1.Text + '?',
mtConfirmation, mbYesNoCancel, 0) = mrYes then
TableFound := False;
if not TableFound then
CreateTable; // create the table
finally
Free; // destroy the temporary TTable when done
end;
end;
end;
TTable *NewTable = new TTable(Form1);
NewTable->Active = false;
NewTable->DatabaseName = "BCDEMOS";
NewTable->TableName = Edit1->Text;
NewTable->TableType = ttDefault;
NewTable->FieldDefs->Clear();
TFieldDef *NewField = NewTable->FieldDefs->AddFieldDef(); // define first field
NewField->DataType = ftInteger;
NewField->Name = Edit2->Text;
NewField = NewTable->FieldDefs->AddFieldDef(); // define second field
NewField->DataType = ftString;
NewField->Size = StrToInt(Edit3->Text);
NewField->Name = Edit4->Text;
NewTable->IndexDefs->Clear();
TIndexDef *NewIndex = NewTable->IndexDefs->AddIndexDef(); // add an index
NewIndex->Name = "PrimaryIndex";
NewIndex->Fields = Edit2->Text;
NewIndex->Options << ixPrimary << ixUnique;
// Now check for prior existence of this table
bool CreateIt = (!NewTable->Exists);
if (!CreateIt)
if (Application->MessageBox((AnsiString("Overwrite table ") + Edit1->Text +
AnsiString("?")).c_str(),
"Table Exists", MB_YESNO) == IDYES)
CreateIt = true;
if (CreateIt)
NewTable->CreateTable(); // create the table
Deleting tables
TTable and TIBTable let you delete tables from the underlying database table without using SQL. To delete a table at run time, call the dataset's DeleteTable method. For example, the following statement removes the table underlying a dataset:
CustomersTable.DeleteTable;
CustomersTable->DeleteTable();
Warning: When you delete a table with DeleteTable, the table and all its data are gone forever.
If you are using TTable, you can also delete tables at design time: Right-click the table component and select Delete Table from the context menu. The Delete Table menu pick is only present if the table component represents an existing database table (the DatabaseName and TableName properties specify an existing table).
See Also
- Sorting Records with Indexes
- Limiting Records with Ranges
- Using Indexes to Search for Records
- Creating Master-Detail Relationships
- Controlling Read-Write Access to Tables
- Emptying Tables
- Synchronizing Tables