Creating Primary Key in a database from imported data

Creating tables and queries
Post Reply
WabashCannonball
Posts: 1
Joined: Fri Jan 26, 2018 12:06 am

Creating Primary Key in a database from imported data

Post by WabashCannonball »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Primary Key in a database from imported data

Post by Villeroy »

It is a two step procedure.

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;
[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
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
cjesag
Posts: 12
Joined: Tue Oct 18, 2011 3:31 am

Re: Creating Primary Key in a database from imported data

Post by cjesag »

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
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Creating Primary Key in a database from imported data

Post by FJCC »

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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Primary Key in a database from imported data

Post by Villeroy »

cjesag wrote: The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
Your Base document is connected to a spreasheet. See status bar.
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
cjesag
Posts: 12
Joined: Tue Oct 18, 2011 3:31 am

Re: Creating Primary Key in a database from imported data

Post by cjesag »

FJCC 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.
Yes, I used the Tools/SQL
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
cjesag
Posts: 12
Joined: Tue Oct 18, 2011 3:31 am

Re: Creating Primary Key in a database from imported data

Post by cjesag »

Villeroy wrote:
cjesag wrote: The GUI method offers me no drop-down box to select Primary Key and the "Index Design" icon is ghosted.
Your Base document is connected to a spreasheet. See status bar.
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?
Does this mean I cannot insert a Primary Key into my existing database?
Thanks,
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Primary Key in a database from imported data

Post by Villeroy »

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
cjesag
Posts: 12
Joined: Tue Oct 18, 2011 3:31 am

Re: Creating Primary Key in a database from imported data

Post by cjesag »

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.
Ahh. Thank you. That makes sense.
Charlie
OpenOffice 4.1.10 on Mac OS 11.4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating Primary Key in a database from imported data

Post by Villeroy »

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
Post Reply