[Solved] Tables not retaining pkey when making a duplicate

[MikeytheMagnificent]

When working with c&p to make a working copy of a table, absolutely no probs, my copytable is perfect writable, editable and deleteable.

But, when I try to copy via tools/sql/execute_command (select * into COMPS1 from COMPS) the table although copied, loses its pkey attribute and the table becomes read only and hence cannot be manipulated. This comes as an unpleasant surprise to me.

q1. How can I restore the pkey attribute once the copy table has landed? Even if I try to do it via edit table/create (reset) pkey it pretends its resetting it, but it IS only pretending!
obviously
select * ...... isn't the correct command ( or is incomplete), even though it comes recommended at
http://www.w3schools.com/sql/sql_select_into.asp
In fact I am failing to be able to upgrade a readonly table and can add neither a pkey nor unique index

q2. Better still, How do I achieve my not_read_only copy in one single sql command?
q3. How to land an empty (without data) copy via sql? and
q4. what sql command to append data from a second copy into the first copy? probably
Merge into COMPS1 from COMPS2 or similar, but first I need COMPS1 [rwed] via the sql route

most has never been resolved in my dead thread at
http://user.services.openoffice.org/en/ ... 61&t=55866 last post
Perhaps anyone able to kindly solve q1,q2 can offer me as much advice as possible to help solve q3,q4
--

[RPG]

Hello

Q1: I think you should work with the update command or delete first all records and then insert the new records.

Romke

[MikeytheMagnificent]

However-which-way, I first need a copied table which is editable and I'm not getting that via the sql route although it is automatic via c&p.

Is this the way it is with you?

I'm not sure I'm sure what you're trying to say, afaik all UPDATE will do is change all (filtered) Records to some new value, say 1324 and not create a value sequence. The values I need are there and copied already, the problem is the table they are in isn't a proper workable table. How can I make it useable.

When you (Romke) say

I think....

Is that Dutch for

You Very Silly Englishman...

[RPG]

Hello

I have real problems to understand your language. You are not talking in a way I can understand from my knowledge about SQL and also of OOo. Also I'm a normal home user and not a professional.

pretends its resetting it,

This makes me to think you want reset your tables. Resetting your tables is: Delete your old values and fill it with the value you want have. This can also be done with updating.

It maybe clear that a PK must be always unique in a table.

For most starters the next is true: When you do not get the result you expect you do something wrong. Most of the time the wrong result is not a bug in the system your use.

Working with SQL in macros and forms without understanding is giving a lot of problems. Be aware that the forms are not aware of the modified tables by macros. If you do a lot of SQL command and maybe also change your tables with macros then how can we find your problem.

Romke

[DACM]

The SQL varies depending on the current structure/state of your table. Assuming the built-in HSQL 1.8 database engine...

To copy/backup an existing table containing a Primary Key with AutoValue enabled (or otherwise containing an appropriate INTEGER-like field with unique values) use Tools > SQL... :

• Code: Select all

  SELECT * INTO "NewTable" FROM "ExistingTable";
  ALTER TABLE "NewTable" ADD PRIMARY KEY ("ColumnName");
  ALTER TABLE "NewTable" ALTER COLUMN "ColumnName" INTEGER IDENTITY;
  

  Then click: View > Refresh Tables to see the change in the Base GUI.
  Notice it's the second line of SQL above that overcomes the bug in the Base GUI (Table > Edit function); the other two lines of SQL can be accomplished using the Base GUI.

To add a new column as the Primary Key with AutoValue functionality:

• Code: Select all

  ALTER TABLE "ExistingTable" ADD COLUMN "ColumnName" INTEGER IDENTITY;

  or you can also specify the starting value and/or column position (among the options):

  Code: Select all

  ALTER TABLE "ExistingTable" ADD COLUMN "ColumnName" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) BEFORE "ExistingColumn";

  Notice we can use a single line of SQL (above) when adding a new column. In this case, the HSQL 1.8 engine adds the Primary Key constraint automatically when applying the AutoValue attribute (IDENTITY). This is not the case with HSQLDB 2.x which allows you to apply sequence generators to columns other than the Primary Key column(s).

The Base GUI offers limited DDL functionality to assist in creating and adjusting table structures and relationships. And as you've experienced, the 'Table > Edit' interface has some holes and bugs . But can you blame it? Well yes, perhaps it should be better tuned for the built-in version of the HSQL database engine. But otherwise, DDL syntax and features vary widely across supported database engines including HSQLDB 1.8, HSQLDB 2.x, H2, MySQL, PostgreSQL, Oracle, etc. So it's not uncommon to find yourself using native/third-party management software, or simply researching the engine's documentation/forums for the proper SQL syntax. I'll admit, universal DDL coverage is a little too much to expect from the Base GUI.

[MikeytheMagnificent]

First, Romke, later DACM

Many thanks for your replies, both very welcome. Sorry, The 'I think...' thing was an appreciation not a sarcasm. I admire everything you say and apologise if you read (past tense) it otherwise.
When I say "pretends it is resetting the pkey" I simply meant it has made a copy table but annoyingly, without a pkey which, when I goto table/edit (structure) the gold key which appears when I rmb on the ID field then table/save and close has disappeared when I try to use it
and yes resetting is a poor choice of word because I dont want to reset any values. I only want my pkey from my old table to become a pkey in my new working copy ie a full carbon copy

I think I do understand the sql you speak of, and was simply mystified that the 'select into' command copied everything from one table to its backup including all the values and column formats (integer, varchar etc) including the pkey values, unfortunately it did not retain those values as pkey, and left the table keyless and hence read only. Once I had got to the read only situation I had been unable to re-upgrade it to a 'proper table'. Perhaps it's intended and not a bug, but to me it would seem more sensible to make the copy identical just like http://www.w3schools.com/sql/sql_select_into.asp and if you dont fancy keeping the keys and formats, to do something about that the more complex way. Still it is the way it is, for now
Despite what Villeroi says and I'm not trying to deny his tutoring only to say that I haven't been able to replicate his result here in Windoze


In table design you right-click some field(s) and set option "Primary key".

Setting a primary key in the table editor (in German screenshot: "Primärschlüssel")

as soon as I save and close the table/edit window with gold key as shown, the moment I reopen it or try to edit some data I find the pkey has vanished and the table had reverted to read only. I presume V's didn't. Anyone know how to fix this for me, please?? or anyone else getting my result??
I can however import a 'proper editable table' by pasting it in from a spreadsheet with a suitable unique pkey candidate, then change all the column formats back to integer,decimal,date,boolean etc and nominating the pkey (rmb in the correct pre-existing field) before I press the create button. Alternatively I could ask Base to create a new temporary pkey automatically, then swap it with my old one after creationI had understood from somewhere that Base would make some intelligent guesses about column formats imported from an .ods but pkey candidates and even dates are turned into varchars and cannot be reformatted once the creation process has run. If you do make this mistake you can c&p whatever you've got, back into a new .ods which remarkably still remembers all about the formats it set when it was originally created and which Base inconveniently failed to make a note of then paste it straight back to a new Base Table, this time remembering to edit your column formats before running the create table process.
With regard to the Update Command in the tools/sql/command


Update COMPS set DATE = 29/05/1990 where REG=true will reset the DATE of every record with REG=true
the total number of records remains unchanged but the contents could be devastated in one command
To create new extra records I use the Insert into COMPS command, each Insert can only insert one record (I think)
Insert into COMPS2 (REG,DUMMY) values (true,true) and will include an auto pkey
Wheras Delete from COMPS will delete 1 some or all records in 1 command. There is no undo

not to be confused with

oStatement.executeUpdate ( "insert into COMPS2 (REG,DUMMY) values (true,true)") inserts 1 additional record with a new autoPkey
oStatement.executeUpdate ("Update COMPS set LONGJUMP= 7022 where REG = true") changes all records where REG =true to 7022
oStatement.executeUpdate ("Delete from EMPLOYEES2 where EMPID in (10,14)" ) deletes some records and ignores the rest
These are only available in Basic Macro Programmes and despite the executeUpdate title can be Inserts, Deletes or Updates and are identical in syntax to the Commands BUT the values can now be variables replacing fixed values and the statements can be looped and repeated many times in one proceedure


DACM your answer was bang on the money and the single 4 line command

drop table COMPS1 if exists;
SELECT * INTO COMPS1 FROM COMPS;
ALTER TABLE COMPS1 ADD PRIMARY KEY (ENT);
ALTER TABLE COMPS1 ALTER COLUMN ENT INTEGER IDENTITY;

The last 2 lines solving the 2 bugs create primary key from existing column and thus make the table useable
and make the newly re-created primary key autoincrementing as it was in the original

and thus did exactly as required in my case to make my backup (or working) copy identical to the original
I was unaware you and others had tackled previously http://user.services.openoffice.org/en/ ... 78#p187178
Please can we have more solved problems like this written up as short tutorials where they'd be much easier to locate, just like your Data Loss Eliminator. Excellent, Thanks very much.
Even the Table/Refresh annoyance is a worthwhile subject for those who dont know about it. Is it something we've got for eternity because I need all my tables and forms to refresh automatically whenever I run a sub