Can I add fields to an existing table?

[LizH]

I'm setting up my first simple database, really an address book with a few more fields. Can I add fields to my first table, or do I need to create a new table every time I want to add a field? Can I pull data from several tables for an input form?

[UnklDonald418]

Can I add fields to my first table

Right click on the table and select Edit to open the table design GUI.

Can I pull data from several tables for an input form?

Yes. Look here for
Base Tutorials
Also look at the link on that page to Database Examples
Documentation can be found at
https://wiki.openoffice.org/wiki/Docume ... e_Chapters
Chapter 8 relates to Base

[LizH]

Thanks, Donald. I'll look at these over the weekend.

[Glen59]

Donald, what are the steps after I click edit ?

[FJCC]

The dialog that opens should have a list of the table's column names and one for the corresponding data types. Just append new column names and their types.

[UnklDonald418]

To add a new field to an existing table using the Table Design GUI, scroll to the bottom of the list of existing fields and type the name for the new field.
Then select the data type and if it is a text field you can accept the default or specify a different Length.
Save your changes, either by clicking the Save icon or pressing <Ctrl>S.
You can change the names of existing fields, but changing their data type may not work if the table already has existing data.
If there is a problem, you will get an error dialog when you try to Save the changes or when you attempt to Exit the GUI.

Alternatively, you can skip the GUI and execute a command at TOOLS>SQL, something like

Code: Select all

ALTER TABLE "YourTableName" ADD COLUMN "NewColumnName" VARCHAR(n);

replacing the names of your table and the new column name and for a text field the maximum number of characters allowed. There is more flexibility using this method, because you can add further constraints, for instance executing the following command

Code: Select all

ALTER TABLE "YourTableName" ADD COLUMN "NewColumnName" VARCHAR(n) SET DEFAULT 'SomeString';

each time a new record is entered into the table the new column will have the specified default string automatically entered. There are a number of other constraints that can be added , some depending on the data type.

[Villeroy]

If you find yourself constantly adding new columns like addr1, addr2, phone1, phone2, email1, email2, email3 then you better follow another approach. All tables of a relational database should grow vertically row by row. Adding new columns during normal operation indicates that you either simply forgot some detail (which is just natural) or it is a common design flaw. In the latter case the correct approach would involve multiple lists of addresses, phones and mails which are related to a person. With input forms these interconnected lists are easy to maintain without any new columns and with no limits how many phones a person can own. Searching single columns of consecutive phone numbers is a lot easier than multiple columns of phones with gaps in them.