A field can be defined as the intersection in a table where a row meets a column, containing a clearly differentiated atomic piece of information. Each data field should be unique and represent and indivisible quantity of information.
Each database field has a name, which enables the data to be accessed. A database field can be based on a domain definition or defined individually in the IBExpert Create Table or Table Editors, in which case Firebird/InterBase® automatically creates a system domain for the field definition.
Fields can be inserted into a table at the time of table creation, using the IBExpert DB Explorer or menu item New Table. It is however often necessary to add new fields after the table has been created. This can be easily done in IBExpert by opening the Table Editor (double-click on the relevant table in the IBExpert DB Explorer) or using the DB Explorer right-click menu Edit Table ... (or key combination [Ctrl + O]), and then inserting a field using the
Add Field icon (or [Ins] key) or the Table Editor right-click menu Insert Field, to open the Adding New Field Editor.
The Adding New Field Editor displays the table name, into which the field is to be inserted. The new field name can be specified by the user, along with the parameters Not NULL and Primary Key. Further options are to be found on the Default and Check pages, and the usual IBExpert Desc (= Description) and DDL (= Data Definition Language) information pages are also included.
The new field may be based upon an existing domain (which may be edited using the Edit button) or a new domain can be created directly from the New Field Editor. All existing domains (in the connected database) can be viewed in the Domain drop-down list. The domain information can be viewed in the Editor's lower panel.
It is also possible to define certain numeric formats as standard using the IBExpert Options menu item, Environment Options / Grid / Display Formats, if wished. These format standards can be overwritten in individual fields here in the Field Editor.
Of course a new field doesn't have to be based on a domain. The data type can be specified using the pull-down list under the Raw Data type tab. However, Firebird/InterBase® automatically generates a system domain for all specified fields, so when a new field is inserted, or existing field altered, Firebird/InterBase® inserts or alters the respective system domain.
Additional context-sensitive input fields appear, relevant to the data type selected (e.g. when
VARCHAR is selected, options for specifying Length, Charset, and Collate are offered; in the case of
NUMERIC, Precision and Scale can be specified).
The Autoincrement page allows new generators to be created, or an existing generator to be selected. New triggers and procedures can also be created directly here in this Editor for this field, if desired.
The last two pages display the object Description (which can be inserted, edited and deleted here by the user as wished), and the DDL page,
which displays the SQL code for the field as specified by the user.
Similar to Alter Domain, certain field attributes may be altered. For example, the data type, the field name and the field position may be altered;
CHECK instructions and default values may be added, altered or deleted. There are some limitations to modifying columns if they are used as part of a constraint, view, etc. Such dependencies can be viewed in the Domain Editor on the Used By page.
Fields can be altered in the Table Editor by double-clicking on the selected field, right-clicking and selecting Edit Field from the menu, or pressing the [Enter] key to open the Field Editor:
You will notice that you need to switch to the Domain Editor to perform any actual changes, as even if the field is not based on a user-defined domain, Firebird/InterBase® automatically creates a system domain for all field definitions. Simply click Edit Domain to spring to the Domain Editor:
The desired alterations can then be easily made to the user-defined or system domain and executed and checked before finally committing:
Fields can be dropped directly in the Table Editor on the Fields page, by using the "-" icon in the Table Editor toolbar, selecting from the right-click menu or using the key combination [Shift + Del].
IBExpert asks for confirmation:
before finally dropping the field. Once dropped, it cannot be retrieved.
When dropping fields, it is important to note that the field may not be part of the table's primary key, have a foreign key relationship with another table, contain a unique constraint, be part of a table constraint or part of another column's
The Constraints page in the Table Editor lists all such fields, so that the developer can quickly ascertain whether constraint alterations/deletions are necessary, before dropping the field in question (or whether, in fact, the field should be dropped at all!).
Using SQL the syntax is:
ALTER TABLE <table_name> DROP <field_name>;