Creating Primary Key in a database from imported data
-
- Posts: 1
- Joined: Fri Jan 26, 2018 12:06 am
Creating Primary Key in a database from imported data
I imported some data from an existing spreadsheet. My problem is, how do I set up the obligatory primary key retroactively? Also, how do you get the programme to automatically generate a value to the primary key heading?
OpenOffice 4.1.4 on Chrome
Re: Creating Primary Key in a database from imported data
It is a two step procedure.
Easy without GUI:
menu:Tools>SQL...
[insert your actual table and column names]
menu:View>Refresh Tables
In the GUI:
Open the table for editing (right-click>Edit...)
Right-click the column selector (the grey box left of the name) and choose "Primary Key"
Apply (save) this change.
Now mark the column as "AutoValue"
The embedded HSQLDB (see status bar of the database window) is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Easy without GUI:
menu:Tools>SQL...
Code: Select all
ALTER TABLE "Table Name" ADD PRIMARY KEY ("Column Name");
ALTER TABLE "Table Name" ALTER COLUMN "Column Name" INTEGER IDENTITY;
menu:View>Refresh Tables
In the GUI:
Open the table for editing (right-click>Edit...)
Right-click the column selector (the grey box left of the name) and choose "Primary Key"
Apply (save) this change.
Now mark the column as "AutoValue"
The embedded HSQLDB (see status bar of the database window) is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Creating Primary Key in a database from imported data
OpenOffice 4.1.9
Mac OS 11.2.2
I have the same situation. The application frequently crashes when it tries accessing the Data Source for mail merge operations. I was hoping that redoing the database and including the primary key could correct this problem.
The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
The without GUI method
ALTER TABLE "Data" ADD PRIMARY KEY ("key");
ALTER TABLE "Data" ALTER COLUMN "key" INTEGER IDENTITY;
returns the error:
1: syntax error, unexpected ALTER, expecting BETWEEN or SQL_TOKEN_IN or SQL_TOKEN_LIKE
Any help is greatly appreciated.
Charlie
Mac OS 11.2.2
I have the same situation. The application frequently crashes when it tries accessing the Data Source for mail merge operations. I was hoping that redoing the database and including the primary key could correct this problem.
The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
The without GUI method
ALTER TABLE "Data" ADD PRIMARY KEY ("key");
ALTER TABLE "Data" ALTER COLUMN "key" INTEGER IDENTITY;
returns the error:
1: syntax error, unexpected ALTER, expecting BETWEEN or SQL_TOKEN_IN or SQL_TOKEN_LIKE
Any help is greatly appreciated.
Charlie
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
OpenOffice 4.1.10 on Mac OS 11.4
Re: Creating Primary Key in a database from imported data
Are you using the menu Tools -> SQL to execute the query? I cannot duplicate your error but I do get an error when I try to execute an ALTER query in the regular query window.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Creating Primary Key in a database from imported data
Your Base document is connected to a spreasheet. See status bar.cjesag wrote: The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Creating Primary Key in a database from imported data
Yes, I used the Tools/SQLFJCC wrote:Are you using the menu Tools -> SQL to execute the query? I cannot duplicate your error but I do get an error when I try to execute an ALTER query in the regular query window.
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
OpenOffice 4.1.10 on Mac OS 11.4
Re: Creating Primary Key in a database from imported data
Yes it is. When the database was initiated, I already had a large spreadsheet and since there is an option to create the database from an existing spreadsheet I went that route. Maybe this isn't "imported data" as the thread subject suggests?Villeroy wrote:Your Base document is connected to a spreasheet. See status bar.cjesag wrote: The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
Does this mean I cannot insert a Primary Key into my existing database?
Thanks,
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
OpenOffice 4.1.10 on Mac OS 11.4
Re: Creating Primary Key in a database from imported data
All your data are still in the spreadsheet and only a spreadsheet application such as Calc can edit these data. This is a pseudo-database because the structures that are stored in a spreadsheet have nothing in common with the structures that are stored in a database. Base presents the sheets' contents as if they were tables of a true database.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Creating Primary Key in a database from imported data
Ahh. Thank you. That makes sense.Villeroy wrote:All your data are still in the spreadsheet and only a spreadsheet application such as Calc can edit these data. This is a pseudo-database because the structures that are stored in a spreadsheet have nothing in common with the structures that are stored in a database. Base presents the sheets' contents as if they were tables of a true database.
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
OpenOffice 4.1.10 on Mac OS 11.4
Re: Creating Primary Key in a database from imported data
Contrary to the topic title " View first unread post Creating Primary Key in a database from imported data", nothing has been imported, copied, converted. The Base document is linked to spreadsheet data.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice