How to: SQL default values into a table.
Using LibreOffice Community 7.1.3.2 (x86) on Windows 10.0, split HSQLDB
2.3.2 how may I create tables with field values set to a default value?
Thank you!
CREATE TABLE “Test”(
“id” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
/* start at 1 instead of 0 to match Firefox */
“Code” VARCHAR(3),
/* I want Code to default to “903” */
“City_FK” INTEGER NOT NULL
/* This FK should default to “1” */
)
For what it is worth, I did search “HyperSQL User Guide” - (HSQLDB) 2.3 - and did learn how.
Hello,
Not clear on comment - question answered or not? If answered, why not post your findings and accept your own answer?
This is still a question, my friend. I have NOT found the answer.
Hello,
CREATE TABLE "Test"(
"id" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
"Code" VARCHAR(3) DEFAULT '903',
"City_FK" INTEGER DEFAULT 1
)
Note that everything in a record cannot be defaulted to. There needs to be some entry somewhere.
Thank you, @Ratslinger,
My guess is that this is more a problem of my perception and expectation. I expect to see the value in the table as I enter values. ALSO, I expect (and hope) that a form based on the table (created with the wizard, for data entry only) would show the default and allow a user to accept or change the values. They are not seen. Yes, they are written to the table!
I suppose that this deserves a new question: How to see default values in tables and forms?
It is better to present what you want done instead of how to do something. Often these are not the same.
For the form, many controls have a default value which can be entered in the properties and this may even take the place of table defaults. I am confused as to the need of a default of a foreign key as this is often handled by a form and sub form set up.
Edit:
Also see this answer and the Bug report link → table design view default date
Edit 2:
Just tested setting defaults on form controls (with table defaults left in place) for the two fields and all seems to work without problem.
Thank you, @Ratslinger,
I try to reduce the problem to its minimum to help me find the answers, and to reduce the “XY question” problem.
In my “big form”, where a city is selected and its PK is taken as an FK from T_Cities, I would like a default city shown, not the list of all cities.
Sorry, but now the only thing similar to the original question seems to be a default.
Don’t know what a “big form” is. City selected - list box? A city table? Keys? T_Cities? Default city shown - Where? How? Key? Text?
Please realize I have no idea what you are looking at.
This even sounds like a completely different question.
Have read this a number of times and still not sure what exactly is wanted.
You have a Form. This form has a field which currently is a list box. The list box contains a list of cities from a Cities table.
Now it is not known if you want to abandon this list altogether or to have a specific item in the list be a default. If the latter it will require a macro. If the former, change the list box to a text box and set a default.
This is what I can arrive at based upon what I see here.
@Ratslinger
I have tried to upload my split DB and reframe my question - problem - hope with Not able to see table defaults in forms