Integer Not Null

SewHelpMe · June 17, 2012, 12:18am

This is my first time using LibreOffice Base and in case I'm not very clear
... I haven't programmed anything in almost 15 years so this is a bit of a
transition for an old lady.

I'm creating a table and I want a field to be of type Integer and Not Null.
(Note: I already have another field in the same table that is with the same
configuration, ie Integer/Not Null, and I'm editing an existing table to add
this field.)

I keep getting the error message:

Error while saving the table design
Column constraints are not acceptable in statement [ALTER TABLE "Companion
Subspecies" ADD "Help Type" INTEGER NOT NULL]

Could someone kindly point me in the right direction to correct my problem?

Thank you.

elderdanlewis · June 17, 2012, 12:33am

SewHelpMe wrote:

This is my first time using LibreOffice Base and in case I'm not very clear
... I haven't programmed anything in almost 15 years so this is a bit of a
transition for an old lady.

I'm creating a table and I want a field to be of type Integer and Not Null.
(Note: I already have another field in the same table that is with the same
configuration, ie Integer/Not Null, and I'm editing an existing table to add
this field.)

I keep getting the error message:

Error while saving the table design
Column constraints are not acceptable in statement [ALTER TABLE "Companion
Subspecies" ADD "Help Type" INTEGER NOT NULL]

Could someone kindly point me in the right direction to correct my problem?

Thank you.

--
View this message in context: http://nabble.documentfoundation.org/Integer-Not-Null-tp3990551.html
Sent from the Users mailing list archive at Nabble.com.

Are you using SQL to edit the table? Or, are you clicking the table, and selecting Edit from the context menu? (This opens the Table Design dialog.)

--Dan

elderdanlewis · June 17, 2012, 1:10am

SewHelpMe wrote:

This is my first time using LibreOffice Base and in case I'm not very clear
... I haven't programmed anything in almost 15 years so this is a bit of a
transition for an old lady.

I'm creating a table and I want a field to be of type Integer and Not Null.
(Note: I already have another field in the same table that is with the same
configuration, ie Integer/Not Null, and I'm editing an existing table to add
this field.)

I keep getting the error message:

Error while saving the table design
Column constraints are not acceptable in statement [ALTER TABLE "Companion
Subspecies" ADD "Help Type" INTEGER NOT NULL]

Could someone kindly point me in the right direction to correct my problem?

Thank you.

--
View this message in context: http://nabble.documentfoundation.org/Integer-Not-Null-tp3990551.html
Sent from the Users mailing list archive at Nabble.com.

I had to read your email another time to understand what you were saying.
The SQL for altering a table by adding a field is:

ALTER TABLE <tablename> ADD [COLUMN] <columnname> Datatype
[(columnSize[,precision])] [{DEFAULT <defaultValue> |
GENERATED BY DEFAULT AS IDENTITY (START WITH <n>[, INCREMENT BY <m>])}] |
[[NOT] NULL] [IDENTITY] [PRIMARY KEY]
[BEFORE <existingcolumn>];

From what I read, "Companion Subspecies" is the table's name and "Help type" is the field's name. Putting these into the SQL statement:
ALTER TABLE "Companion Subspecies" ADD COLUMN "Help type" INTEGER NOT NULL;

Looks like you used ADD instead of ADD COLUMN.
I have added an attachment to this email for your personal use: the HSQLDB user guide version 1.8. (This guide is for the version of HSQLDB used by Base.) This should help you refresh your memory.

--Dan

SewHelpMe · June 17, 2012, 2:29am

Let's see. First I'm using the design table and not SQL. (Haven't used since
mid-1980's and don't feel I recall enough.)

Next: I was altering an existing column, not adding it. So, the error
message occurs as a popup when I try to save my changes.

Interestingly enough, I did try adding another field with the same
attributes and got a similar error.

In the end, I deleted the entire table and recreated it from scratch.

Given some of the odd things I've had happen, I suspect the SQL error is
indicative of some bugs in Base.

Thank you very much for the Guide. Since today is the first time I've used
Base, I'm still also trying to figure out where things are located and how
they work ... such as switching between a form's design and editing it (the
button at the bottom doesn't seem to work).

Tom_Davies · June 17, 2012, 6:55am

Hi
I am not sure this helps now but it might be good to bookmark these links
https://wiki.documentfoundation.org/Faq#Base
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide

Note that
https://wiki.documentfoundation.org/Documentation/Publications
gives documentation for the other apps too. The Faq is incomplete for most
of the other apps but is almost fully translated from the French Faq. The
publications are complete for the other guides but so far we only have 2
chapters done for the Base Guide.

Anyone wishing to help complete documentation or help with translating is
usually welcomed by the team but they are very busy so they might not be
fast to respond.
Regards from
Tom

Villeroy · June 17, 2012, 10:00am

Tom,
Shut up if you do not have anything to answer.
The embedded HSQL database is documented here:
http://www.hsqldb.org/doc/1.8/guide/ch09.html

Villeroy · June 17, 2012, 10:13am

SewHelpMe wrote

Error while saving the table design
Column constraints are not acceptable in statement [ALTER TABLE "Companion
Subspecies" ADD "Help Type" INTEGER NOT NULL]

First of all we should know which type of database you refer to. No, Base is
not a database. It includes a certain type of database product which is
fully documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html

Assuming that we are talking about HSQLDB 1.8, the COLUMN keyword is
missing. Try this:
ALTER TABLE "Companion Subspecies" ADD COLUMN "Help Type" INTEGER NOT NULL

If your integer "Help Type" is supposed to be a foreign key you may also do
something like this:
ALTER TABLE "Companion Subspecies" ADD CONSTRAINT "FK Help Type" FOREIGN
KEY("Help Type") REFERENCES "Help Types"("ID")

So far all this is possible in the Base GUI.
Select table, menu:Edit>Edit...
Add an integer column and set "Entry required" to "Yes"
In case of a foreign key you can add a relation in the relations manager
adding the involved tables and dragging a line between the foreign key and
the other table's primary key.

This is how you get the full database as SQL script:

SQL...

SCRIPT '/tmp/database.sql'
where the single quoted string specifies the output file.
You can also close the database, open it in a zip tool and get the embedded
file ./database/script

elderdanlewis · June 17, 2012, 11:22am

SewHelpMe wrote:

Let's see. First I'm using the design table and not SQL. (Haven't used since
mid-1980's and don't feel I recall enough.)

Next: I was altering an existing column, not adding it. So, the error
message occurs as a popup when I try to save my changes.

Interestingly enough, I did try adding another field with the same
attributes and got a similar error.

In the end, I deleted the entire table and recreated it from scratch.

Given some of the odd things I've had happen, I suspect the SQL error is
indicative of some bugs in Base.

Thank you very much for the Guide. Since today is the first time I've used
Base, I'm still also trying to figure out where things are located and how
they work ... such as switching between a form's design and editing it (the
button at the bottom doesn't seem to work).

--
View this message in context: http://nabble.documentfoundation.org/Integer-Not-Null-tp3990551p3990556.html
Sent from the Users mailing list archive at Nabble.com.

http://wiki.documentfoundation.org/Documentation/Publications

About 2/3 down the page lists a couple of Base Guide chapters that are available that should help to tell you where things are located in Base.
When you edited the table using Design View, what did you select to change the field property to NOT NULL? Entry Required should be set to Yes to make the field NOT NULL.

--Dan

elderdanlewis · June 18, 2012, 4:16pm

SewHelpMe wrote:

This is my first time using LibreOffice Base and in case I'm not very clear
... I haven't programmed anything in almost 15 years so this is a bit of a
transition for an old lady.

I'm creating a table and I want a field to be of type Integer and Not Null.
(Note: I already have another field in the same table that is with the same
configuration, ie Integer/Not Null, and I'm editing an existing table to add
this field.)

I keep getting the error message:

Error while saving the table design
Column constraints are not acceptable in statement [ALTER TABLE "Companion
Subspecies" ADD "Help Type" INTEGER NOT NULL]

Could someone kindly point me in the right direction to correct my problem?

Thank you.

--
View this message in context: http://nabble.documentfoundation.org/Integer-Not-Null-tp3990551.html
Sent from the Users mailing list archive at Nabble.com.

Seems like I guessed wrong twice. The problem that you have has to do with adding a field that is NOT NULL. When you add a field to a table, it has no values until entries are made. So, selecting "Yes" for Entry Required causes the error.
Create the field with "NO" selected for "Entry Required." Enter the appropriate data making sure all the rows of the table have an entry for it. Now edit the table by setting "Entry Required" to Yes.

--Dan

Villeroy · June 18, 2012, 8:01pm

Dan Lewis wrote

Seems like I guessed wrong twice. The problem that you have has
to do with adding a field that is NOT NULL. When you add a field to a
table, it has no values until entries are made. So, selecting "Yes" for
Entry Required causes the error.
Create the field with "NO" selected for "Entry Required." Enter
the appropriate data making sure all the rows of the table have an entry
for it. Now edit the table by setting "Entry Required" to Yes.

Good catch.
There is also an option to turn off referencial integrity temporarily.
http://hsqldb.org/doc/1.8/guide/ch09.html#set_refint-section (read warning,
handle with care)