Can I add fields to an existing table?

Creating tables and queries
Post Reply
LizH
Posts: 2
Joined: Fri May 04, 2018 9:21 pm

Can I add fields to an existing table?

Post by 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?
OpenOffice 4.1.5 on Windows 9
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can I add fields to an existing table?

Post by 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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
LizH
Posts: 2
Joined: Fri May 04, 2018 9:21 pm

Re: Can I add fields to an existing table?

Post by LizH »

Thanks, Donald. I'll look at these over the weekend.
OpenOffice 4.1.5 on Windows 9
Glen59
Posts: 5
Joined: Wed Dec 15, 2021 8:59 pm

Re: Can I add fields to an existing table?

Post by Glen59 »

Donald, what are the steps after I click edit ?
OpenOffice 4 Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Can I add fields to an existing table?

Post by 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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can I add fields to an existing table?

Post by 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.
Last edited by UnklDonald418 on Thu Dec 16, 2021 11:07 pm, edited 1 time in total.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can I add fields to an existing table?

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply